Can't delete row from single column table

Question

I have problem with the query to delete a row from table(I am using MySQL lite), I'm using data bound comobox to select what to delete but I get this error {"Invalid column name 'Football'."} on executing the command

        con.Open();
        SqlCommand cm = new SqlCommand("DELETE FROM Sports WHERE Sport = " + cbSelectSport.Text + "", con);
        cm.ExecuteNonQuery();
        MessageBox.Show("Done");
        con.Close();

Don't use string concatenation to create the query, use a parameterized query and pass the criteria as a parameter — Panagiotis Kanavos, Feb 24 '15 at 15:52
You are inviting [SQL Injection](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). — DonBoitnott, Feb 24 '15 at 15:54
MySQL or SQL Server? The tag says MySQL but the code is SQL Server — Panagiotis Kanavos, Feb 24 '15 at 15:55

score 1 · Answer 1 · edited Feb 24 '15 at 16:16

1

Be careful with you syntax.

I don't know the type of the sport column, but I think need to enclose your value in quotes( single or double).

new SqlCommand("DELETE FROM Sports WHERE Sport = \"" + cbSelectSport.Text + "\", con);

or

new SqlCommand("DELETE FROM Sports WHERE Sport = '" + cbSelectSport.Text + "', con);

edited Feb 24 '15 at 16:16

Codeek

1,624
1
11
20

answered Feb 24 '15 at 15:51

A.D.

1,160
1
8
23

It's mysql if I trust the tag – A.D. Feb 24 '15 at 15:53
1

Better yet, use a parameterized query to avoid this problem entirely *and* improve performance – Panagiotis Kanavos Feb 24 '15 at 15:53
1

This isn't a real solution - image what will happen if the user enters a `'` or `"` – Panagiotis Kanavos Feb 24 '15 at 15:54
@PanagiotisKanavos I agree with you. But I don't know C# and how to use the parameterized query. – A.D. Feb 24 '15 at 15:56

Panagiotis Kanavos · Answer 2 · 2015-02-24T16:06:26.567

1

String concatenation should be avoided in almost every case. You should use parameterized queries whenever possible. You avoid conversions, SQL injection attacks and the code is typically faster because the server can reuse execution plans

Writing a parameterized query is also easier:

using(var con=new SqlConnection(...))
{
    con.Open();
    var cm = new SqlCommand("DELETE FROM Sports WHERE Sport = @sports", con);
    var parameter=cm.Parameters.AddWithValue("@sports",cbSelectSport.Text);
    cm.ExecuteNonQuery();
    MessageBox.Show("Done");
 };

This way the parameter values are passed out of band (ie outside the query) without converting to text. This is extremely useful when you want to pass decimal or date values.

Most people would warn against using AddWithValue because it makes too many assumptions based on its input value that can hurt performance. In this case you can use Add to create the parameter, then set its value, size, precision etc:

    var parameter=cm.Parameters.Add("@sports",SqlDbType.NVarChar);
    parameter.Size=20;
    parameter.Value=cbSelectSport.Text;

edited Feb 24 '15 at 16:06

answered Feb 24 '15 at 15:59

Panagiotis Kanavos

120,703
13
188
236

I agree 99.99999%. However your comment about reusing plans is a common myth. Ad hoc queries have their execution plan cached just like everything else and can be reused. – Sean Lange Feb 24 '15 at 16:46
Not a myth - with a parameterized query the server can create a plan that can match any value passed to the parameter. With an ad-hoc query plan the server will have to guess which parts are actually parameters in order to pick a previous plan. If that fails, a new one will be created – Panagiotis Kanavos Feb 24 '15 at 16:55
It caches the plan just the same. It is not always as effective though. – Sean Lange Feb 24 '15 at 19:02
Here is an excellent post about this topic. It isn't the cut and dry "parameterized queries are faster" that many people claim. http://www.sqlservercentral.com/blogs/scarydba/2009/10/05/ad-hoc-queries-don_1920_t-reuse-execution-plans_3A00_-myth-or-fact/ – Sean Lange Feb 24 '15 at 19:39
@SeanLange I don't think the article says what you think it says. It compares ad-hoc, parameterized queries and stored procedures and states that with ad-hoc ` ... you’re going to get a different execution plan. That’s because the full text of the query is used to determine if the existing plan will work for the new query ...`. It's the parameterized version that reuses execution plans. SQL Server can use parameter sniffing to parameterize even in ad-hoc queries but that doesn't always work – Panagiotis Kanavos Feb 25 '15 at 07:37

score 0 · Accepted Answer · answered Feb 24 '15 at 15:50

0

You must specify textvalue in single quotation marks ''.

SqlCommand cm = new SqlCommand("DELETE FROM Sports WHERE Sport = '" + cbSelectSport.Text + "'", con);

answered Feb 24 '15 at 15:50

hkutluay

6,794
2
33
53

3

Better yet, use a parameterized query to avoid this problem entirely *and* improve performance – Panagiotis Kanavos Feb 24 '15 at 15:52

score 0 · Answer 4 · answered Feb 24 '15 at 15:53

0

You might want to add the single quote:

SqlCommand cm = new SqlCommand("DELETE FROM Sports WHERE Sport = '" + cbSelectSport.Text + "'", con);

answered Feb 24 '15 at 15:53

bleedr

25
6

score 0 · Answer 5 · answered Feb 24 '15 at 15:59

0

You should use parametrized query to prevent SQL Injection attack. Also it will solve your problem. By the way you can just add single quotes to your query.

"DELETE FROM Sports WHERE Sport = '" + cbSelectSport.Text + "'"

answered Feb 24 '15 at 15:59

František Pártl

37
6

This won't work if the combo contains a single quote. Which is why the OP should use parameterized queries – Panagiotis Kanavos Feb 24 '15 at 16:07

Can't delete row from single column table

5 Answers5