Deleting from SQL database using C#

Question

hi I'm having troubles regarding the window alert of my program. it always show 'no record found in the database' but it successfully delete the row. asking for a hand here. thanks!

try
            {
                con.Open();
                string Sql = "DELETE FROM contacts WHERE LastName LIKE '" + txtbx_delete.Text + "'";

                MySqlDataAdapter da = new MySqlDataAdapter(Sql, con);
                DataSet ds = new DataSet();
                da.Fill(ds);


                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Delete successful! Check display!')", true);
                }
                else
                {
                    ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('No record with that last name found!')", true);
                }
            }

Yeah. COMMON SENSE: How would a DELETE statement load data into a DataSet? Just asking. Because in my world (which, grante, does not use MySQL) DELETE does NOT generate a output. — TomTom, Mar 29 '16 at 07:05

sujith karivelil · Answer 1 · 2019-07-17T13:55:29.107

You need to use ExecuteNonQuery() instead for Adapter Which gives you the number of rows affected, based on that you can throw the message; and probably parameterized queries are better option to avoid injection(more impotent since you are dealing with delete).

  string Sql = "DELETE FROM contacts WHERE LastName LIKE @delete";
    using(MySqlCommand cmd = new MySqlCommand(readCommand))
   {
    cmd.Parameters.Add(new MySqlParameter("@delete", txtbx_delete.Text));  
    int result= m.ExecuteNonQuery();
    //result holds number of rows affected 
    if (result>0)
     {
      ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Delete successful! Check display!')", true);
     }
    else
     {
       ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('No record with that last name found!')", true);
     }
   }

Always try to make use of the using statement to dispose of the objects correctly. You can try it for connections also, it will drop the connection instantly after using it.

where should i put that on my code. im sorry im just new in this language. trying m best to catch up. — Alphi Canoza, Mar 29 '16 at 07:09
Should be `int result = m.ExecuteNonQuery();`. @AlphiCanoza you replace your `if` statement with `if (result > 0)`. — Brad, Mar 29 '16 at 08:36

score 2 · Answer 2 · answered Mar 29 '16 at 07:05

2

This is because you don't fetch the table. You are simply running a delete query.

answered Mar 29 '16 at 07:05

Eminem

7,206
15
53
95

Deleting from SQL database using C#

2 Answers2