将日期插入SQL Server DB [英] Inserting dates into SQL Server DB
问题描述
我在C#.NET页面中运行以下代码,并且它没有在数据库中输入
值。我确定问题与txtBirth
字段有关。它允许用户以dd / mm / yyyy的形式输入DOB,我认为这是导致问题的
斜杠(/)。如果我不在这个
字段中输入DOB,那么所有数据都会毫无问题地进入数据库。
有什么想法?
SQL Server 2000,VS.NET,C#
if(Page.IsValid)
{
//将新用户保存到数据库
SqlConnection con;
string sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append(" INSERT INTO [User]");
sb.Append(" (UserID,Login,Password,FirstName,LastName,");
sb.Append(" PhoneNumber,Email,IsAdministrator,Address,");
sb.Append(" CellNumber,DateOfBirth)");
sb.Append(" VALUES(''{0}'',' {1},{2},{3},{4},{5},{6}, 7} '',
");
//没有引号的可选值,因为它们可以为空
sb.Append ({8},{9},{10}));
//添加所需的值来替换
values.Add(Guid.NewGuid()。ToString());
values.Add(txtLogin.Text);
值。添加(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
//添加可选值或Null
if(txtAddress.Text!= string .Empty)
values.Add("''" + txtAddress.Text +"''");
else
values.Add(" Null");
if(txtMobile.Text!= string.Empty)
values.Add("''" + txtMobile.Text +" ''");
else
values.Add(" Null");
if(txtBirth.Text!= string.Empty)
values.Add("''" + txtBirth.Text +"''");
else
values.Add(" Null");
//格式化带有数值数组的字符串
sql = String.Format(sb.ToString(),values.ToArray());
/ /连接并执行SQL
con = new SqlConnection(" data source = 127.0.0.1; initial catalog = Friends; user
id = sa; );
cmd =新的SqlCommand(sql,con);
con.Open();
bool doredirect = true;
试试
>
{
cmd.ExecuteNonQuery();
}
catch
{
doredirect = false;
this.lblMessage.Visible = true;
//this.lblMessage.Text ="插入无法执行。用户名可能已经
。" ;;
this.lblMessage.Text = sql;
}
终于
{
con.Close();
}
if(doredirect)
Response.Redirect(" Login.aspx");
}
else
lblMessage.Text ="修复以下错误并重试: ;
}
I''m running the following code in a C#.NET page and it doesn''t enter the
values into the DB. I''m certain the problem is to do with the txtBirth
field. It allows users to enter a DOB as dd/mm/yyyy and I think it''s the
slashes(/) that are causing the problem. If I don''t enter a DOB in this
field then all the data enters into the database without a problem.
Any ideas?
SQL Server 2000, VS.NET, C#
if (Page.IsValid)
{
// Save the new user to the database
SqlConnection con;
string sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append("INSERT INTO [User] ");
sb.Append("(UserID, Login, Password, FirstName, LastName, ");
sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
sb.Append("CellNumber, DateOfBirth) ");
sb.Append("VALUES (''{0}'', ''{1}'', ''{2}'', ''{3}'', ''{4}'', ''{5}'', ''{6}'', ''{7}'',
");
// Optional values without quotes as they can be null
sb.Append("{8}, {9}, {10})");
// Add required values to replace
values.Add(Guid.NewGuid().ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
// Add the optional values or Null
if (txtAddress.Text != string.Empty)
values.Add("''" + txtAddress.Text + "''");
else
values.Add("Null");
if (txtMobile.Text != string.Empty)
values.Add("''" + txtMobile.Text + "''");
else
values.Add("Null");
if (txtBirth.Text != string.Empty)
values.Add("''" + txtBirth.Text + "''");
else
values.Add("Null");
// Format the string with the array of values
sql = String.Format(sb.ToString(), values.ToArray());
// Connect and execute the SQL
con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends; user
id=sa;");
cmd = new SqlCommand(sql, con);
con.Open();
bool doredirect=true;
try
{
cmd.ExecuteNonQuery();
}
catch
{
doredirect = false;
this.lblMessage.Visible = true;
//this.lblMessage.Text = "Insert couldn''t be performed. Username may already
be taken.";
this.lblMessage.Text = sql;
}
finally
{
con.Close();
}
if (doredirect)
Response.Redirect("Login.aspx");
}
else
lblMessage.Text = "Fix the following errors and retry:";
}
推荐答案
嗨安德鲁,
您可以考虑使用参数化命令。
请参阅SqlCommand.Parameters属性..
-
Miha Markic - RightHand .NET咨询与开发
miha at rthand com
www.rthand.com
" Andrew Banks" < BA **** @ nojunkblueyonder.co.uk>在消息中写道
新闻:Hg ********************* @ news-text.cableinet.net ...
Hi Andrew,
You might consider using parametrised commands.
See SqlCommand.Parameters property..
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Andrew Banks" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Hg*********************@news-text.cableinet.net...
我在C#.NET页面中运行以下代码,并且它没有在数据库中输入
值。我确定问题与txtBirth
字段有关。它允许用户输入DOB作为dd / mm / yyyy,我认为它是导致问题的斜杠(/)。如果我在这个
字段中没有输入DOB,那么所有数据都会毫无问题地进入数据库。
任何想法?
SQL Server 2000 ,VS.NET,C#
如果(Page.IsValid)
//将新用户保存到数据库中
SqlConnection con;
字符串sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append(" INSERT INTO [User]");
sb.Append("(UserID) ,登录,密码,名字,姓氏,");
sb.Append(" PhoneNumber,Email,IsAdministrator,Address,");
sb.Append (CellNumber,DateOfBirth");
sb.Append(" VALUES('{0}'',''{1}'',''{2}'' ,''{3}'',''{4}'',''{5}'',''{6}'',''{7}'',
");
//没有引号的可选值因为它们可以为null
s b.Append(" {8},{9},{10})");
//添加所需的值来替换
values.Add(Guid .NewGuid()。ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
//添加可选值或Null
> if(txtAddress.Text!= string.Empty)
values.Add("''" + txtAddress.Text +"''");
values.Add(" Null);
if( txtMobile.Text!= string.Empty)
values.Add("''" + txtMobile.Text +"''");
else <
values.Add(" Null);
if(txtBirth.Text!= string.Empty)
values.Add(" ''" + txtBirth.Text +"''");
values.Add(" Null");
使用值数组格式化字符串
sql = String.Format(sb.ToString(),values.ToArray());
//连接并执行SQL
con = new SqlConnection(" data source = 127.0.0.1; initial catalog = Friends;
user id = sa;");
cmd = new SqlCommand(sql,con);
con.Open();
bool doredirect = true;
尝试
{
cmd.ExecuteNonQuery();
}
抓住
{
doredirect = false ;
this.lblMessage.Visible = true;
//this.lblMessage.Text ="插入无法执行。用户名可能已经花了
。;
this.lblMessage.Text = sql;
}
终于>
{
con.Close();
}
如果(doredirect)
回复.Redirect(" Login.aspx");
}
lblMessage.Text ="修复以下错误并重试: " ;;
}
I''m running the following code in a C#.NET page and it doesn''t enter the
values into the DB. I''m certain the problem is to do with the txtBirth
field. It allows users to enter a DOB as dd/mm/yyyy and I think it''s the
slashes(/) that are causing the problem. If I don''t enter a DOB in this
field then all the data enters into the database without a problem.
Any ideas?
SQL Server 2000, VS.NET, C#
if (Page.IsValid)
{
// Save the new user to the database
SqlConnection con;
string sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append("INSERT INTO [User] ");
sb.Append("(UserID, Login, Password, FirstName, LastName, ");
sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
sb.Append("CellNumber, DateOfBirth) ");
sb.Append("VALUES (''{0}'', ''{1}'', ''{2}'', ''{3}'', ''{4}'', ''{5}'', ''{6}'', ''{7}'',
");
// Optional values without quotes as they can be null
sb.Append("{8}, {9}, {10})");
// Add required values to replace
values.Add(Guid.NewGuid().ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
// Add the optional values or Null
if (txtAddress.Text != string.Empty)
values.Add("''" + txtAddress.Text + "''");
else
values.Add("Null");
if (txtMobile.Text != string.Empty)
values.Add("''" + txtMobile.Text + "''");
else
values.Add("Null");
if (txtBirth.Text != string.Empty)
values.Add("''" + txtBirth.Text + "''");
else
values.Add("Null");
// Format the string with the array of values
sql = String.Format(sb.ToString(), values.ToArray());
// Connect and execute the SQL
con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends; user id=sa;");
cmd = new SqlCommand(sql, con);
con.Open();
bool doredirect=true;
try
{
cmd.ExecuteNonQuery();
}
catch
{
doredirect = false;
this.lblMessage.Visible = true;
//this.lblMessage.Text = "Insert couldn''t be performed. Username may already be taken.";
this.lblMessage.Text = sql;
}
finally
{
con.Close();
}
if (doredirect)
Response.Redirect("Login.aspx");
}
else
lblMessage.Text = "Fix the following errors and retry:";
}
我和Miha在一起。您可以使用参数完成相同的操作而不是
添加另一行代码。你会得到更好的表现而且你不会担心注射攻击或像O''Malley这样的名字。
既然你' '正在使用SQL Server ..这个mod将启动它
sb.Append(" VALUES(@ FirstParamName,@ SecondParamName,@ ThirdParamName等)
然后,添加参数...
cmd.Parameters.Add(" @ FirstParamName",SqlDbType.DateTime).Value =(Date)
txtBirthday.Text;
(我知道生日与第一个参赛者没有对应,但只是
想要告诉你它是如何工作的。)来自我可以看到,使用参数代替
的好处(这对于存储过程而言看起来很成熟).....
http://www.knowdotnet.com/articles/storedprocsvb.html
>
祝你好运,
Bill
" Andrew Banks" b a****@nojunkblueyonder.co.uk>在消息中写道
新闻:Hg ********************* @ news-text.cableinet.net ...
I''m with Miha. You can accomplish the same thing with Parameters and not
add another line of code. You''ll get better performance and you won''t have
to worry about injection attacks or names like O''Malley.
Since you''re using SQL Server..this mod will start it
sb.Append("VALUES (@FirstParamName, @SecondParamName, @ThirdParamName etc)
THen, add the parameters...
cmd.Parameters.Add("@FirstParamName", SqlDbType.DateTime).Value = (Date)
txtBirthday.Text;
(I know Birthday doesn''t correspond with the first paramater, but just
wanted to show you how it works.) From what I can see, it''s all upside for
using Parameters instead (and this looks ripe for a Stored Procedure).....
http://www.knowdotnet.com/articles/storedprocsvb.html
Good Luck,
Bill
"Andrew Banks" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Hg*********************@news-text.cableinet.net...
我在C#.NET页面中运行以下代码,并且它没有在数据库中输入
值。我确定问题与txtBirth
字段有关。它允许用户输入DOB作为dd / mm / yyyy,我认为它是导致问题的斜杠(/)。如果我在这个
字段中没有输入DOB,那么所有数据都会毫无问题地进入数据库。
任何想法?
SQL Server 2000 ,VS.NET,C#
如果(Page.IsValid)
//将新用户保存到数据库中
SqlConnection con;
字符串sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append(" INSERT INTO [User]");
sb.Append("(UserID) ,登录,密码,名字,姓氏,");
sb.Append(" PhoneNumber,Email,IsAdministrator,Address,");
sb.Append (CellNumber,DateOfBirth");
sb.Append(" VALUES('{0}'',''{1}'',''{2}'' ,''{3}'',''{4}'',''{5}'',''{6}'',''{7}'',
");
//没有引号的可选值因为它们可以为null
s b.Append(" {8},{9},{10})");
//添加所需的值来替换
values.Add(Guid .NewGuid()。ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
//添加可选值或Null
> if(txtAddress.Text!= string.Empty)
values.Add("''" + txtAddress.Text +"''");
values.Add(" Null);
if( txtMobile.Text!= string.Empty)
values.Add("''" + txtMobile.Text +"''");
else <
values.Add(" Null);
if(txtBirth.Text!= string.Empty)
values.Add(" ''" + txtBirth.Text +"''");
values.Add(" Null");
使用值数组格式化字符串
sql = String.Format(sb.ToString(),values.ToArray());
//连接并执行SQL
con = new SqlConnection(" data source = 127.0.0.1; initial catalog = Friends;
user id = sa;");
cmd = new SqlCommand(sql,con);
con.Open();
bool doredirect = true;
尝试
{
cmd.ExecuteNonQuery();
}
抓住
{
doredirect = false ;
this.lblMessage.Visible = true;
//this.lblMessage.Text ="插入无法执行。用户名可能已经花了
。;
this.lblMessage.Text = sql;
}
终于>
{
con.Close();
}
如果(doredirect)
回复.Redirect(" Login.aspx");
}
lblMessage.Text ="修复以下错误并重试: " ;;
}
I''m running the following code in a C#.NET page and it doesn''t enter the
values into the DB. I''m certain the problem is to do with the txtBirth
field. It allows users to enter a DOB as dd/mm/yyyy and I think it''s the
slashes(/) that are causing the problem. If I don''t enter a DOB in this
field then all the data enters into the database without a problem.
Any ideas?
SQL Server 2000, VS.NET, C#
if (Page.IsValid)
{
// Save the new user to the database
SqlConnection con;
string sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append("INSERT INTO [User] ");
sb.Append("(UserID, Login, Password, FirstName, LastName, ");
sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
sb.Append("CellNumber, DateOfBirth) ");
sb.Append("VALUES (''{0}'', ''{1}'', ''{2}'', ''{3}'', ''{4}'', ''{5}'', ''{6}'', ''{7}'',
");
// Optional values without quotes as they can be null
sb.Append("{8}, {9}, {10})");
// Add required values to replace
values.Add(Guid.NewGuid().ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
// Add the optional values or Null
if (txtAddress.Text != string.Empty)
values.Add("''" + txtAddress.Text + "''");
else
values.Add("Null");
if (txtMobile.Text != string.Empty)
values.Add("''" + txtMobile.Text + "''");
else
values.Add("Null");
if (txtBirth.Text != string.Empty)
values.Add("''" + txtBirth.Text + "''");
else
values.Add("Null");
// Format the string with the array of values
sql = String.Format(sb.ToString(), values.ToArray());
// Connect and execute the SQL
con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends; user id=sa;");
cmd = new SqlCommand(sql, con);
con.Open();
bool doredirect=true;
try
{
cmd.ExecuteNonQuery();
}
catch
{
doredirect = false;
this.lblMessage.Visible = true;
//this.lblMessage.Text = "Insert couldn''t be performed. Username may already be taken.";
this.lblMessage.Text = sql;
}
finally
{
con.Close();
}
if (doredirect)
Response.Redirect("Login.aspx");
}
else
lblMessage.Text = "Fix the following errors and retry:";
}
Miha,
谢谢为你的inout。我不太清楚该怎么做。我是一个非常新的
到.NET并正在编写一本Wrox书 - 它实际上是他们的
教程中的一个导致我错误!
请你再详细解释一下。
谢谢
Miha Markic < miha at rthand com>在消息中写道
新闻:OE ************** @ tk2msftngp13.phx.gbl ...
Miha,
Thanks for your inout. I''m not too sure what to do with this. I''m quite new
to .NET and am working through a Wrox book - it''s actually one of their
tutorials thats causing me the error!
Would you mind giving a little more explanation please.
Thanks
"Miha Markic" <miha at rthand com> wrote in message
news:OE**************@tk2msftngp13.phx.gbl...
安德鲁,
您可以考虑使用参数化命令。
请参阅SqlCommand.Parameters属性..
- Miha Markic - RightHand .NET咨询&开发
miha at rthand com
www.rthand.com
安德鲁·班克斯 < BA **** @ nojunkblueyonder.co.uk>在消息中写道
新闻:Hg ********************* @ news-text.cableinet.net ...
Hi Andrew,
You might consider using parametrised commands.
See SqlCommand.Parameters property..
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Andrew Banks" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Hg*********************@news-text.cableinet.net...
我在C#.NET页面中运行以下代码,它没有输入
值到DB中。我确定问题与txtBirth
字段有关。它允许用户输入DOB作为dd / mm / yyyy,我认为它是导致问题的斜杠(/)。如果我在这个
字段中没有输入DOB,那么所有数据都会毫无问题地进入数据库。
任何想法?
SQL Server 2000 ,VS.NET,C#
如果(Page.IsValid)
//将新用户保存到数据库中
SqlConnection con;
字符串sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append(" INSERT INTO [User]");
sb.Append("(UserID) ,登录,密码,名字,姓氏,");
sb.Append(" PhoneNumber,Email,IsAdministrator,Address,");
sb.Append (CellNumber,DateOfBirth");
sb.Append(" VALUES('{0}'',''{1}'',''{2}'' ,''{3}'',''{4}'',''{5}'',''{6}'',
''{7}'',");
//没有引号的可选值,因为它们可以为null
某人.Append(" {8},{9},{10})");
//添加所需的值来替换
values.Add(Guid。 NewGuid()。ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
//添加可选值或Null
if(txtAddress.Text!= string.Empty)
values.Add("''" + txtAddress.Text +"''");
values.Add(" Null);
if( txtMobile.Text!= string.Empty)
values.Add("''" + txtMobile.Text +"''");
else <
values.Add(" Null);
if(txtBirth.Text!= string.Empty)
values.Add(" ''" + txtBirth.Text +"''");
values.Add(" Null");
使用值数组格式化字符串
sql = String.Format(sb.ToString(),values.ToArray());
//连接并执行SQL
con = new SqlConnection(" data source = 127.0.0.1; initial catalog = Friends;
I''m running the following code in a C#.NET page and it doesn''t enter the
values into the DB. I''m certain the problem is to do with the txtBirth
field. It allows users to enter a DOB as dd/mm/yyyy and I think it''s the
slashes(/) that are causing the problem. If I don''t enter a DOB in this
field then all the data enters into the database without a problem.
Any ideas?
SQL Server 2000, VS.NET, C#
if (Page.IsValid)
{
// Save the new user to the database
SqlConnection con;
string sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append("INSERT INTO [User] ");
sb.Append("(UserID, Login, Password, FirstName, LastName, ");
sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
sb.Append("CellNumber, DateOfBirth) ");
sb.Append("VALUES (''{0}'', ''{1}'', ''{2}'', ''{3}'', ''{4}'', ''{5}'', ''{6}'', ''{7}'', ");
// Optional values without quotes as they can be null
sb.Append("{8}, {9}, {10})");
// Add required values to replace
values.Add(Guid.NewGuid().ToString());
values.Add(txtLogin.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
values.Add(0);
// Add the optional values or Null
if (txtAddress.Text != string.Empty)
values.Add("''" + txtAddress.Text + "''");
else
values.Add("Null");
if (txtMobile.Text != string.Empty)
values.Add("''" + txtMobile.Text + "''");
else
values.Add("Null");
if (txtBirth.Text != string.Empty)
values.Add("''" + txtBirth.Text + "''");
else
values.Add("Null");
// Format the string with the array of values
sql = String.Format(sb.ToString(), values.ToArray());
// Connect and execute the SQL
con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends;
user
user
id = sa;");
cmd = new SqlCommand(sql,con);
con.Open();
bool doredirect = true ;
尝试
{
cmd.ExecuteNonQuery();
}
CATC h />
{doredirect = false;
this.lblMessage.Visible = true;
//这个。 lblMessage.Text ="无法执行插入。用户名
id=sa;");
cmd = new SqlCommand(sql, con);
con.Open();
bool doredirect=true;
try
{
cmd.ExecuteNonQuery();
}
catch
{
doredirect = false;
this.lblMessage.Visible = true;
//this.lblMessage.Text = "Insert couldn''t be performed. Username may
已经
被采取。" ;;
this.lblMessage.Text = sql;
}
最后
{
con.Close();
}
如果(doredirect)
Response.Redirect(" Login.aspx");
}
其他
lblMessage .Text ="修正以下错误并重试:" ;;
}
be taken.";
this.lblMessage.Text = sql;
}
finally
{
con.Close();
}
if (doredirect)
Response.Redirect("Login.aspx");
}
else
lblMessage.Text = "Fix the following errors and retry:";
}
这篇关于将日期插入SQL Server DB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!