locked
problem in mysql Bulk insert using datatable and mysqldataadapter RRS feed

  • Question

  • User-1169581568 posted

    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

    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 ;

     

    Monday, October 3, 2016 8:57 AM

All replies