我该怎么办？ [英] What should I fix ?

问题描述

System.Data.dll中发生了'System.Data.SqlClient.SqlException'类型的未处理异常



附加信息：必须声明标量变量@ LastName。



我尝试过：

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Must declare the scalar variable "@LastName".

What I have tried:

String ConnectionString, SQL;
ConnectionString = "Data Source=DESKTOP-411ATJG\\SQLEXPRESS; Initial Catalog=CarRental; Integrated Security=True";
SQL = "Update Customers SET LastName=@LastName,phone=@phone,Address=@Address where CustID=@CustID ";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = SQL;
cmd.Parameters.Add("@CustID", SqlDbType.Int).Value = Convert.ToInt32(cust.ToString());

if (ULN.Text != "")
{
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = ULN.Text;
}

if (UAddress.Text != "")
{
    cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = UAddress.Text;
}

if (UPhone.Text != "")
{
    cmd.Parameters.Add("@phone", SqlDbType.Int).Value = Convert.ToInt32(UPhone.ToString());
}

conn.Open();
cmd.ExecuteNonQuery();

cmd.Parameters.Clear();
conn.Close();

推荐答案

您在查询中定义了 @LastName 但是您有条件地为它提供值（而不是所有时间）。

You have defined @LastName in your query but you are conditionally providing the value for it (not all the time).

您必须始终提供查询字符串中定义的参数。因为您正在更新记录集而不想修改所有字段，所以必须使用不同的SQL命令字符串。



您可以使用子字符串：

You have to always provide the parameters defined in the query string. Because you are updating a recordset and don't want to modify all fields, you have to use different SQL command strings.

You can use a sub string for that:

SqlCommand cmd = conn.CreateCommand();
cmd.Parameters.Add("@CustID", SqlDbType.Int).Value = Convert.ToInt32(cust.ToString());
String queryParams;
if (ULN.Text != "")
{
    queryParams = "LastName=@LastName"; 
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = ULN.Text; 
}
if (UAddress.Text != "") 
{ 
    if (queryParams != "")
        queryParams += ","; 
    queryParams += "Address=@Address"; 
    cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = UAddress.Text; 
}
if (UPhone.Text != "") 
{ 
    if (queryParams != "")
        queryParams += ","; 
    queryParams += "phone=@phone"; 
    cmd.Parameters.Add("@phone", SqlDbType.Int).Value = Convert.ToInt32(UPhone.ToString());
}
cmd.CommandText = "UPDATE Customers SET " + queryParams + " WHERE CustID=@CustID";

ULN.Text为null，这就是您收到该错误的原因。您需要在参数中添加默认值，或在运行时构建SQL查询。我建议使用null解析运算符而不是if语句。



我还建议使用using关键字以确保正确处理并避免内存泄漏。

ULN.Text is null, that's why you're getting that error. You need to add defaults to your parameters, or build your SQL query at runtime. I suggest the null-resolution operator rather than if statements.

I also suggest the "using" keyword to ensure proper disposal and avoid memory leaks.

var ConnectionString = "Data Source=DESKTOP-411ATJG\\SQLEXPRESS; Initial Catalog=CarRental; Integrated Security=True";
var SQL = "Update Customers SET LastName=@LastName,phone=@phone,Address=@Address where CustID=@CustID";

using(SqlConnection conn = new SqlConnection(ConnectionString)){
   using(SqlCommand cmd = conn.CreateCommand())
   {
      cmd.CommandText = SQL;
      cmd.Parameters.Add("@CustID", SqlDbType.Int).Value = 
Convert.ToInt32(cust.ToString());
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = ULN.Text ?? string.Empty; 
      cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = UAddress.Text ?? string.Empty; 
      cmd.Parameters.Add("@phone", SqlDbType.Int).Value = UPhone.Text != null ? Convert.ToInt32(UPhone.Text.ToString()) : 0; 
      conn.Open();
      cmd.ExecuteNonQuery();
   }
}

这篇关于我该怎么办？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！