have an error in SQL syntax in C# application

Question

i have a MySql Bulk insert code but it returns with error in syntax

here is my C# code :

 DataTable dt2 = new DataTable();
        dt2.Columns.AddRange(new DataColumn[27] {
            new DataColumn("item_code", typeof(int)),
            new DataColumn("item_name", typeof(string)),
            new DataColumn("store_code",typeof(int)) ,
            new DataColumn("trans_no",typeof(int)) ,
            new DataColumn("invoice_no",typeof(int)) ,
            new DataColumn("trans_type",typeof(int)) ,
            new DataColumn("item_netPrice",typeof(float)) ,
            new DataColumn("qty",typeof(float)) ,
            new DataColumn("total_netPrice",typeof(float)) ,
            new DataColumn("item_priceWithVAT",typeof(float)) ,
            new DataColumn("total_priceWithVAT",typeof(float)) ,
            new DataColumn("item_notebookPrice", typeof(float)) ,
            new DataColumn("total_notebookPrice",typeof(float)) ,
            new DataColumn("item_difference",typeof(float)),
            new DataColumn("total_difference", typeof(float)),
            new DataColumn("item_costPrice", typeof(float)),
            new DataColumn("total_costPrice", typeof(float)),
            new DataColumn("tax_percentage", typeof(float)) ,
            new DataColumn("tax_val", typeof(float)) ,
            new DataColumn("trans_date", typeof(DateTime)),
            new DataColumn("user_add", typeof(string)),
            new DataColumn("repres_no", typeof(string)) ,
            new DataColumn("notes", typeof(string)),
            new DataColumn("payment_type", typeof(string)) ,
            new DataColumn("create_user", typeof(string)),
            new DataColumn("create_date", typeof(DateTime)) ,
            new DataColumn("create_time", typeof(TimeSpan)) });
           
        StringBuilder sCommand = new StringBuilder("INSERT INTO delta_items_grid (item_code,item_name,store_code,trans_no,invoice_no,trans_type,item_netPrice,qty, "
            + "total_netPrice,item_priceWithVAT,total_priceWithVAT,item_notebookPrice,total_notebookPrice,item_difference,total_difference,item_costPrice,total_costPrice, "
            + "tax_percentage,tax_val,trans_date,user_add,repres_no,notes,payment_type,create_user,create_date,create_time) VALUES ");
        List<string> Rows = new List<string>();

        foreach (DataGridViewRow row in invoicesGrid.Rows)
        {
            DateTime tdate = DateTime.Parse(row.Cells["TRANS_DATE"].Value.ToString());

            Rows.Add(string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}', " +
                 "'{21}','{22}','{23}','{24}','{25}','{26}')", row.Cells["ITEM_CODE"].Value.ToString(),row.Cells["ITEMS_NAME"].Value.ToString(),row.Cells["STORE_CODE"].Value.ToString(),
                row.Cells["TRANS_NO"].Value.ToString(), row.Cells["INVOICE_NO"].Value.ToString(), row.Cells["TRANS_TYPE"].Value.ToString(), row.Cells["PRICE_DEALING"].Value.ToString(),
                row.Cells["QUANTITY"].Value.ToString(),row.Cells["TOTAL_VALUE"].Value.ToString(),(float.Parse(row.Cells["NET_VALUE"].Value.ToString()) / float.Parse(row.Cells["QUANTITY"].Value.ToString())),
                row.Cells["NET_VALUE"].Value.ToString(), 0,0,0,0,0,0,row.Cells["TAX_PER"].Value.ToString(),
                row.Cells["TAX_VAL"].Value.ToString(), tdate.ToString("yyyy-MM-dd"), row.Cells["USER_ADD"].Value.ToString(),row.Cells["REPRES_NO"].Value.ToString(),
                row.Cells["NOTES"].Value.ToString(),row.Cells["PAYMENT_TYPE"].Value.ToString(), DoSomething.UserName_Param,
                DateTime.Today.Date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture), DateTime.Now.ToString("HH:mm:ss")));

           
            int _item_code = Int32.Parse(row.Cells["ITEM_CODE"].Value.ToString());
            string _item_name = row.Cells["ITEMS_NAME"].Value.ToString();
            int _store_code = Int32.Parse(row.Cells["STORE_CODE"].Value.ToString());
            float _trans_no = float.Parse(row.Cells["TRANS_NO"].Value.ToString());
            float _invoice_no = float.Parse(row.Cells["INVOICE_NO"].Value.ToString());
            string _trans_type = row.Cells["TRANS_TYPE"].Value.ToString();
            float _item_netPrice = float.Parse(row.Cells["PRICE_DEALING"].Value.ToString());
            float _qty = float.Parse(row.Cells["QUANTITY"].Value.ToString());
            float _total_netPrice = float.Parse(row.Cells["TOTAL_VALUE"].Value.ToString());
            float _item_priceWithVAT = float.Parse(row.Cells["NET_VALUE"].Value.ToString()) / float.Parse(row.Cells["QUANTITY"].Value.ToString());
            float _total_priceWithVAT = float.Parse(row.Cells["NET_VALUE"].Value.ToString());
            float _item_notebookPrice = 0;
            float _total_notebookPrice = 0;
            float _item_difference = 0;
            float _total_difference = 0;
            float _item_costPrice = 0;
            float _total_costPrice = 0;
            float _tax_percentage = float.Parse(row.Cells["TAX_PER"].Value.ToString());
            float _tax_val = float.Parse(row.Cells["TAX_VAL"].Value.ToString());
            DateTime _trans_date = tdate;
            string _user_add = row.Cells["USER_ADD"].Value.ToString();
            string _repres_no = row.Cells["REPRES_NO"].Value.ToString();
            string _notes = row.Cells["NOTES"].Value.ToString();
            string _payment_type = row.Cells["PAYMENT_TYPE"].Value.ToString();
            string _create_user = DoSomething.UserName_Param;
            string _create_date = DateTime.Today.Date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
            string _create_time = DateTime.Now.ToString("HH:mm:ss");
            dt2.Rows.Add(_item_code, _item_name, _store_code, _trans_no, _invoice_no, _trans_type,
                _item_netPrice, _qty, _total_netPrice, _item_priceWithVAT, _total_priceWithVAT, _item_notebookPrice, _total_notebookPrice, _item_difference, _total_difference,
                _item_costPrice, _total_costPrice, _tax_percentage, _tax_val, _trans_date, _user_add, _repres_no, _notes, _payment_type,
                _create_user, _create_date, _create_time);
        }
        if (Rows.Count > 0)
        {
            sCommand.Append(string.Join(",", Rows));
            sCommand.Append(";");
            using (MySqlConnection con = new MySqlConnection("User Id='" + DoSomething.Uid_Param + "';Pwd='" + DoSomething.Password_Param + "';DATA SOURCE='" + DoSomething.Server_Param + "';Initial Catalog='" + DoSomething.Database_Param + "';persist security info=true;CharSet=utf8; "))
            {
                con.Open();
                using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), con))
                {
                    myCmd.CommandType = CommandType.Text;
                    myCmd.ExecuteNonQuery();
                }
                con.Close();
            }
        }

