CODEWITHSUNDEEP
c#sqlsql-serverdatabase
matt2605
matt2605

Reputation: 216

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.

Upvotes: 0

Views: 71

Answers (2)

Rahul Hendawe
Rahul Hendawe

Reputation: 912

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();

Upvotes: 0

Ryan
Ryan

Reputation: 3982

You're missing the "@" for the IDVAL paramer:

string command = "UPDATE QuizTable SET ques1= @ques1VAL WHERE ID = @IDVAL";

Upvotes: 2

Related Questions