Incorrect syntax near '79000'

Question

I have this comamand and that error, in data i have zip code 79000 and table name site

   private void Crt_clck_Click(object sender, EventArgs e)
    {

        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT CMC, [Site Name], [Phone Number], Zip_Code FROM site Where Zip_Code'" + Zipcode.Text + "'";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            con.Close();
        }

can you help me with this

`Zip_Code'" + Zipcode.Text + "'"` should be `Zip_Code='" + Zipcode.Text + "'"`? However, these should be a paramaterized. — Davin Tryon, Mar 30 '17 at 10:44
I vote to close this question due a simple typo, a forgotten `=` — fubo, Mar 30 '17 at 10:45
You should be using parameterized queries read more here, https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/ and http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection — Adil, Mar 30 '17 at 10:47
tnx guys i just forget to use = not my self this days tnx again — Joe, Mar 30 '17 at 12:50

score 5 · Accepted Answer · edited May 23 '17 at 11:46

5

Change your sql statement to

cmd.CommandText = "SELECT CMC, [Site Name], [Phone Number], Zip_Code FROM site Where Zip_Code = '" + Zipcode.Text + "'";

You are missing the = which is needed for the syntax to be correct.

But you should think about using parameter instead to avoid SQL Injection.

Why do we always prefer using parameters in SQL statements? could be interesting for this, too.

edited May 23 '17 at 11:46

Community

1
1

answered Mar 30 '17 at 10:45

Mighty Badaboom

6,067
5
34
51

Following this logic I should have added EF or NHibernate... – Mighty Badaboom Mar 30 '17 at 10:47
1

@MightyBadaboom no EF needed, just use parameter to guard against a user inputting a quote (and maybe extra sql statements - `'; drop table site; --`). – Hans Keﬆing Mar 30 '17 at 10:49
1

I know about SQL injection and why and how you should avoid it. Edited my post to let the author know about this, too. I just don't understand why someone downvoting questions when the answer does what the questioner wants. You can always refactoring the code of the questioner to make it better. But that would not help a lot. – Mighty Badaboom Mar 30 '17 at 10:55
For the record, I didn't downvote this answer. – Davin Tryon Mar 30 '17 at 17:58
@DavinTryon thx :) – Mighty Badaboom Mar 31 '17 at 06:44

Incorrect syntax near '79000'

1 Answers1