how to add more than one record adding in to database

Kobinath Ram

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 < dataGridView1.Rows.Count; 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();

18 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bidyasagar Mishra9y ago
check in gridview.Rows.count value using debug, is there avilable more than one record.
Rafnas T P9y ago
what is the error are you getting?
Kobinath Ram9y ago
still error while running the code
Rafnas T P9y ago
hi ram,
have you got the output
Amit Gupta9y ago
@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 Ram9y ago
ya sir. i will able to add only one row.
Karthi Keyan9y ago
@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 P9y ago
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
Kobinath Ram9y ago
ya. sir. can you write the code for it. with break point then i can easly understand thanks
Rafnas T P9y ago
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 Ram9y ago
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 Keyan9y ago
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 Ram9y ago
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 P9y ago
put messegebox outside the loop and try like below code
for (int i = 0; i < dataGridView1.Rows.Count; 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 Gupta9y ago
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 < dataGridView1.Rows.Count; 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.
Khaja Moizuddin9y ago
You mean you are able to add only one record at a time ? Right
Kobinath Ram9y ago
ya sir
int x=cmd.ExecuteNonQuery();
i tried
x becames 1 sir. this time also one record has been added.
Amit Gupta9y ago
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 !!