incorrect syntax error While executing

Question

I am executing a SQL statement in SQL Server Express

insert into dscl_sql_log (user_name, sql_time, sql) 
values ('ADMIN', '2/17/2014 7:05:45 PM', 'select * from cn_fieldsurvey_trn where unit_code = '03' and rownum < 10')

It gives an error message that **incorrect syntax near '03'**

You should use double quotas around string constant = ''03'' — valex
– valex, Commented Feb 17, 2014 at 13:41

Mudassir Hasan · Accepted Answer · 2014-02-17 13:57:43Z

1

 insert into dscl_sql_log (user_name, sql_time, sql) values ('ADMIN', '2/17/2014 7:05:45 PM', 'select * from cn_fieldsurvey_trn where unit_code = ''03'' and rownum < 10')

If you are getting value from textbox then it is highly recommended to use parameterized query to prevent SQL Injection Attacks and avoid explicitly escaping single characters in value being passed.

       string query=  @"insert into dscl_sql_log (user_name, sql_time, sql) values  " +
            " (" +
                  @"'ADMIN', '2/17/2014 7:05:45 PM',@sql "+
             ")";

        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@sql", txtbox.Text);

            cmd.ExecuteNonQuery();
        }

edited Feb 17, 2014 at 13:57

answered Feb 17, 2014 at 13:41

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ravi Over a year ago

Thanks for answering, but what if I am getting a value of sql column from a text box. Actually

string LogQry = "insert into dscl_sql_log (user_name, sql_time, sql) values ('"+user_code+"', '" + DateTime.Now + "', '"+SqlText.ToString()+"')";

above is my code in .cs page.

jean Over a year ago

You can use my answer. But since you are dynamically building that query just replace one quote for two quotes in that ToString. Btw your app is likey to be prone o SQL inject attacks.

Mudassir Hasan Over a year ago

@jean parameterized query should be used

jean · Accepted Answer · 2014-02-17 13:51:35Z

0

You need to replace that quotes with 2 quotes, you are getting a error due to not escaping quotes.

insert into dscl_sql_log (user_name, sql_time, sql) values 
    ('ADMIN', '2/17/2014 7:05:45 PM',
replace('select * from cn_fieldsurvey_trn where unit_code = '03' and rownum < 10','''',''''''))

answered Feb 17, 2014 at 13:51

jean

4,3574 gold badges33 silver badges55 bronze badges

Comments

Vinicius Botelho Bizarri · Accepted Answer · 2014-02-17 16:18:52Z

0

Or you can use like this:

insert into dscl_sql_log (user_name, sql_time, sql) values ('ADMIN', '2/17/2014 7:05:45 PM', 'select * from cn_fieldsurvey_trn where unit_code = ' + '03' + ' and rownum < 10')

But be careful whit SQL Injection issues like they said!

answered Feb 17, 2014 at 16:18

Vinicius Botelho Bizarri

732 silver badges10 bronze badges

Comments

LuisR9 · Accepted Answer · 2014-02-17 19:13:18Z

0

try:

insert into dscl_sql_log (user_name, sql_time, sql) 
values ('ADMIN', '2/17/2014 7:05:45 PM', 'select * from cn_fieldsurvey_trn where unit_code = ''03'' and rownum < 10')

use 2 quote in the strings.

answered Feb 17, 2014 at 19:13

LuisR9

1163 bronze badges

Collectives™ on Stack Overflow

incorrect syntax error While executing

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related