0

I am developing a database application in C#.NET and SQL Server 2012. Some of my SQL statements are not working properly . When I execute the code it does not give any error. But when I try to delete something or Update a record, I does not do that. The code lies below: 

    public void updateFinalTable()
    {
        DialogResult result = MessageBox.Show("Please make sure no fields are empty or they will get changed. \n\t\t Do you want to continue?",
        "Important Note",
        MessageBoxButtons.YesNo);
        if (result == DialogResult.Yes)
        {
            try
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);

                con.Open();
                SqlCommand cmd = new SqlCommand("UPDATE fianlTable SET   AccountNumber='" + textBox1.Text + "', Date='" + dateTimePicker1.Value.ToString("MM/dd/yyyy") + "', CustomerName='" + textBox3.Text + "' , Debit='" + txtDebit.Text + "', Credit='" + txtCredit.Text + "', Balance='" + txtBalance.Text + "'  WHERE Id LIKE '" + textBox4.Text + "' ", con);
                cmd.ExecuteNonQuery();
                this.fianlTableBindingSource.AddNew();
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter("select * from fianlTable WHERE (UserName LIKE '" + LoginSession.UserID + "')", con);

                sda.Fill(dt);
                dataGridView1.DataSource = dt;
                refresh();
                con.Close();

                MessageBox.Show("Record Updated Successfully!");

            catch (Exception)
            {
                MessageBox.Show("Record Could Not be updated...!   ");
            }
        }
    }

Similar is the case with delete operation . Both codes give no error but inside the database no change is observed.

Muhammad Shahid
  • 19
  • 1
  • 9
  • Perhaps the query is working fine just not "hitting" the row - this could be caused by an incorrect Id in the query? I cannot help but notice a whitespace in the end of the quey: `textBox4.Text + "' "` - have you tried removing it? – Andersnk May 17 '16 at 20:34
  • 5
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection – marc_s May 17 '16 at 20:35
  • because your WHEREs make the queries affect no records. Debug your application and extract the constructed T-SQL before is passed to the server. Then manually execute the SQL in SSMS to prove that it affects no records and for you to see your error in the SQL produced by your code. You should use parameterized procs anyways, IMO. – Ricardo C May 17 '16 at 20:35
  • 1
    Yep. What @marc_s said =) – Ricardo C May 17 '16 at 20:37
  • You want the like query, then try putting % for comparison. Otherwise use equal to. Most probably your query is failing to mach records. – Thanigainathan May 17 '16 at 20:48

1 Answers1

1

You have used Like in your where condition instead of =. So your code should be like this -

SqlCommand cmd = new SqlCommand("UPDATE fianlTable SET AccountNumber='" + textBox1.Text + "', Date='" + 
dateTimePicker1.Value.ToString("MM/dd/yyyy") + "', CustomerName='" +
textBox3.Text + "' , Debit='" + txtDebit.Text + "', Credit='" + 
txtCredit.Text + "', Balance='" + txtBalance.Text + 
"'  WHERE Id = '" + textBox4.Text + "' ", con);

ATTENTION This type of query potentially lead to SQL Injection. You better go with parametrized queries, like this -

string qry = = "UPDATE fianlTable SET AccountNumber = @accnt, CustomerName = @cname Where ID = @id)";

 SqlCommand cmd = new SqlCommand(qry, con);
 cmd.Parameters.AddWithValue("@accnt", textBox1.Text);
 cmd.Parameters.AddWithValue("@cname", textBox3.Text);
 cmd.Parameters.AddWithValue("@id", textBox4.Text);  
 cmd.ExecuteNonQuery();
Krishnraj Rana
  • 6,516
  • 2
  • 29
  • 36
  • You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s May 17 '16 at 21:05