locked
Syntax error in INSERT INTO statement RRS feed

  • Question

  • User186553308 posted

     cmd.CommandText = "insert into bkdb (BKDATE,CONSIGNER,CONSIGNEE,FROM,TO,ARTICAL,DESCRIPTION,SAID,PMARK,WEIGHT,RATE,PAY,SC,BC,ST,DV,HC,TOTAL) values ('" + date + "','" + consinor + "','" + consinee + "','" + source + "','" + destination + "'," + artical + ",'" + des + "','" + said + "','" + pmark + "'," + weight + "," + rate + "," + pay + "," + sc + "," + bc + "," + st + "," + dv + "," + hc + "," + total + ")";
                con.Open();
                cmd.ExecuteNonQuery();

          

         con.Close();

    Hi

    I'm using access database with an auto-number ID. I am getting Syntax error in INSERT INTO statement error while inserting. I give all fields name except ID. I checked everything but still getting this error. 


    cmd.CommandText = "insert into bkdb (BKDATE,CONSIGNER,CONSIGNEE,FROM,TO,ARTICAL,DESCRIPTION,SAID,PMARK,WEIGHT,RATE,PAY,SC,BC,ST,DV,HC,TOTAL) values ('" + date + "','" + consinor + "','" + consinee + "','" + source + "','" + destination + "'," + artical + ",'" + des + "','" + said + "','" + pmark + "'," + weight + "," + rate + "," + pay + "," + sc + "," + bc + "," + st + "," + dv + "," + hc + "," + total + ")";


    Plz help me guys..

    Saturday, June 26, 2010 7:42 AM

Answers

  • User-1199946673 posted

    FYI: Most experience people here will tell you to use parameters instead of concatenating strings...for a number of reasons.
     

    Yes indeed:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    But that won't solve this problem

    what is the format of date time

    you can try with date format is "mm/DD/yyyy" and then try

    If the format of the date would be wrong, it would result in a Data Type MisMatch error, not in a syntax error! How to work with dates in Access is explained in this article:

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

    I am getting Syntax error in INSERT INTO statement

    Although you better should use parameterized queries, your problem would still exist. That's because FROM and TO are reserved words in Jet. Reserved words shoudl better not be used as field (or table) names, but if you do, you should enclose them in Brackets:

    insert into bkdb (BKDATE,CONSIGNER,CONSIGNEE,[FROM],[TO],ARTICAL, .........

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 26, 2010 9:10 AM

All replies

  • User187056398 posted

    What does the cmd.CommandText string look like after you build it?

    You can copy the results from the debugger and run it standalone in an 'sql' window in Access to test it.

    FYI: Most experience people here will tell you to use parameters instead of concatenating strings...for a number of reasons.

    Saturday, June 26, 2010 8:01 AM
  • User-1802908944 posted

    what is the format of date time

    you can try with date format is "mm/DD/yyyy" and then try


    Saturday, June 26, 2010 8:06 AM
  • User-1199946673 posted

    FYI: Most experience people here will tell you to use parameters instead of concatenating strings...for a number of reasons.
     

    Yes indeed:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    But that won't solve this problem

    what is the format of date time

    you can try with date format is "mm/DD/yyyy" and then try

    If the format of the date would be wrong, it would result in a Data Type MisMatch error, not in a syntax error! How to work with dates in Access is explained in this article:

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

    I am getting Syntax error in INSERT INTO statement

    Although you better should use parameterized queries, your problem would still exist. That's because FROM and TO are reserved words in Jet. Reserved words shoudl better not be used as field (or table) names, but if you do, you should enclose them in Brackets:

    insert into bkdb (BKDATE,CONSIGNER,CONSIGNEE,[FROM],[TO],ARTICAL, .........

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 26, 2010 9:10 AM
  • User186553308 posted

    Thanks for quick reply..

    I don't know where the syntax error gone but this time i am getting error: 

    The type initializer for 'System.Transactions.Diagnostics.DiagnosticTrace' threw an exception. 


    cmd.CommandText string after build:

    "insert into bkdb (BKDATE,CONSIGNER,CONSIGNEE,FROM,TO,ARTICAL,DESCRIPTION,SAID,PMARK,WEIGHT,RATE,PAY,SC,BC,ST,DV,HC,TOTAL) values ('6/26/2010','SIDDHARTH KOCHAR','AKSHAY KANKARIYA','WADI','NAGPUR',2,'COMPUTER','BOX','123',2,850,1700,20,20,43.86,2000,100,1883.86)"



    Saturday, June 26, 2010 9:22 AM
  • User187056398 posted

     

    Assuming you are storing a date as a DateTime, by using a parameter, you don't have to worry about the DateTime format.

    You are correct in that it looks like reserved words are being used in the SQL string.

    Saturday, June 26, 2010 9:25 AM
  • User-1199946673 posted

    Assuming you are storing a date as a DateTime, by using a parameter, you don't have to worry about the DateTime format.
     

    Correct, which is ecplained in Mike's article on Dates and Access...

    I don't know where the syntax error gone but this time i am getting error: 

    The type initializer for 'System.Transactions.Diagnostics.DiagnosticTrace' threw an exception. 

     

    Believe me, your syntax error is still there, read my previous post. This new error is probably caused by a typo in one of your (web) config file(s)

    Saturday, June 26, 2010 9:47 AM
  • User186553308 posted

    You solved my problem. Only the date is problem. Thank you very much..

    Saturday, June 26, 2010 1:29 PM