ASP.NET Error The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.

Question

Hey all I'm trying to do the following insert query

SqlDataSource userQuizDataSource = new SqlDataSource();
userQuizDataSource.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=quizApp;Integrated Security=True";
userQuizDataSource.InsertCommand = "INSERT INTO [UserQuiz] ([DateTimeComplete], [Score], [UserName]) VALUES (@DateTimeComplete, @Score, @UserName)";

userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString());
userQuizDataSource.InsertParameters.Add("Score", score.ToString());
userQuizDataSource.InsertParameters.Add("UserName", User.Identity.Name);

int rowsAffected = userQuizDataSource.Insert();

Buti keep getting the following error:

The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value. The statement has been terminated.

Can anyone help me out ??

thanks !!

Answer 1

Try changing this:

userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString());

to this:

userQuizDataSource.InsertParameters.Add("@startdate", SqlDbType.DateTime, DateTime.Now.ToString());

Answer 2

Try no converting your date to string:

userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now);

Edit: Try this then:

userQuizDataSource.InsertParameters.Add("DateTimeComplete", TypeCode.DateTime, DateTime.Now.ToString());

There is another way to just pass the actual object, but I can't remember.. sorry.

Answer 3

What does your statement DateTime.Now.ToString() return??

What language and regional settings is your SQL Server expecting??

Do you have a mismatch there??? Maybe your .NET returns a MM/dd/yyyy format, while SQL Server expects dd/MM/yyyy (or vice-versa).

Try this code in your SQL Server:

DECLARE @test TABLE (smalldate SMALLDATETIME)
INSERT INTO @test VALUES ('02/21/2010 22:00:32') --
SELECT * FROM @test

Replace my string there with what output you got from .NET's DateTime.Now.ToString() - does this work? Does SQL Server give you a better error message?

Next, try to use the ISO-8601 format for dates (YYYYMMDD) - this works for ALL regional and language settings in SQL Server - does this work??

DECLARE @test TABLE (smalldate SMALLDATETIME)
INSERT INTO @test VALUES ('20100221 22:00:32') --
SELECT * FROM @test