hii i am having a strange problem in mysql insert using dataadapter and datatable.... i am not able to figure it out what is the actual problem .. here is the code snippet :
Only one row insert and then error givien
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 'SP_InsertCTC; SP_InsertCTC; SP_InsertCTC; SP_InsertCTC' at line 1
<div>
publicvoidBulkCopyCTC(List<EmployeeDet> list){DataTable dt =newDataTable();
dt.Columns.Add(newDataColumn("employee_id",typeof(System.String)));
dt.Columns.Add(newDataColumn("employee_name",typeof(System.String)));
dt.Columns.Add(newDataColumn("emp_ctc",typeof(System.Decimal)));foreach(EmployeeDet item in list){DataRow dr = dt.NewRow();
dr["employee_id"]= item.GetID();
dr["employee_name"]= item.GetName();
dr["emp_ctc"]= item.GetCTC();
dt.Rows.Add(dr);}MySqlConnection con =newMySqlConnection(newConnectionUtils().GetConnectionString());if(con.State==ConnectionState.Open){
con.Close();}
con.Open();MySqlCommand cmd =newMySqlCommand("SP_InsertCTC", con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters.Add("?e_id",MySqlDbType.String).SourceColumn="employee_id";
cmd.Parameters.Add("?e_name",MySqlDbType.String).SourceColumn="employee_name";
cmd.Parameters.Add("?emp_ctc",MySqlDbType.Decimal).SourceColumn="emp_ctc";MySqlDataAdapter da =newMySqlDataAdapter();
da.InsertCommand= cmd;
da.UpdateBatchSize=100;int records = da.Update(dt);Response.Write("<script>alert('inserted "+ records +" Rows')</script>");
con.Close();}}
the problem is the decimal field is getting inserted totally fine.. but in case of employee id and employee name no values are getting inserted ... not able to figure it out why .. can someone help me....
Hi sumantKumarnit,
Form you error message, it indicate that there have incorrect in you storedproduce. So, you could try to replace your storedproduce with the code below:
CREATE PROCEDURE SP_InsertCTC
( @e_id VARCHAR(20), @e_name VARCHAR(50), @emp_ctc DECIMAL(10,2))
as
BEGIN
INSERT INTO Employee_CTC('emp_id','emp_name','CTC') VALUES(@e_id ,@e_name,@emp_ctc);
END
Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.
Best regards
Cathy
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
None
0 Points
3 Posts
problem in mysql Bulk insert using datatable and mysqldataadapter
Oct 03, 2016 08:57 AM|sumantKumarnit|LINK
hii i am having a strange problem in mysql insert using dataadapter and datatable.... i am not able to figure it out what is the actual problem .. here is the code snippet :
Only one row insert and then error givien
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 'SP_InsertCTC; SP_InsertCTC; SP_InsertCTC; SP_InsertCTC' at line 1
<div>the problem is the decimal field is getting inserted totally fine.. but in case of employee id and employee name no values are getting inserted ... not able to figure it out why .. can someone help me....
Table
Store Procedure
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_InsertCTC`( e_id VARCHAR(20), e_name VARCHAR(50), emp_ctc DECIMAL(10,2))
BEGIN
INSERT INTO Employee_CTC(emp_id,emp_name,CTC) VALUES(e_id ,e_name,emp_ctc);
END $$
DELIMITER ;
Star
8670 Points
2882 Posts
Re: problem in mysql Bulk insert using datatable and mysqldataadapter
Oct 04, 2016 10:57 AM|Cathy Zou|LINK
Hi sumantKumarnit,
Form you error message, it indicate that there have incorrect in you storedproduce. So, you could try to replace your storedproduce with the code below:
Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.
Best regards
Cathy
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
None
0 Points
3 Posts
Re: problem in mysql Bulk insert using datatable and mysqldataadapter
Oct 04, 2016 11:23 AM|sumantKumarnit|LINK
hi
thanks for your reply,
but,this Procedure not execute properly ,
i am not able to figure out where is the problem because one row is inserted then error show
Star
8670 Points
2882 Posts
Re: problem in mysql Bulk insert using datatable and mysqldataadapter
Oct 13, 2016 02:22 AM|Cathy Zou|LINK
Hi sumantKumarnit,
Is there any error arise in your application?
You also could refer to the following links:
http://www.inmotionhosting.com/support/website/database-troubleshooting/error-1064
http://stackoverflow.com/questions/38802392/mysql-error-you-have-an-error-in-your-sql-syntax-check-the-manual-that-corresp
https://forum.joomla.org/viewtopic.php?t=928785
Best regards
Cathy
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.