Syntax Error Insert into Statement

[sonurathore]

pls help me

this code error geting for syntax error insert into statement

pls check it

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) "
            + "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)";

            
                using (OleDbCommand cmd = new OleDbCommand(SqlString, cnnOLEDB))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@CODE",OleDbType.Char).Value= txtscode.Text;
                    cmd.Parameters.Add("@AREA",OleDbType.Char).Value=cmdarea.Text;
                    cmd.Parameters.Add("@NAME", OleDbType.Char).Value = txtname.Text;
                    cmd.Parameters.Add("@ADDR", OleDbType.Char).Value = txtadd.Text;
                    cmd.Parameters.Add("@CITY", OleDbType.Char).Value = comboBox8.Text;
                    cmd.Parameters.Add("@STATE",OleDbType.Char).Value = comboBox9.Text;
                    cmd.Parameters.Add("@PIN",OleDbType.Char).Value = txtzip.Text;
                    cmd.Parameters.Add("@COUNTRY",OleDbType.Char).Value = comboBox10.Text;
                    cmd.Parameters.Add("@CONTACT",OleDbType.Char).Value = txtcontact.Text;
                    cmd.Parameters.Add("@PHONE",OleDbType.Char).Value = txtphone.Text;
                    cmd.Parameters.Add("@MOBILE",OleDbType.Char).Value = txtmob.Text;
                    cmd.Parameters.Add("@FAX",OleDbType.Char).Value = txtfax.Text;
                    cmd.Parameters.Add("@EMAIL_ADDR",OleDbType.Char).Value = txtEmail.Text;
                    cmd.Parameters.Add("@CREDIT_LIM", OleDbType.Numeric).Value = txtcredit.Text;
                    cmd.Parameters.Add("@DUE_DAYS", OleDbType.Numeric).Value = txtdue.Text;
                    cmd.Parameters.Add("@CUST_SALES_ACC",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@DL_NUM",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@CST_NO",OleDbType.Char).Value = txtcst.Text;
                    cmd.Parameters.Add("@ST_NO",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@ZONE",OleDbType.Char).Value = cmdzone.Text;
                    cmd.Parameters.Add("@CHK_HP_CUST", OleDbType.BigInt).Value = 0;
                    cmd.Parameters.Add("@G_NAME",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@G_ADDR1",OleDbType.Char).Value ="";
                    cmd.Parameters.Add("@G_ADDR2",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@G_CITY",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@G_STATE",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@G_PIN",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@G_PHONES",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@CUST_CAT",OleDbType.Char).Value = cmdcat.Text;
                    cmd.Parameters.Add("@STD_DISCOUNT",OleDbType.Numeric).Value = txtdeiscount.Text;
                    cmd.Parameters.Add("@CUST_BANKER",OleDbType.Char).Value = txtbanker.Text;
                    cmd.Parameters.Add("@CUST_TRANSPORT",OleDbType.Char).Value = txttransport.Text;
                    cmd.Parameters.Add("@Cust_Tel2",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@Cust_mobile",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@Cust_email",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@CustWebsite",OleDbType.Char).Value ="";
                    cmd.Parameters.Add("@VAT_No",OleDbType.Char).Value = txtvat.Text;
                    cmd.Parameters.Add("@Cust_Notes",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@SM_Code",OleDbType.Char).Value ="";
                    cmd.Parameters.Add("@UserDefined1",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@UserDefined2",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@UserDefined3",OleDbType.Char).Value ="";
                    cmd.Parameters.Add("@UserDefined4",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@UserDefined5",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@UserDefined6",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@AccountCode",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@AccountName",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@ContAccount",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@Delivery_Route",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@Cust_SettleDiscount", OleDbType.Numeric).Value = 0;
                    cmd.Parameters.Add("@Cust_Acc_Open", OleDbType.DBDate).Value = DateTime.Now;
                    cmd.Parameters.Add("@Cust_Last_Cr_Review", OleDbType.DBDate).Value = DateTime.Now;
                    cmd.Parameters.Add("@Cust_Next_Cr_Review", OleDbType.DBDate).Value = DateTime.Now;
                    cmd.Parameters.Add("@Cust_AppDate", OleDbType.DBDate).Value = DateTime.Now;
                    cmd.Parameters.Add("@Cust_RecDate", OleDbType.DBDate).Value = DateTime.Now;
                    cmd.Parameters.Add("@Cust_Acc_Hold", OleDbType.BigInt).Value = 0;
                    cmd.Parameters.Add("@TaxCode",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@CHK_OVR_PROD", OleDbType.BigInt).Value = 0;
                    cmd.Parameters.Add("@CHK_INACTIVE", OleDbType.BigInt).Value =0;
                    cmd.Parameters.Add("@StatusText",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@drop_number",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@DelRouteCode",OleDbType.Char).Value = "";
                    cmd.Parameters.Add("@UserName",OleDbType.Char).Value = Uname;

                  
                    cnnOLEDB.Open();
                    cmd.ExecuteNonQuery();
                    cnnOLEDB.Close();

[sonurathore]

Syntax error in INSERT INTO statement

Please help me

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

This error was referencing "cmd.ExecuteNon Query()"

Here is my code

OleDbCommand cmd = new OleDbCommand();
            cnnOLEDB.Open();
            cmd.Connection = cnnOLEDB;
            cmd.CommandType = CommandType.Text;

            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)"
            + "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 + "')";


            cmd.ExecuteNonQuery();
            cnnOLEDB.Close();

[India777]

In "@U serName" remove the space and put
@ before "STD_DISCOUNT". It may be cause the Error.

[Frinavale]

You should use the OleDbCommand.Pa rameters 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.

OleDbCommand cmd = new OleDbCommand();
cnnOLEDB.Open();
cmd.Connection = cnnOLEDB;
cmd.CommandType = CommandType.Text;
 
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)" +
"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)";
 
cmd.Parameters.AddWithValue("@CODE", txtscode.Text)
cmd.Parameters.AddWithValue("@AREA", cmdarea.Text)
cmd.Parameters.AddWithValue("@NAME", txtname.Text)
cmd.Parameters.AddWithValue("@ADDR", txtadd.Text)

cmd.Parameters.AddWithValue("@CITY", comboBox8.Text)
cmd.Parameters.AddWithValue("@STATE", comboBox9.Text)
cmd.Parameters.AddWithValue("@PIN", txtzip.Text)

cmd.Parameters.AddWithValue("@COUNTRY", comboBox10.Text)
cmd.Parameters.AddWithValue("@CONTACT", txtcontact.Text)
cmd.Parameters.AddWithValue("@PHONE", txtphone.Text)

cmd.Parameters.AddWithValue("@MOBILE", txtmob.Text)
cmd.Parameters.AddWithValue("@FAX", txtfax.Text)
cmd.Parameters.AddWithValue("@EMAIL_ADDR", txtEmail.Text)

cmd.Parameters.AddWithValue("@CREDIT_LIM", txtcredit.Text)
cmd.Parameters.AddWithValue("@DUE_DAYS", txtdue.Text)
cmd.Parameters.AddWithValue("@CUST_SALES_ACC", "")

cmd.Parameters.AddWithValue("@DL_NUM", "")
cmd.Parameters.AddWithValue("@CST_NO", txtcst.Text)
cmd.Parameters.AddWithValue("@ST_NO", "")

cmd.Parameters.AddWithValue("@ZONE", cmdzone.Text)
cmd.Parameters.AddWithValue("@CHK_HP_CUS", 0)
cmd.Parameters.AddWithValue("@G_NAME", "")

cmd.Parameters.AddWithValue("@CG_ADDR1ODE", "")
cmd.Parameters.AddWithValue("@G_ADDR2", "")
cmd.Parameters.AddWithValue("@G_CIT", "")

cmd.Parameters.AddWithValue("@G_STATE", "")
cmd.Parameters.AddWithValue("@G_PIN", "")
cmd.Parameters.AddWithValue("@G_PHONES", "")
cmd.Parameters.AddWithValue("@CUST_CAT", cmdcat.Text)

cmd.Parameters.AddWithValue("@STD_DISCOUNT",txtdeiscount.Text)
cmd.Parameters.AddWithValue("@CUST_BANKER", txtbanker.Text)
cmd.Parameters.AddWithValue("@CUST_TRANSPORT",txttransport.Text)

cmd.Parameters.AddWithValue("@Cust_Tel2", "")
cmd.Parameters.AddWithValue("@Cust_mobile", "")
cmd.Parameters.AddWithValue("@Cust_email", "")

cmd.Parameters.AddWithValue("@CustWebsite", "")
cmd.Parameters.AddWithValue("@VAT_No", txtvat.Text)
cmd.Parameters.AddWithValue("@Cust_Notes", "")

cmd.Parameters.AddWithValue("@SM_Code", "")
cmd.Parameters.AddWithValue("@UserDefined1", "")
cmd.Parameters.AddWithValue("@UserDefined2", "")

cmd.Parameters.AddWithValue("@UserDefined3", "")
cmd.Parameters.AddWithValue("@UserDefined4", "")
cmd.Parameters.AddWithValue("@UserDefined5", "")

cmd.Parameters.AddWithValue("@UserDefined6", "")
cmd.Parameters.AddWithValue("@AccountCode", "")
cmd.Parameters.AddWithValue("@AccountName", "")

cmd.Parameters.AddWithValue("@ContAccount", "")
cmd.Parameters.AddWithValue("@Delivery_Route", "")
cmd.Parameters.AddWithValue("@Cust_SettleDiscount", 0)

cmd.Parameters.AddWithValue("@Cust_Acc_Open", DateTime.Now)
cmd.Parameters.AddWithValue("@Cust_Last_Cr_Review", DateTime.Now)

cmd.Parameters.AddWithValue("@Cust_Next_Cr_Review",  DateTime.Now)
cmd.Parameters.AddWithValue("@Cust_AppDate", DateTime.Now)

cmd.Parameters.AddWithValue("@Cust_RecDate", DateTime.Now)
cmd.Parameters.AddWithValue("@Cust_Acc_Hold", 0)

cmd.Parameters.AddWithValue("@TaxCode", "")
cmd.Parameters.AddWithValue("@CHK_OVR_PROD", 0)
cmd.Parameters.AddWithValue("@CHK_INACTIVE", 0)

cmd.Parameters.AddWithValue("@StatusText", "")
cmd.Parameters.AddWithValue("@drop_number", "")
cmd.Parameters.AddWithValue("@DelRouteCode", "")
cmd.Parameters.AddWithValue("@UserName", Uname)

cmd.ExecuteNonQuery();
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

[raghurocks]

just amazing......u r awesomeeeeeeeee eeeeeeeeee