CODEWITHSUNDEEP
sqlsql-serverdatabase
Dave
Dave

Reputation: 87

Trouble with updating database data

I'm having trouble with my UPDATE statement in which I'm trying to update table data through WHERE clause, which gives me error of data miss-match.

sqL = "UPDATE Customer SET name= '" & txtName.Text & "', adress= '" & txtAdress.Text & "', contact = '" & txtContact.Text & "' WHERE Customer_ID = '" & txtCustomerID.Text & "'"

I've also tried 

sqL = "UPDATE Customer SET name= '" & txtName.Text & "', adress= '" & txtAdress.Text & "', contact = '" & txtContact.Text & "' WHERE Customer_ID = '" & Convert.ToInt32(txtCustomerID.Text) & "'"

with no luck.

Upvotes: 0

Views: 86

Answers (3)

Suraj Singh
Suraj Singh

Reputation: 4069

Your query will not compile :-

The string concatenation operator in C# is plus sign not ampersand.

however as kaf advised always use parametrized queries. enter image description here

Try using plus sign instead of an ampersand.

 "UPDATE Customer SET name= '" + txtName.Text + "', adress= '" + txtAdress.Text + "', contact = '" + txtContact.Text + "' WHERE Customer_ID = '" + txtCustomerID.Text + "'"

If customer ID is int ,convert it to int .

Upvotes: 0

Kaf
Kaf

Reputation: 33829

Please use a parameterised query which is much cleaner and safer:

If you are on c#:

string sql = "UPDATE Customer SET name= @name, adress=@address, contact = @contact" +
             " WHERE Customer_ID = @id";
using(SqlConnection conn = new SqlConnection("yourConnectionString"))
{
   SqlCommand cmd = new SqlCommand(sql, conn);
   cmd.Parameters.AddWithValue("@name",txtName.Text);
   cmd.Parameters.AddWithValue("@address",txtAdress.Text);
   cmd.Parameters.AddWithValue("@contact",txtContact.Text);

   /*
     NOTE: Make sure Textbox value is convertible to Customer_ID data type 
           before executing the  query. It should be done before the using statement.
           Use string.Trim() method to remove any space characters at start/end
   */
   cmd.Parameters.AddWithValue("@id",txtCustomerID.Text.Trim());

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

}

Upvotes: 2

Mathmagician
Mathmagician

Reputation: 113

It looks like the data type of Customer_ID is an int. In that case, remove the single quotes from around your convert statement.

sqL = "UPDATE Customer SET name= '" & txtName.Text & "', adress= '" & txtAdress.Text & "', contact = '" & txtContact.Text & "' WHERE Customer_ID = " & Convert.ToInt32(txtCustomerID.Text)

But do double check the data type in your table to be sure.

Upvotes: 0

Related Questions