By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 748 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Syntax Error Insert into Statement

P: 3
pls help me

this code error geting for syntax error insert into statement

pls check it

Expand|Select|Wrap|Line Numbers
  1. string SqlString = "INSERT INTO CUSTOMER (CODE,AREA,NAME,ADDR,CITY,STATE,PIN,COUNTRY,CONTACT,PHONE,MOBILE,FAX,EMAIL_ADDR,CREDIT_LIM,DUE_DAYS,CUST_SALES_ACC,DL_NUM,CST_NO,ST_NO,ZONE,CHK_HP_CUST,G_NAME,G_ADDR1,G_ADDR2,G_CITY,G_STATE,G_PIN,G_PHONES,CUST_CAT,STD_DISCOUNT,CUST_BANKER,CUST_TRANSPORT,Cust_Tel2,Cust_mobile,Cust_email,CustWebsite,VAT_No,Cust_Notes,SM_Code,UserDefined1,UserDefined2,UserDefined3,UserDefined4,UserDefined5,UserDefined6,AccountCode,AccountName,ContAccount,Delivery_Route,Cust_SettleDiscount,Cust_Acc_Open,Cust_Last_Cr_Review,Cust_Next_Cr_Review,Cust_AppDate,Cust_RecDate,Cust_Acc_Hold,TaxCode,CHK_OVR_PROD,CHK_INACTIVE,StatusText,drop_number,DelRouteCode,UserName) "
  2.             + "values (@CODE,@AREA,@NAME,@ADDR,@CITY,@STATE,@PIN,@COUNTRY,@CONTACT,@PHONE,@MOBILE,@FAX,@EMAIL_ADDR,@CREDIT_LIM,@DUE_DAYS,@CUST_SALES_ACC,@DL_NUM,@CST_NO,@ST_NO,ZONE,@CHK_HP_CUST,@G_NAME,@G_ADDR1,@G_ADDR2,@G_CITY,@G_STATE,@G_PIN,@G_PHONES,@CUST_CAT,STD_DISCOUNT,@CUST_BANKER,@CUST_TRANSPORT,@Cust_Tel2,@Cust_mobile,@Cust_email,@CustWebsite,@VAT_No,@Cust_Notes,@SM_Code,@UserDefined1,@UserDefined2,@UserDefined3,@UserDefined4,@UserDefined5,@UserDefined6,@AccountCode,@AccountName,@ContAccount,@Delivery_Route,@Cust_SettleDiscount,@Cust_Acc_Open,@Cust_Last_Cr_Review,@Cust_Next_Cr_Review,@Cust_AppDate,@Cust_RecDate,@Cust_Acc_Hold,@TaxCode,@CHK_OVR_PROD,@CHK_INACTIVE,@StatusText,@drop_number,@DelRouteCode,@UserName)";
  3.  
  4.  
  5.                 using (OleDbCommand cmd = new OleDbCommand(SqlString, cnnOLEDB))
  6.                 {
  7.                     cmd.CommandType = CommandType.Text;
  8.                     cmd.Parameters.Add("@CODE",OleDbType.Char).Value= txtscode.Text;
  9.                     cmd.Parameters.Add("@AREA",OleDbType.Char).Value=cmdarea.Text;
  10.                     cmd.Parameters.Add("@NAME", OleDbType.Char).Value = txtname.Text;
  11.                     cmd.Parameters.Add("@ADDR", OleDbType.Char).Value = txtadd.Text;
  12.                     cmd.Parameters.Add("@CITY", OleDbType.Char).Value = comboBox8.Text;
  13.                     cmd.Parameters.Add("@STATE",OleDbType.Char).Value = comboBox9.Text;
  14.                     cmd.Parameters.Add("@PIN",OleDbType.Char).Value = txtzip.Text;
  15.                     cmd.Parameters.Add("@COUNTRY",OleDbType.Char).Value = comboBox10.Text;
  16.                     cmd.Parameters.Add("@CONTACT",OleDbType.Char).Value = txtcontact.Text;
  17.                     cmd.Parameters.Add("@PHONE",OleDbType.Char).Value = txtphone.Text;
  18.                     cmd.Parameters.Add("@MOBILE",OleDbType.Char).Value = txtmob.Text;
  19.                     cmd.Parameters.Add("@FAX",OleDbType.Char).Value = txtfax.Text;
  20.                     cmd.Parameters.Add("@EMAIL_ADDR",OleDbType.Char).Value = txtEmail.Text;
  21.                     cmd.Parameters.Add("@CREDIT_LIM", OleDbType.Numeric).Value = txtcredit.Text;
  22.                     cmd.Parameters.Add("@DUE_DAYS", OleDbType.Numeric).Value = txtdue.Text;
  23.                     cmd.Parameters.Add("@CUST_SALES_ACC",OleDbType.Char).Value = "";
  24.                     cmd.Parameters.Add("@DL_NUM",OleDbType.Char).Value = "";
  25.                     cmd.Parameters.Add("@CST_NO",OleDbType.Char).Value = txtcst.Text;
  26.                     cmd.Parameters.Add("@ST_NO",OleDbType.Char).Value = "";
  27.                     cmd.Parameters.Add("@ZONE",OleDbType.Char).Value = cmdzone.Text;
  28.                     cmd.Parameters.Add("@CHK_HP_CUST", OleDbType.BigInt).Value = 0;
  29.                     cmd.Parameters.Add("@G_NAME",OleDbType.Char).Value = "";
  30.                     cmd.Parameters.Add("@G_ADDR1",OleDbType.Char).Value ="";
  31.                     cmd.Parameters.Add("@G_ADDR2",OleDbType.Char).Value = "";
  32.                     cmd.Parameters.Add("@G_CITY",OleDbType.Char).Value = "";
  33.                     cmd.Parameters.Add("@G_STATE",OleDbType.Char).Value = "";
  34.                     cmd.Parameters.Add("@G_PIN",OleDbType.Char).Value = "";
  35.                     cmd.Parameters.Add("@G_PHONES",OleDbType.Char).Value = "";
  36.                     cmd.Parameters.Add("@CUST_CAT",OleDbType.Char).Value = cmdcat.Text;
  37.                     cmd.Parameters.Add("@STD_DISCOUNT",OleDbType.Numeric).Value = txtdeiscount.Text;
  38.                     cmd.Parameters.Add("@CUST_BANKER",OleDbType.Char).Value = txtbanker.Text;
  39.                     cmd.Parameters.Add("@CUST_TRANSPORT",OleDbType.Char).Value = txttransport.Text;
  40.                     cmd.Parameters.Add("@Cust_Tel2",OleDbType.Char).Value = "";
  41.                     cmd.Parameters.Add("@Cust_mobile",OleDbType.Char).Value = "";
  42.                     cmd.Parameters.Add("@Cust_email",OleDbType.Char).Value = "";
  43.                     cmd.Parameters.Add("@CustWebsite",OleDbType.Char).Value ="";
  44.                     cmd.Parameters.Add("@VAT_No",OleDbType.Char).Value = txtvat.Text;
  45.                     cmd.Parameters.Add("@Cust_Notes",OleDbType.Char).Value = "";
  46.                     cmd.Parameters.Add("@SM_Code",OleDbType.Char).Value ="";
  47.                     cmd.Parameters.Add("@UserDefined1",OleDbType.Char).Value = "";
  48.                     cmd.Parameters.Add("@UserDefined2",OleDbType.Char).Value = "";
  49.                     cmd.Parameters.Add("@UserDefined3",OleDbType.Char).Value ="";
  50.                     cmd.Parameters.Add("@UserDefined4",OleDbType.Char).Value = "";
  51.                     cmd.Parameters.Add("@UserDefined5",OleDbType.Char).Value = "";
  52.                     cmd.Parameters.Add("@UserDefined6",OleDbType.Char).Value = "";
  53.                     cmd.Parameters.Add("@AccountCode",OleDbType.Char).Value = "";
  54.                     cmd.Parameters.Add("@AccountName",OleDbType.Char).Value = "";
  55.                     cmd.Parameters.Add("@ContAccount",OleDbType.Char).Value = "";
  56.                     cmd.Parameters.Add("@Delivery_Route",OleDbType.Char).Value = "";
  57.                     cmd.Parameters.Add("@Cust_SettleDiscount", OleDbType.Numeric).Value = 0;
  58.                     cmd.Parameters.Add("@Cust_Acc_Open", OleDbType.DBDate).Value = DateTime.Now;
  59.                     cmd.Parameters.Add("@Cust_Last_Cr_Review", OleDbType.DBDate).Value = DateTime.Now;
  60.                     cmd.Parameters.Add("@Cust_Next_Cr_Review", OleDbType.DBDate).Value = DateTime.Now;
  61.                     cmd.Parameters.Add("@Cust_AppDate", OleDbType.DBDate).Value = DateTime.Now;
  62.                     cmd.Parameters.Add("@Cust_RecDate", OleDbType.DBDate).Value = DateTime.Now;
  63.                     cmd.Parameters.Add("@Cust_Acc_Hold", OleDbType.BigInt).Value = 0;
  64.                     cmd.Parameters.Add("@TaxCode",OleDbType.Char).Value = "";
  65.                     cmd.Parameters.Add("@CHK_OVR_PROD", OleDbType.BigInt).Value = 0;
  66.                     cmd.Parameters.Add("@CHK_INACTIVE", OleDbType.BigInt).Value =0;
  67.                     cmd.Parameters.Add("@StatusText",OleDbType.Char).Value = "";
  68.                     cmd.Parameters.Add("@drop_number",OleDbType.Char).Value = "";
  69.                     cmd.Parameters.Add("@DelRouteCode",OleDbType.Char).Value = "";
  70.                     cmd.Parameters.Add("@UserName",OleDbType.Char).Value = Uname;
  71.  
  72.  
  73.                     cnnOLEDB.Open();
  74.                     cmd.ExecuteNonQuery();
  75.                     cnnOLEDB.Close();
May 31 '12 #1
Share this Question
Share on Google+
4 Replies


P: 3
Please help me

I received an error saying: "Syntax error in INSERT INTO statement."

This error was referencing "cmd.ExecuteNonQuery()"

Here is my code
Expand|Select|Wrap|Line Numbers
  1. OleDbCommand cmd = new OleDbCommand();
  2.             cnnOLEDB.Open();
  3.             cmd.Connection = cnnOLEDB;
  4.             cmd.CommandType = CommandType.Text;
  5.  
  6.             cmd.CommandText = "INSERT INTO CUSTOMER(CODE,AREA,NAME,ADDR,CITY,STATE,PIN,COUNTRY,CONTACT,PHONE,MOBILE,FAX,EMAIL_ADDR,CREDIT_LIM,DUE_DAYS,CUST_SALES_ACC,DL_NUM,CST_NO,ST_NO,ZONE,CHK_HP_CUST,G_NAME,G_ADDR1,G_ADDR2,G_CITY,G_STATE,G_PIN,G_PHONES,CUST_CAT,STD_DISCOUNT,CUST_BANKER,CUST_TRANSPORT,Cust_Tel2,Cust_mobile,Cust_email,CustWebsite,VAT_No,Cust_Notes,SM_Code,UserDefined1,UserDefined2,UserDefined3,UserDefined4,UserDefined5,UserDefined6,AccountCode,AccountName,ContAccount,Delivery_Route,Cust_SettleDiscount,Cust_Acc_Open,Cust_Last_Cr_Review,Cust_Next_Cr_Review,Cust_AppDate,Cust_RecDate,Cust_Acc_Hold,TaxCode,CHK_OVR_PROD,CHK_INACTIVE,StatusText,drop_number,DelRouteCode,UserName)"
  7.             + "values('" + txtscode.Text + "','" + cmdarea.Text + "','" + txtname.Text + "','" + txtadd.Text + "','" + comboBox8.Text + "','" + comboBox9.Text + "','" + txtzip.Text + "','" + comboBox10.Text + "','" + txtcontact.Text + "','" + txtphone.Text + "','" + txtmob.Text + "','" + txtfax.Text + "','" + txtEmail.Text + "'," + txtcredit.Text + "," + txtdue.Text + ",'" + "" + "','" + "" + "','" + txtcst.Text + "','" + "" + "','" + cmdzone.Text + "',0,'" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + cmdcat.Text + "'," + txtdeiscount.Text + ",'" + txtbanker.Text + "','" + txttransport.Text + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + txtvat.Text + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "','" + "" + "',0,'" + DateTime.Now + "','" + DateTime.Now + "','" + DateTime.Now + "','" + DateTime.Now + "','" + DateTime.Now + "',0,'" + "" + "',0,0,'" + "" + "','" + "" + "','" + "" + "','" + Uname + "')";
  8.  
  9.  
  10.             cmd.ExecuteNonQuery();
  11.             cnnOLEDB.Close();
May 31 '12 #2

India777
P: 61
In "@U serName" remove the space and put
@ before "STD_DISCOUNT".It may be cause the Error.
May 31 '12 #3

Frinavale
Expert Mod 5K+
P: 9,731
You should use the OleDbCommand.Parameters Property to supply parameters to your SQL Query.

I think the reason you are having problems is how how you are creating the part of the string that provides values. You probably don't have something formatted correctly which is causing a syntax error when you try to execute the SQL statement.

Honestly, if you are not planning on providing values for columns, then you should not include those columns in your insert command. This will avoid problems and make it easier to understand what is going on. You just have to set up "default values" for any field that you aren't providing during the insert that cannot be null.

Anyways, I tore your crazy thing apart so that it uses parameters. It's not guaranteed to work because I have no idea what combo box # represents, you should have named your controls so that you can tell what they represent inc doe.

Actually you probably have an error because you're accessing ComboBox.Text instead of the selected value or item or whatever, but I don't know what you're doing so this might be fine.

Here is the code that I was talking about.......
Like I said, I have no idea if it's going to work or if I'm providing the correct values for the parameters because your code is so confusing.

If you want to copy this code, use the appropriate links on the text editor so that you don't grab the line numbers as well.
Expand|Select|Wrap|Line Numbers
  1. OleDbCommand cmd = new OleDbCommand();
  2. cnnOLEDB.Open();
  3. cmd.Connection = cnnOLEDB;
  4. cmd.CommandType = CommandType.Text;
  5.  
  6. cmd.CommandText = "INSERT INTO CUSTOMER(
  7.   CODE,AREA,NAME,ADDR,
  8.   CITY,STATE,PIN,
  9.   COUNTRY,CONTACT,PHONE,
  10.   MOBILE,FAX,EMAIL_ADDR,
  11.   CREDIT_LIM,DUE_DAYS,CUST_SALES_ACC,
  12.   DL_NUM,CST_NO,ST_NO,
  13.   ZONE,CHK_HP_CUST,G_NAME,
  14.   G_ADDR1,G_ADDR2,G_CITY,
  15.   G_STATE,G_PIN,G_PHONES,CUST_CAT,
  16.   STD_DISCOUNT,CUST_BANKER,CUST_TRANSPORT,
  17.   Cust_Tel2,Cust_mobile,Cust_email,
  18.   CustWebsite,VAT_No,Cust_Notes,
  19.   SM_Code,UserDefined1,UserDefined2,
  20.   UserDefined3,UserDefined4,UserDefined5,
  21.   UserDefined6,AccountCode,AccountName,
  22.   ContAccount,Delivery_Route,Cust_SettleDiscount,
  23.   Cust_Acc_Open,Cust_Last_Cr_Review,
  24.   Cust_Next_Cr_Review,Cust_AppDate,
  25.   Cust_RecDate,Cust_Acc_Hold,
  26.   TaxCode,CHK_OVR_PROD,CHK_INACTIVE,
  27.   StatusText,drop_number,DelRouteCode,UserName)" +
  28. "VALUES(
  29.   @CODE,@AREA,@NAME,@ADDR,
  30.   @CITY,@STATE,@PIN,
  31.   @COUNTRY,@CONTACT,@PHONE,
  32.   @MOBILE,@FAX,@EMAIL_ADDR,
  33.   @CREDIT_LIM,@DUE_DAYS,@CUST_SALES_ACC,
  34.   @DL_NUM,@CST_NO,@ST_NO,
  35.   @ZONE,@CHK_HP_CUST,@G_NAME,
  36.   @G_ADDR1,@G_ADDR2,@G_CITY,
  37.   @G_STATE,@G_PIN,@G_PHONES,@CUST_CAT,
  38.   @STD_DISCOUNT,@CUST_BANKER,@CUST_TRANSPORT,
  39.   @Cust_Tel2,@Cust_mobile,@Cust_email,
  40.   @CustWebsite,@VAT_No,@Cust_Notes,
  41.   @SM_Code,@UserDefined1,@UserDefined2,
  42.   @UserDefined3,@UserDefined4,@UserDefined5,
  43.   @UserDefined6,@AccountCode,@AccountName,
  44.   @ContAccount,@Delivery_Route,@Cust_SettleDiscount,
  45.   @Cust_Acc_Open,@Cust_Last_Cr_Review,
  46.   @Cust_Next_Cr_Review,@Cust_AppDate,
  47.   @Cust_RecDate,@Cust_Acc_Hold,
  48.   @TaxCode,@CHK_OVR_PROD,@CHK_INACTIVE,
  49.   @StatusText,@drop_number,@DelRouteCode,@UserName)";
  50.  
  51. cmd.Parameters.AddWithValue("@CODE", txtscode.Text)
  52. cmd.Parameters.AddWithValue("@AREA", cmdarea.Text)
  53. cmd.Parameters.AddWithValue("@NAME", txtname.Text)
  54. cmd.Parameters.AddWithValue("@ADDR", txtadd.Text)
  55.  
  56. cmd.Parameters.AddWithValue("@CITY", comboBox8.Text)
  57. cmd.Parameters.AddWithValue("@STATE", comboBox9.Text)
  58. cmd.Parameters.AddWithValue("@PIN", txtzip.Text)
  59.  
  60. cmd.Parameters.AddWithValue("@COUNTRY", comboBox10.Text)
  61. cmd.Parameters.AddWithValue("@CONTACT", txtcontact.Text)
  62. cmd.Parameters.AddWithValue("@PHONE", txtphone.Text)
  63.  
  64. cmd.Parameters.AddWithValue("@MOBILE", txtmob.Text)
  65. cmd.Parameters.AddWithValue("@FAX", txtfax.Text)
  66. cmd.Parameters.AddWithValue("@EMAIL_ADDR", txtEmail.Text)
  67.  
  68. cmd.Parameters.AddWithValue("@CREDIT_LIM", txtcredit.Text)
  69. cmd.Parameters.AddWithValue("@DUE_DAYS", txtdue.Text)
  70. cmd.Parameters.AddWithValue("@CUST_SALES_ACC", "")
  71.  
  72. cmd.Parameters.AddWithValue("@DL_NUM", "")
  73. cmd.Parameters.AddWithValue("@CST_NO", txtcst.Text)
  74. cmd.Parameters.AddWithValue("@ST_NO", "")
  75.  
  76. cmd.Parameters.AddWithValue("@ZONE", cmdzone.Text)
  77. cmd.Parameters.AddWithValue("@CHK_HP_CUS", 0)
  78. cmd.Parameters.AddWithValue("@G_NAME", "")
  79.  
  80. cmd.Parameters.AddWithValue("@CG_ADDR1ODE", "")
  81. cmd.Parameters.AddWithValue("@G_ADDR2", "")
  82. cmd.Parameters.AddWithValue("@G_CIT", "")
  83.  
  84. cmd.Parameters.AddWithValue("@G_STATE", "")
  85. cmd.Parameters.AddWithValue("@G_PIN", "")
  86. cmd.Parameters.AddWithValue("@G_PHONES", "")
  87. cmd.Parameters.AddWithValue("@CUST_CAT", cmdcat.Text)
  88.  
  89. cmd.Parameters.AddWithValue("@STD_DISCOUNT",txtdeiscount.Text)
  90. cmd.Parameters.AddWithValue("@CUST_BANKER", txtbanker.Text)
  91. cmd.Parameters.AddWithValue("@CUST_TRANSPORT",txttransport.Text)
  92.  
  93. cmd.Parameters.AddWithValue("@Cust_Tel2", "")
  94. cmd.Parameters.AddWithValue("@Cust_mobile", "")
  95. cmd.Parameters.AddWithValue("@Cust_email", "")
  96.  
  97. cmd.Parameters.AddWithValue("@CustWebsite", "")
  98. cmd.Parameters.AddWithValue("@VAT_No", txtvat.Text)
  99. cmd.Parameters.AddWithValue("@Cust_Notes", "")
  100.  
  101. cmd.Parameters.AddWithValue("@SM_Code", "")
  102. cmd.Parameters.AddWithValue("@UserDefined1", "")
  103. cmd.Parameters.AddWithValue("@UserDefined2", "")
  104.  
  105. cmd.Parameters.AddWithValue("@UserDefined3", "")
  106. cmd.Parameters.AddWithValue("@UserDefined4", "")
  107. cmd.Parameters.AddWithValue("@UserDefined5", "")
  108.  
  109. cmd.Parameters.AddWithValue("@UserDefined6", "")
  110. cmd.Parameters.AddWithValue("@AccountCode", "")
  111. cmd.Parameters.AddWithValue("@AccountName", "")
  112.  
  113. cmd.Parameters.AddWithValue("@ContAccount", "")
  114. cmd.Parameters.AddWithValue("@Delivery_Route", "")
  115. cmd.Parameters.AddWithValue("@Cust_SettleDiscount", 0)
  116.  
  117. cmd.Parameters.AddWithValue("@Cust_Acc_Open", DateTime.Now)
  118. cmd.Parameters.AddWithValue("@Cust_Last_Cr_Review", DateTime.Now)
  119.  
  120. cmd.Parameters.AddWithValue("@Cust_Next_Cr_Review",  DateTime.Now)
  121. cmd.Parameters.AddWithValue("@Cust_AppDate", DateTime.Now)
  122.  
  123. cmd.Parameters.AddWithValue("@Cust_RecDate", DateTime.Now)
  124. cmd.Parameters.AddWithValue("@Cust_Acc_Hold", 0)
  125.  
  126. cmd.Parameters.AddWithValue("@TaxCode", "")
  127. cmd.Parameters.AddWithValue("@CHK_OVR_PROD", 0)
  128. cmd.Parameters.AddWithValue("@CHK_INACTIVE", 0)
  129.  
  130. cmd.Parameters.AddWithValue("@StatusText", "")
  131. cmd.Parameters.AddWithValue("@drop_number", "")
  132. cmd.Parameters.AddWithValue("@DelRouteCode", "")
  133. cmd.Parameters.AddWithValue("@UserName", Uname)
  134.  
  135. cmd.ExecuteNonQuery();
  136. cnnOLEDB.Close();
In the future, if you have a problem like this, output the text for your SQL command into a TextBox or something so that you can see what you have generated...if you don't see anything wrong with the SQL by looking at it, copy it and paste it into an application that allows you to run queries on your database. If it doesn't work, then fix it.

-Frinny
May 31 '12 #4

P: 46
just amazing......u r awesomeeeeeeeeeeeeeeeeeee
Jun 14 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.