matt2605 matt2605 - 5 months ago 7
SQL Question

SQL update to database not working

I am trying to update my database but it is not working. I first tried this code:

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\QuizDB.mdf;Integrated Security=True;User Instance=True;");
con.Open();
string command = "UPDATE QuizTable SET ques1= @ques1VAL WHERE ID=@IDVAL";
SqlCommand cmd = new SqlCommand(@command, con);
cmd.Parameters.AddWithValue("@ques1VAL", ques1TextBox.Text);
cmd.Parameters.AddWithValue("@IDVAL", IDTextBox.Text);
cmd.ExecuteNonQuery();
con.Close();


It doesn't throw an error but it doesn't update the database. When I tried the next code, only integers are updated and not strings.

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\QuizDB.mdf;Integrated Security=True;User Instance=True;");
con.Open();
string command = "UPDATE QuizTable " +
"SET ques1=" + ques1TextBox.Text +
" WHERE ID=" + IDTextBox.Text;
SqlCommand cmd = new SqlCommand(@command, con);
cmd.ExecuteNonQuery();
con.Close();


Can anyone explain what I am doing wrong? I prefer code to be secure against sql injection is possible please.

Answer

In first case you are missing @ at where condition so, it should be like- SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\QuizDB.mdf;Integrated Security=True;User Instance=True;"); con.Open(); string command = "UPDATE QuizTable SET ques1= @ques1VAL WHERE ID=@IDVAL"; SqlCommand cmd = new SqlCommand(@command, con); cmd.Parameters.AddWithValue("@ques1VAL", ques1TextBox.Text); cmd.Parameters.AddWithValue("@IDVAL", IDTextBox.Text); cmd.ExecuteNonQuery(); con.Close();

In your second case you pass the .text but not use proper string quotations. It should be like -

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\QuizDB.mdf;Integrated Security=True;User Instance=True;"); con.Open(); string command = "UPDATE QuizTable " + "SET ques1='" + ques1TextBox.Text + "' WHERE ID='" + IDTextBox.Text+"'; SqlCommand cmd = new SqlCommand(@command, con); cmd.ExecuteNonQuery(); con.Close();