but after running i got error :

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CSH','admin','2021-03-06','18:05:01'),('1080974','ميكروييف ترست بل' at line 1'

i don't know what is wrong with this code can any one help me ?

Please post the value of `sCommand` just before it's executed & the full error message. — Alex K., Mar 06 '21 at 15:04
MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CSH','admin','2021-03-06','18:05:01'),('1080974','ميكروييف ترست بل' at line 1' — Mohamad Salama, Mar 06 '21 at 15:12
Your values in the sCommand string need to be in parenthesis, change your sCommand definition line to end in "...) VALUES (" and your final append to ");" — Jeffrey, Mar 06 '21 at 15:18
no it was working before but i dont know why this error come now ? — Mohamad Salama, Mar 06 '21 at 15:37
I would add a messagebox or a console writeline method after you finish building your string to output your sql code and analyze it for errors. It also looks like you are appending data to the string without removing the first dataset. — Jeffrey, Mar 06 '21 at 15:52
For money, use `decimal` instead of `float`. See https://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql/13030389 — Tu deschizi eu inchid, Mar 06 '21 at 16:08
You should use prepared statements instead of building the SQL query yourself. See other questions like https://stackoverflow.com/questions/11070434/using-prepared-statement-in-c-sharp-with-mysql — Progman, Mar 06 '21 at 16:43

score 0 · Answer 1 · answered Mar 06 '21 at 16:35

Thanks for all of you i found the problem

it was one of the bulk rows in the note column it was like this : 0555164000 \ عبدالرحمن \

so it contains backslash to solve it i replaced it with '-'

row.Cells["NOTES"].Value.ToString().Replace(@"\", "-")

and now it works fine

have an error in SQL syntax in C# application

1 Answers1