locked
Inside foreach loop executenonquery is not working RRS feed

  • Question

  • Hi,

    I am very new to C# programming. I am facing one problem to execute insert query inside foreach loop.

    conn.Open();      

    foreach(DataRowrow1 indt.Rows)

    { CustNo = (row1[

    "CustNo"].ToString());

    CustName = (row1[

    "CustName"].ToString());

    cmd.CommandText =

    "insert into mytable(id,CustNo,CustName) values("+ id+ ","+ CustNo + ",'"+ CustName + "')";

    cmd.ExecuteNonQuery(); --------------> if i give this statement outside the foreach loop its inserting last row into table. but i need to insert all data table rows.

    }     

     conn.Close();

    Whats thew wrong in the code?

    please give your suggestion.

    thank you

    Friday, December 14, 2012 6:11 PM

Answers

  • Seems like your trying to enter values that contain symbols used for other means in your specific sql.

    Like productName = Thommy's Soap will encounter an error as your insert string would looke like:

    ..., 'Thommy's Soap', ... --> syntax error

    Use parameters instead... prevents trouble with syntax errors and hacking attempts.

    E.g.:

    CustNo = (row1["CustNo"].ToString());
    CustName = (row1["CustName"].ToString());
    
    cmd.CommandText = "insert into mytable(id,CustNo,CustName)
        values( @id, @CustNo, @CustName)";
    
    cmd.Parameters.AddWithValue("id", id);
    cmd.Parameters.AddWithValue("CustNo", CustNo);
    cmd.Parameters.AddWithValue("CustName", CustName);
    
    cmd.ExecuteNonQuery();
    
    cmd.Parameters.Clear();

    And if your id is identity then you don't have to set it specifically.

    Edit: Corrected code from Add() to AddWithValue(). My wrong.

    As reference see:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx


    • Marked as answer by neel24 Monday, December 17, 2012 6:24 PM
    • Edited by Backgammon89 Monday, December 17, 2012 6:51 PM Minor mistake in methodcalling.
    Friday, December 14, 2012 11:48 PM

All replies

  • The id field is the same for all customers - it's not updated in the loop.

    There might be other things as well.


    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    Friday, December 14, 2012 8:56 PM
  • that key value is a foreign key.Primary key is identity.

    if i give Executenonquery statement inside for each loop i am getting error like below,

    {"Incorrect syntax near 'ROURKE'.\r\nUnclosed quotation mark after the character string ')'."}

    Friday, December 14, 2012 11:08 PM
  • Seems like your trying to enter values that contain symbols used for other means in your specific sql.

    Like productName = Thommy's Soap will encounter an error as your insert string would looke like:

    ..., 'Thommy's Soap', ... --> syntax error

    Use parameters instead... prevents trouble with syntax errors and hacking attempts.

    E.g.:

    CustNo = (row1["CustNo"].ToString());
    CustName = (row1["CustName"].ToString());
    
    cmd.CommandText = "insert into mytable(id,CustNo,CustName)
        values( @id, @CustNo, @CustName)";
    
    cmd.Parameters.AddWithValue("id", id);
    cmd.Parameters.AddWithValue("CustNo", CustNo);
    cmd.Parameters.AddWithValue("CustName", CustName);
    
    cmd.ExecuteNonQuery();
    
    cmd.Parameters.Clear();

    And if your id is identity then you don't have to set it specifically.

    Edit: Corrected code from Add() to AddWithValue(). My wrong.

    As reference see:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx


    • Marked as answer by neel24 Monday, December 17, 2012 6:24 PM
    • Edited by Backgammon89 Monday, December 17, 2012 6:51 PM Minor mistake in methodcalling.
    Friday, December 14, 2012 11:48 PM
  • Hi BackGammon,

    Thank you.

    Its working.Instead of Add used AddWithValue to add parameter value.

    Monday, December 17, 2012 6:24 PM
  • Thank you..its working

    Monday, December 17, 2012 6:25 PM