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?
Best Answer
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 expectsdd/MM/yyyy
(or vice-versa).Try this code in your SQL Server:
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??