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

  • Question

  • User-2091523492 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 :

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


    the code for the table is :

    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
    )


    please help out to figure why the emp_id and emp_name arent getting inserted properly :(


    Thanks & Regards

    Joy 


     


     

    Sunday, January 9, 2011 11:37 PM

Answers

  • User-129899387 posted

    Hi,

    The stored procedure is wrong.  It should be: 

    INSERT INTO Employee_CTC(emp_id,emp_name,CTC) VALUES(e_id,e_name,emp_CTC);


     Also, I recommend you to remove Create table statement from stored procedure - this is an unusal practice.


    Cheers 


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 10, 2011 7:35 AM

All replies

  • User-129899387 posted

    Hi,

    Could you send stored procedure.

    Thanks 

    Monday, January 10, 2011 4:22 AM
  • User-2091523492 posted

     the stored proc is :

    CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_InsertCTC`(e_id VARCHAR(20),e_name VARCHAR(50),emp_ctc DECIMAL(10,2))
    BEGIN
    	CREATE TABLE IF NOT EXISTS Employee_CTC(emp_id VARCHAR(20),emp_name VARCHAR(50),CTC DECIMAL(10,2));
    	INSERT INTO Employee_CTC(emp_id,emp_name,CTC) VALUES(emp_id,emp_name,emp_CTC);
        END$$


     

    Monday, January 10, 2011 6:19 AM
  • User-129899387 posted

    Hi,

    The stored procedure is wrong.  It should be: 

    INSERT INTO Employee_CTC(emp_id,emp_name,CTC) VALUES(e_id,e_name,emp_CTC);


     Also, I recommend you to remove Create table statement from stored procedure - this is an unusal practice.


    Cheers 


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 10, 2011 7:35 AM
  • User-2091523492 posted

    oh thanks for pointing out .. it was a silly mistake .... actually it was an temporary table ... after the use it was supposed to b truncated !! ... so thts d basic reason i created the table inside stored proc....


    and thanks for pointing my mistake out :)  

    Monday, January 10, 2011 7:44 AM
  • User35911766 posted

    Hi

    I am trying as it is with ur code in my application, but i can not insert bulk using datatable and mysqldataadapter in mysql store procedure.

    can you please tell me how to do it in mysql.

    Thank you in advance.

    can you please attach documentation through mail:: sivakishore.teru@hotmail.com

    Thursday, April 18, 2013 7:23 AM
  • User35911766 posted
    Ok thank you so much. I got it how to do it. But i have one doubt,while inserting the record can i check that record is already present in database or not. If present can i delete and reinsert? Can you please give a reply for this
    Sunday, April 21, 2013 2:14 AM
  • User35911766 posted

    Yes. thank you so much i tested this, in this scenario also it is working fine.

    thank you

    Monday, April 22, 2013 5:02 AM
  • User35911766 posted

    Hi,

    I given all parameters correctly but it is showing the error "input string is not in a correct format"

    can you pls tell me how to resolve this please.

    Friday, April 26, 2013 2:59 AM