problem in mysql Bulk insert using datatable and mysqldataadapter RSS

• • Reply
  

  sumantKumarn...

  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>

  public void BulkCopyCTC(List<EmployeeDet> list)
      {
          DataTable dt = new DataTable();
          dt.Columns.Add(new DataColumn("employee_id",typeof( System.String)));
          dt.Columns.Add(new DataColumn("employee_name", typeof(System.String)));
          dt.Columns.Add(new DataColumn("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 = new MySqlConnection(new ConnectionUtils().GetConnectionString());
          if (con.State == ConnectionState.Open)
          {
              con.Close();
          }
          con.Open();
          MySqlCommand cmd = new MySqlCommand("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 = new MySqlDataAdapter();
          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....

  Table

  CREATE TABLE `employee_ctc` (
  	`emp_id` VARCHAR(20) NULL DEFAULT NULL,
  	`emp_name` VARCHAR(50) NULL DEFAULT NULL,
  	`CTC` DECIMAL(10,2) NULL DEFAULT NULL
  )

  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 ;

   

  </div>
• • Reply
  

  Cathy Zou

  Contributor

  6050 Points

  2084 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:

  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.
  
• • Reply
  

  sumantKumarn...

  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

   

• • Reply
  

  Cathy Zou

  Contributor

  6050 Points

  2084 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

  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.