我对这段代码有问题......我对发生的事情一无所知......
当我在 Visual Studio 中运行此代码时,我收到一条错误消息:
System.InvalidCastException: '对象不能从 DBNull 转换为其他类型。'
这是代码:
conn.Open();
SqlCommand com = new SqlCommand("update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='" + comboBox1.Text + "'", conn);
SqlDataAdapter da = new SqlDataAdapter("Declare @startdate smalldatetime declare @enddate smalldatetime set @startdate = (select date_lended from dbo.lend where client_name = '" + comboBox1.Text + "') set @enddate = (select date_back from dbo.lend where client_name = '" + comboBox1.Text + "') SELECT DATEDIFF(DAY, @startdate+2, @enddate)as timepassedd", conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow DR in dt.Rows)
{
int date;
date = Convert.ToInt32(DR["timepassedd"]);
if (date > 0)
{
com = new SqlCommand("DELETE lend WHERE client_name ='"+comboBox1.Text+"'" +
"UPDATE book_list set book_stock = book_stock 1 WHERE book_name ='" + comboBox1.Text + "'",conn);
com.ExecuteNonQuery();
MessageBox.Show("You Returned the book " + date + " Days Late!" +
"please pay the fee to the front desk");
UserPanel u = new UserPanel();
u.Show();
this.Hide();
}
else if (date <= 0)
{
com = new SqlCommand("DELETE lend WHERE client_name ='" + comboBox1.Text + "'" +
"UPDATE book_list set book_stock = book_stock 1 WHERE book_name ='" + comboBox1.Text + "'", conn);
com.ExecuteNonQuery();
MessageBox.Show("You returned the book " + date + " Days Late!" +
"please pay the fee to the front desk");
UserPanel u = new UserPanel();
u.Show();
this.Hide();
}
}
conn.Close();
提前致谢
conn.Open();
SqlCommand com = new SqlCommand("update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='" + comboBox1.Text + "'", conn);
SqlDataAdapter da = new SqlDataAdapter("Declare @startdate smalldatetime declare @enddate smalldatetime set @startdate = (select date_lended from dbo.lend where client_name = '" + comboBox1.Text + "') set @enddate = (select date_back from dbo.lend where client_name = '" + comboBox1.Text + "') SELECT DATEDIFF(DAY, @startdate+2, @enddate)as timepassedd", conn);
DataSet ds = new DataSet();
da.Fill(ds);
if(ds.Tables.Count == 1)
{
if(ds.Tables[0].Rows.Count > 0)
{
foreach(DataRow dr in ds.Tables[0].Rows)
{
if(dr.ItemArray.Length > 0)
{
if(dr["timepassedd"] != DBNull.Value)
{
int date;
date = Convert.ToInt32(Dr["timepassedd"]);
if (date > 0)
{
com = new SqlCommand("delete lend where client_name ='" + comboBox1.Text + "'" +
"UPDATE book_list set book_stock = book_stock 1 WHERE book_name ='" + comboBox1.Text + "'", conn);
com.ExecuteNonQuery();
MessageBox.Show("You Returned the book " + date + " Days Late!" +
"please pay the fee to the front desk");
UserPanel u = new UserPanel();
u.Show();
this.Hide();
}
else if (date <= 0)
{
com = new SqlCommand("delete lend where client_name ='" + comboBox1.Text + "'" +
"UPDATE book_list set book_stock = book_stock 1 WHERE book_name ='" + comboBox1.Text + "'", conn);
com.ExecuteNonQuery();
MessageBox.Show("You Returned the book " + date + " Days Late!" +
"please pay the fee to the front desk");
UserPanel u = new UserPanel();
u.Show();
this.Hide();
}
}
}
}
}
}
conn.Close();
您应该检查DBNull
值,如果该列在数据库中可以为空,DBNull.Value
则将返回a !所以你应该在处理它之前检查这个列是否有这样的值
此外,你不应该接受来自用户输入的值并将它们直接注入到 SQL 查询中!ADO.Net
. 在类Sql Parameters
的属性Parameters
中可以找到称为它的东西SqlCommand
。您应该使用此属性将来自用户的值作为参数添加到查询中
例如,可以像这样添加客户端名称:
com.Parameters.Add(new SqlParameter("client_name", comboBox1.Text));
现在您告诉 Sql 命令该值实际上是在SqlParameters
集合中显示的,如下所示:
SqlCommand com = new SqlCommand("update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='@client_name'", conn);
看看这个问题为什么我们总是喜欢在 SQL 语句中使用参数?
这是该属性的MSDN 参考Parameters
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句