deleting row from database using C#

Question

Why this code is not deleting row and show error message

 protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            String connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid ('"+cids.Text+"')",con);
            con.Open();
            cmd.ExecuteNonQuery();
            Response.Redirect("done.aspx");
            con.Close();
        }
        catch (SqlException)
        { Label1.Text = "error";
        }
    }

What error message is it showing? (Hint: change `catch(SqlException)` to `catch(SqlException e)` and post the value of `e`) — crthompson, Feb 27 '14 at 07:17
I suspect your SQL is a little rusty. Perhaps you should consult the manual: https://dev.mysql.com/doc/refman/5.7/en/delete.html — Tieson T., Feb 27 '14 at 07:20

score 4 · Answer 1 · edited May 23 '17 at 10:31

4

USE SQL PARAMETERS, YOUR CODE IS TOTALLY UNSAFE, FIX IT!

Did you mean to use the SQL IN clause? That would be "WHERE cid IN (...)", or just single value with "WHERE cid = ..."

See Parameterize an SQL IN clause to use parameters with IN clause

edited May 23 '17 at 10:31

Community

1
1

answered Feb 27 '14 at 07:17

TFD

23,890
2
34
51

now using this SqlCommand cmd = new SqlCommand("DELETE from [course] where cid IN ('" + cids.Text + "')", con); – user3359284 Feb 27 '14 at 07:36
@user3359284 the point TFD is making is that _any_ query you build strictly by creating a string has the potential to cause problems, including the potential to have very unwanted queries injected into your database through this insecure hole you create. Imagine for example that `cids.Text` is `"); drop table course"`... your query is not likely to please you! Using **proper** parameterized query creation avoids this. – mah Feb 27 '14 at 15:33

score 1 · Answer 2 · answered Feb 27 '14 at 07:20

1

SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid ('"+cids.Text+"')",con);

Should be:

SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid IN ('"+cids.Text+"')",con);

Notice the "in" between cid and (

The assumption is that you should use in because of the parens. But if you have only one value you are wanting to compare to then = should do the trick.

Also, as @TFD notes, you should use parameterized SQL. This code will allow for SQL Injection attacks.

answered Feb 27 '14 at 07:20

crthompson

15,653
6
58
80

now moves to the next page but does not delete row from database – user3359284 Feb 27 '14 at 07:32
@user3359284 What error are you getting? – crthompson Feb 27 '14 at 07:32
does delete row from database but moves to next page – user3359284 Feb 27 '14 at 07:39
I dont understand. Now it DOES delete the row? What is the value of `SqlException`? – crthompson Feb 27 '14 at 07:44
@user3359284 Please change `catch(SqlException)` to `catch(SqlException e)` and post the value of `e` – crthompson Feb 27 '14 at 07:46

score 1 · Answer 3 · answered Feb 27 '14 at 07:21

Ok, did you ever read the SQL syntax somewhere? Or are you just throwing random stuff at a wall and hoping it sticks?

cid ('"+cids.Text+"')",

Let's ignore the fact that this is the best way to get fired - anyone can execute ANY RANDOM SQL HE WANTS in your database. Bad news - happens when you do not check your input (cids.Text.

But brutally sppeaking, if I have 55 in the text box, that translates into:

cid ('55')

Ok, where does that come from?

() indiate IN clause, but there is no IN.

SQL syntax says quality are = - that would be cid = 55

THen a number is not a string (no need for '55', it is 55).

Correct would be

cid = 55

Please, a one minute check of the documentation is sometimes ncice to learn a language. Helps to not throw random statements into a compiler hoping they are correct.

Nagaraj S · Accepted Answer · 2014-02-27T07:54:26.143

you forget to use =

SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid ('"+cids.Text+"')",con);

This line shoud be

SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid ='"+cids.Text+"'",con);

or use WHERE cid IN (...)

SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid IN ('"+cids.Text+"')",con);

protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            String connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd=new SqlCommand("DELETE  from [course] where cid IN ('"+cids.Text+"')",con);
            con.Open();
            cmd.ExecuteNonQuery();
            Response.Redirect("done.aspx");
            con.Close();
        }
        catch (SqlException ex)
        { 
         Label1.Text = "error";
        }
    }

it does not delete row from database but control goes to next page — user3359284, Feb 27 '14 at 07:44

deleting row from database using C#

4 Answers4