how to add more than one record adding in to database

Question

i tried below code.but i could add one row record only added in to mysql database. i entered three rows records but only one row records only added.what is the problem of the below code please any one can help me this problem. for (int i = 0; i { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = con; cmd.CommandText = "insert into salesproductnew(salesid,productname,qty,price,grosstotal)values(@salesid,@productname,@qty,@price,@grosstotal)"; cmd.Parameters.AddWithValue("@salesid", lbinvoice.Text); cmd.Parameters.AddWithValue("@productname", dataGridView1.Rows[i].Cells[2].Value); cmd.Parameters.AddWithValue("@qty", dataGridView1.Rows[i].Cells[3].Value); cmd.Parameters.AddWithValue("@price", dataGridView1.Rows[i].Cells[4].Value); cmd.Parameters.AddWithValue("@grosstotal", dataGridView1.Rows[i].Cells[5].Value); con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Record added ............."); con.Close();

Karthi Keyan · Answer

@Ram, string query = " "; foreach (DataGridViewRow row in dataGridView1.Rows) { if(query == "") query = " insert into salesproductnew(salesid,productname,qty,price,grosstotal) values ('"+ lbinvoice.Text+"','"+row.Cells[2].FormatedValue.ToString()+"','"+row.Cells[3].FormatedValue.ToString()+"','"+row.Cells[4].FormatedValue.ToString()+"','"+row.Cells[5].FormatedValue.ToString()+"'); "; else query = query + " insert into salesproductnew(salesid,productname,qty,price,grosstotal) values ('"+ lbinvoice.Text+"','"+row.Cells[2].FormatedValue.ToString()+"','"+row.Cells[3].FormatedValue.ToString()+"','"+row.Cells[4].FormatedValue.ToString()+"','"+row.Cells[5].FormatedValue.ToString()+"'); "; } SqlCommand cmd = new SqlCommand(query,conn); cmd. ExecuteNonQuery(); Try this.

Rafnas T P · Answer

break point is not with code, you hve to add to your IDE see below video for how to debug using break point https://www.youtube.com/watch?v=7ab4z9u7Q_I

Rafnas T P · Answer

are you sure you have more than one record in your gridview? if yes please add break point and check what is the dataGridView1.Rows.Count value

Kobinath Ram · Answer

i tried foreach instead of forloop same message i got 1 row has been added... foreach (DataGridViewRow row in dataGridView1.Rows) { cmdadd.CommandText = "insert into salesproductnew(salesid,productname,qty,price,grosstotal)values(@salesid,@productname,@qty,@price,@grosstotal)"; cmdadd.Parameters.AddWithValue("@salesid", lbinvoice.Text); cmdadd.Parameters.AddWithValue("@productname", row.Cells[2].Value); cmdadd.Parameters.AddWithValue("@qty", row.Cells[3].Value); cmdadd.Parameters.AddWithValue("@price",row.Cells[4].Value); cmdadd.Parameters.AddWithValue("@grosstotal", row.Cells[5].Value); x += cmdadd.ExecuteNonQuery(); cmdadd.Parameters.Clear(); MessageBox.Show("Record added ............."); }

Karthi Keyan · Answer

Hi ram, why dont you use foreach insteadof for loop. In your code put your message box outside of the for loop. because it will show each time the added. may be you stop the debug after the 1st message box shown. so that would be only one row added. make sure that wasn't the mistake.

Kobinath Ram · Answer

i tried the code which you mention put the message box it said 1 rows has been added. i couln't add more than one row sir. please help me on this.thanks sir

Rafnas T P · Answer

put messegebox outside the loop and try like below code for ( int i = 0; i { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = con; cmd.CommandText = "insert into salesproductnew(salesid,productname,qty,price,grosstotal)values(@salesid,@productname,@qty,@price,@grosstotal)" ; cmd.Parameters.AddWithValue( "@salesid" , lbinvoice.Text); cmd.Parameters.AddWithValue( "@productname" , dataGridView1.Rows[i].Cells[2].Value); cmd.Parameters.AddWithValue( "@qty" , dataGridView1.Rows[i].Cells[3].Value); cmd.Parameters.AddWithValue( "@price" , dataGridView1.Rows[i].Cells[4].Value); cmd.Parameters.AddWithValue( "@grosstotal" ,dataGridView1.Rows[i].Cells[5].Value); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } MessageBox.Show( "Record added ............." );

Amit Gupta · Answer

Although I am quite sure that it was only a mannual error not by your code. Make changes the way you wrote the code and see what happens MySqlCommand cmd = new MySqlCommand(); cmd.Connection = con; con.Open (); int x=0; for ( int i = 0; i { cmd.CommandText = "insert into salesproductnew(salesid,productname,qty,price,grosstotal)values(@salesid,@productname,@qty,@price,@grosstotal)" ; cmd.Parameters.AddWithValue( "@salesid" , lbinvoice.Text); cmd.Parameters.AddWithValue( "@productname" , dataGridView1.Rows[i].Cells[2].Value); cmd.Parameters.AddWithValue( "@qty" , dataGridView1.Rows[i].Cells[3].Value); cmd.Parameters.AddWithValue( "@price" , dataGridView1.Rows[i].Cells[4].Value); cmd.Parameters.AddWithValue( "@grosstotal" , dataGridView1.Rows[i].Cells[5].Value); x+= cmd.ExecuteNonQuery(); cmd.Parameters.Clear (); } con.Close (); if (x>0) MessageBox.Show ( "No of row inserted: " + x); However, your code needs some optimization, but for the timebeing you can try the above and see whats happening.

Kobinath Ram · Answer

ya sir int x=cmd.ExecuteNonQuery(); i tried x becames 1 sir. this time also one record has been added.

Amit Gupta · Answer

Your code seems fine. There might be following reason: 1. Recheck your dataGridView1.Rows.Count, may be the count is 1. 2. Is there any primary key on salesid column, I think thats might the reason that restricts insertion on the same salesid. Did you get any exception there? 3. put a variable while executing the query like int x=cmd.ExecuteNonQuery(); Debug your code and check the value of x, is it 1 or multiple. Good Luck !!

Bidyasagar Mishra · Answer

check in gridview.Rows.count value using debug, is there avilable more than one record.

Rafnas T P · Answer

what is the error are you getting?

Kobinath Ram · Answer

still error while running the code

Rafnas T P · Answer

hi ram, have you got the output

Amit Gupta · Answer

@Kobinath Ram You didn't answered the value of gridview.Rows.count value. What is the value of it? Secondly, have you tried the code, which I posted? What is the value of x when messagebox appears.

Kobinath Ram · Answer

ya sir. i will able to add only one row.

Kobinath Ram · Answer

ya. sir. can you write the code for it. with break point then i can easly understand thanks

Khaja Moizuddin · Answer

You mean you are able to add only one record at a time ? Right

how to add more than one record adding in to database

Answers (18)