Answered by:
Inside foreach loop executenonquery is not working

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