Archive for January, 2014

“Conversion of a datetime2 data type to a datetime” error in Entity Framework

I was following the very helpful tutorial at http://msdn.microsoft.com/en-us/data/jj206878, taking my first crack at Entity Framework. Yes, I’m more than a little behind the curve on that technology. But, no time like the present to catch up.

To be honest, I was “mostly following” the tutorial. I went off and created my own User table, the script of which is:

CREATE TABLE [dbo].[User] (     
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[FirstName]   VARCHAR (50)  NOT NULL,
[LastName]    VARCHAR (50)  NOT NULL,
[Email]       VARCHAR (100) NOT NULL,
[Password]    VARCHAR (50)  NOT NULL,
 [CreatedDate] DATETIME      DEFAULT (getdate()) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

And I continued to mostly follow along with the tutorial, generating the various EF classes, and wrote code to insert values into the database using the wonderful EF stuff:

protected void Button1_Click(object sender, EventArgs e)         
{
             using (var db = new Entries())
             {
                 var newUser = new User { FirstName = txt1.Text, LastName = txt2.Text, Email = txt3.Text, Password = txt4.Text };
                 db.Users.Add(newUser);
                 db.SaveChanges();
                var queryUsers = from u in db.Users
                                  orderby u.LastName
                                  select u;
                foreach (var item in queryUsers)
                 {
                     Response.Write("<br>User = " + item.FirstName + " " + item.LastName);
                 }
             }
 }

 

When I ran my incredibly complex code, I received this unhappy message courtesy of a System.Data.Entity.Infrastructure.DbUpdateException:

Message=The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

This was because I had marked my CreatedDate field as nullable. I had thought that the database would just do its thing and create the date when the record was inserted, but EF didn’t like that. So what I did was populate the date in C# code, and that worked out:

var newUser = new User{FirstName = txt1.Text, LastName = txt2.Text, Email = txt3.Text, Password = txt4.Text
, CreatedDate = DateTime.Now};
Advertisements

,

1 Comment

‘Microsoft.ACE.OLEDB.12.0’ provider is not registered

I was working on a method to display the contents of an Excel file . No problem. But when I ran my code, I got an InvalidOperationException exception:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Luckily, this wasn’t too hard of a fix. I needed to install the data connectivity component for MS Office. The link for that is http://www.microsoft.com/en-us/download/details.aspx?id=23734. Once I had the AccessDatabaseEngine.exe, I installed it and ran my code again, and all was well.

Leave a comment

Getting around the “Conversion failed … uniqueidentifier” error

I was trying to find the unique ID of a Smurf in my appropriately named Smurf table. ID was a GUID, so I thought I would run a query like so:

SELECT * FROM Smurf WHERE ID = ’80A17BC9-AA8C-4588-B59D-00011FD411D0′

But Gargamel thwarted me with this error:

 Msg 8169, Level 16, State 2, Line 2  Conversion failed when converting from a character string to uniqueidentifier.

Bummer, my Smurfs were stuck. But luckily, the solution for my stuck Smurfs wasn’t too difficult, I just has to convert the string to a uniqueidentifier:

SELECT * FROM Smurf WHERE ID = CONVERT(uniqueidentifier,’80A17BC9-AA8C-4588-B59D-00011FD411D0′)

And there they were, in all their Smurfy goodness.

,

2 Comments