错误：将nvarchar数据类型转换为smalldatetime数据类型会导致超出范围的值

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:

Buti不断收到以下错误:

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

将nvarchar数据类型转换为smalldatetime数据类型会导致超出范围的值。该语句已终止。

Can anyone help me out?

谁能帮我吗?

5 个解决方案

#1

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

你的语句DateTime.Now.ToString()返回什么?

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

您的SQL Server期望的语言和区域设置是什么?

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).

你有不匹配吗?也许您的.NET返回MM / dd / yyyy格式,而SQL Server期望dd / MM / yyyy(反之亦然)。

Try this code in your SQL Server:

在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?

用.NET的DateTime.Now.ToString()替换我的字符串 - 这有用吗? SQL Server是否为您提供了更好的错误消息?

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??

接下来,尝试使用ISO-8601格式的日期(YYYYMMDD) - 这适用于SQL Server中的所有区域和语言设置 - 这是否有效?

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

#2

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

我有同样的问题将datetime.now添加到我的SQL服务器列'Date'设置为数据类型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

这会将日期返回到服务器期望的格式

#3

Try changing this:

尝试改变这个:

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

to this:

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

#4

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.

还有另一种方法可以传递实际对象,但我记不清了......抱歉。

#5

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

在Windows 8中,如果您在以下位置更改格式后仍遇到此问题

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.

选中相应的复选框,然后单击“确定”。

#1