6

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?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
c11ada
  • 4,302
  • 15
  • 48
  • 62

5 Answers5

5

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • thanks alot ... that works !! i think the problem was more the fact that the format of the date on my machine is different to that of the server !! – c11ada Feb 21 '10 at 22:06
  • .net returns date in the format of dd/MM/yyyy and when i checked SQL server the date formate is US style !! – c11ada Feb 21 '10 at 22:09
  • 2
    The safest possible "date time" format from .net to sql I have used to date is "yyyy-MM-dd HH:mm:ss.fff". PK :-) – Paul Kohler Feb 21 '10 at 22:29
1

I had the same problem adding datetime.now into my SQL server column 'Date' set to datatype SmallDateTime.

To resolve it was quite simple (after many attempts!!) 

string currentdatetime=
DateTime.Now.Year + "." + DateTime.Now.Month + "." + DateTime.Now.Day +
               " " + DateTime.Now.Hour+(":")+DateTime.Now.Minute+(":")+DateTime.Now.Second

This will return the date into the format that the Server will expect

Coding Mash
  • 3,338
  • 5
  • 24
  • 45
Liam
  • 11
  • 1
0

In Windows 8, if you are facing this problem even after changing Formats in below location

Control Panel -> Region

You still have to transfer these settings to your users. In the same window, go to tab "Administrative", click on copy settings.

Select the appropriate check boxes and click OK.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Jasim Khan Afridi
  • 776
  • 3
  • 15
  • 28
0

Try changing this:

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

to this:

userQuizDataSource.InsertParameters.Add("@startdate", SqlDbType.DateTime, DateTime.Now.ToString());
Jim G.
  • 15,141
  • 22
  • 103
  • 166
  • im getting 2 new errors now 1)The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(string, string)' has some invalid arguments 2)Argument '2': cannot convert from 'System.DateTime' to 'string' – c11ada Feb 21 '10 at 21:45
0

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.

mtmk
  • 6,176
  • 27
  • 32
  • im getting 2 new errors now 1)The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(string, string)' has some invalid arguments 2)Argument '2': cannot convert from 'System.DateTime' to 'string' – c11ada Feb 21 '10 at 21:46
  • Sorry, you are right. There is actually another way to add just the object i.e. datetime but I cant remember, haven't got VS opened at the moment. Try it on the actual data source and look for a method something like AddParameterValue..(string name, object value).. – mtmk Feb 21 '10 at 22:07