将日期插入SQL Server时出现问题 [英] Problem inserting dates to SQL Server

查看:75
本文介绍了将日期插入SQL Server时出现问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个格式为dd-MM-yyyy的DateTimePicker。在尝试

时,在SQL Server Date列中插入此日期,以下异常是

抛出:


char的转换数据类型为日期时间数据类型导致

超出范围的日期时间值。


请帮忙。

I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Please help.

推荐答案

RP写道:
RP wrote:

我有一个格式为dd-MM-yyyy的DateTimePicker。在尝试

时,在SQL Server Date列中插入此日期,以下异常是

抛出:


char的转换数据类型为日期时间数据类型导致

超出范围的日期时间值。
I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.



您是以字符串还是DateTime传递参数(指的是

您在此处开始的另一个线程)。 />

在另一个线程中,您的代码试图将字符串

解析为DateTime对象。我假设你正在将它传递给你的SQL调用

。但是你说错误仍然在转换,这表明你正在将一个字符串传递给SQL

服务器并希望它可以将其解析为正确的类型。


所以我的第一个建议就是改变你对SQL的调用来传递
DateTime对象而不是字符串。


如果这不是一个选项,那么下面的代码应该可以工作。


string dateToPass = DateTime.ParseExact(txtDOB.text," dd-MM- yyyy",

System.Globalization.DateTimeFormatInfo.CurrentInf o).ToString(" MM / dd / yyyy");


你肯定也可以使用正则表达式进行转换,

可能更有效率,但这不是我的强项。

-

Tom Porterfield

Are you passing the parameter in as string or as DateTime (referring to
the other thread you had started on this).

In the other thread you had code that was attempting to parse the string
into a DateTime object. I assumed that you were then passing that into
your SQL call. But you are saying the error is still in around
conversion, which indicates to me that you are passing a string into SQL
server and hoping it can parse that into the correct type.

So my first suggestion would be to change your call to SQL to pass in
the DateTime object rather than a string.

If that is not an option, then the following code should work.

string dateToPass = DateTime.ParseExact(txtDOB.text, "dd-MM-yyyy",
System.Globalization.DateTimeFormatInfo.CurrentInf o).ToString("MM/dd/yyyy");

You could surely also do the conversion using regular expression,
possibly more efficiently, but that just isn''t my forte.
--
Tom Porterfield


Tom,


做这样的事情。


DateTime myDate = dateTimePicker1.Value ;

Int32 myRes = ModRes.InsertN ewRecord(插入TestDate

值(''" + myDate +"'')");

MessageBox.Show(myRes.ToString());


public Int32 InsertNewRecord(string myQuery)

{

objModCon.OpenConnection();

SqlCommand cmdInsert = new SqlCommand(myQuery,

objModCon.myCN );

尝试

{

Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();

返回RecordsAffected;

}

catch(SqlException ex)

{

Console.WriteLine(ex);

返回0;

}

终于

{

cmdInsert.Dispose();

objModCon.CloseConnection();

}


}

Tom,

Doing something like this.

DateTime myDate = dateTimePicker1.Value;
Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate
values (''" + myDate + "'')");
MessageBox.Show(myRes.ToString());

public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (SqlException ex)
{
Console.WriteLine(ex);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}

}


那不是个好主意。您应该创建一个命令,其中

参数化sql语句,如下所示:


插入testdate值(@testDate)

>
然后在代码中设置参数的值。


如果你必须使用生成的字符串(我真的建议你不要)

然后你需要这样做:


int myRes = ModRes.InsertNewRecord(" insert into TestDate values(''" +

myDate.ToString(" yyyy-MM-dd")+"'')");


格式yyyy-MM-dd是SQL服务器将采用的格式总是

认可。


你现在拥有的代码,顺便说一下,是等待发生
的注入攻击。 />
-

- Nicholas Paldino [.NET / C#MVP]

- mv*@spam.guard.caspershouse.com

" RP" < rp ********* @ gmail.comwrote in message

news:11 ********************* *@q3g2000prf.googlegro ups.com ...
That''s just not a good idea. You should be creating a command which
parameterizes the sql statement, like so:

insert into testdate values (@testDate)

And then set the value of the parameter in code.

If you HAVE to use a generated string (and I really suggest you don''t)
then you need to do this:

int myRes = ModRes.InsertNewRecord("insert into TestDate values (''" +
myDate.ToString("yyyy-MM-dd") + "'')");

The format yyyy-MM-dd is the format that SQL server will always
recognize.

The code that you have now, btw, is an injection attack waiting to
happen.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11**********************@q3g2000prf.googlegro ups.com...

Tom,


做这样的事情。


DateTime myDate = dateTimePicker1.Value;

Int32 myRes = ModRes.InsertNewRecord(" Insert into TestDate

values(''" + myDate +"'')");

MessageBox.Show(myRes.ToString());


public Int32 InsertNewRecord(string myQuery)

{

objModCon.OpenConnection();

SqlCommand cmdInsert = new SqlCommand(myQuery,

objModCon.myCN) ;

尝试

{

Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();

返回RecordsAffected;

}

catch(SqlException ex)

{

Console.WriteLine(ex);

返回0;

}

终于

{

cmdInsert.Dispose();

objModCon.CloseConnection();

}


}
Tom,

Doing something like this.

DateTime myDate = dateTimePicker1.Value;
Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate
values (''" + myDate + "'')");
MessageBox.Show(myRes.ToString());

public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (SqlException ex)
{
Console.WriteLine(ex);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}

}



这篇关于将日期插入SQL Server时出现问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