Zembian

Problem

Using the following tools Visual Web Developer Express 2008 and Sql Server Express 2008, when inserting a new row into my test User table I got the following error :

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM"

My table definition has 2 columns 

Name:DateAdded  Type : datetime Allow Nulls : No Default Value : getdate()
Name:DateTimeStamp  Type : datetime Allow Nulls : No

Solution

My LINQ insert statement set

DateTimeStamp = DateandTime.Now

and assumed that SQL Server Express 2008 would handle the DateAdded column for me.

Looking at the debug I could see DateTimeStamp was set correctly and DateAdded was empty, still on the assumption that SQL Server Express was taking care of things for me.

What fixed this issue for me was going back to my .dbml diagram, selecting the definition of DateAdded and setting the Auto Generated Value property to True.

This walkthrough explains how to connect to your SQL Server database and read from it using LINQ. 

LINQ is Microsoft's latest solution to the problem of manipulating data from within .NET 3.5. If you are using an application to generate a Data-Access Layer for you, or you are implementing your own database calls using ADO.NET, you can now use LINQ to do those things for you, very quickly and efficiently and without any third-party components. It's fast, and it's pre-installed in Visual Studio 2008. Here's what you do to use LINQ in Visual Studio 2008:

Problem

You want to read something from your SQL Server database using LINQ. Make sure you have Visual Studio 2008 and a populated SQL Server database ready.

Solution

> Create a connection to the database:

 1. Start Visual Studio 2008.

 2. Start a new Web application (or a project type of your choice).

 3. From the menu select View > Server Explorer (Ctrl+Alt+S)

 4. In the Server Explorer pane, Right-Click Data Connections and select Add Connection. 

 5. In the 'Choose Data Source' dialog, choose 'SQL Server' and press Continue.

 6. In the 'Add connection' dialog, enter your SQL Server name and login credentials, and then choose a database and press OK.

> Tell LINQ about the database:

 7. From the menu select View > Add New Item (Ctrl+Shift+A)

 8. From the list of templates, choose "LINQ To SQL Classes" and add a new instance (it's in the Data cateogry if you can't see it!)

 9. In Solution Explorer, you'll now have a strange new file called DataClasses1.dbml and you'll see a big empty window as it opens.

 11. Go back to the Server Explorer and pop open the new data connection you made in step 6. Open the "tables" node and then select every table (shift + mouse click).

 12. Drag the whole lot onto the big empty tab called DataClasses1.dbml (which should still be open).

 13. You'll see the Database schema appear in the window. Save it!

> Read something from the database using LINQ:

Your database is now visible to intellisense, via the DataClasses1DataContext object. and you are now ready to write your LINQ code which will read from the database!  

14. Now to code some LINQ. In this example, I have used a table called tbCustomers, so you need to substitute your own table name. The references to AccountBalance and FirstName are fields in the tbCustomers table. 

> Open default.aspx.vb and paste this into your Page_Load() event: 

Dim db As New DataClasses1DataContext

Run the project and you should see your data being written to the page.