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 ?
sCommandjust before it's executed & the full error message.