Click here to Skip to main content
15,889,637 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:

C#
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();
Posted
Updated 4-Jun-18 5:02am
v2

You have defined @LastName in your query but you are conditionally providing the value for it (not all the time).
 
Share this answer
 
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:
C#
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";
 
Share this answer
 
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.

C#
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();
   }
}
 
Share this answer
 
Comments
Member 13857678 5-Jun-18 12:26pm    
Thank you guys but it didn't work i think the issue is in the sql because i can't execute any query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900