Looping column data insert into SQL Server database

Nebula

I am new to C#. My problem is I want to insert the ean, description, bookcode, price and discount into SQL Server with the qty from column 7 and above:

My current code is able to insert the data but the quantity is already defined. I am thinking to do looping, but I got an error "Must declare scalar variable" and I'm not sure my logic is correct.

Here's a screenshot of my SQL Server table:

My code:

private void button3_Click(object sender, EventArgs e) {
using (SqlConnection conn = new SqlConnection(";Trusted_Connection=False"))
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
conn.Open();
for (int i = 1; i < dataGridView2.Rows.Count; i++) {
comm.CommandText = "INSERT INTO SOLine (CustID,Bookcode,Barcode,Description,Price,Disc,Qty) VALUES ('2058 KBC',@bookcode,@barcode,@desc,@price,@disc,@qty)";
SqlParameter bookcode = comm.Parameters.AddWithValue("@bookcode", dataGridView2.Rows[i].Cells["BOOKCODE"].Value);
SqlParameter barcode = comm.Parameters.AddWithValue("@barcode", dataGridView2.Rows[i].Cells["3"].Value);
SqlParameter desc = comm.Parameters.AddWithValue("@desc", dataGridView2.Rows[i].Cells["4"].Value);
SqlParameter price = comm.Parameters.AddWithValue("@price", dataGridView2.Rows[i].Cells["5"].Value);
SqlParameter disc = comm.Parameters.AddWithValue("@disc", dataGridView2.Rows[i].Cells["6"].Value);
//SqlParameter qty = comm.Parameters.AddWithValue("@qty", dataGridView2.Rows[i].Cells["7"].Value);
if (dataGridView2.Rows[i].Cells["BOOKCODE"].Value == null) { bookcode.Value = DBNull.Value; }
if (dataGridView2.Rows[i].Cells["3"].Value == null) { barcode.Value = DBNull.Value; }
if (dataGridView2.Rows[i].Cells["4"].Value == null) { desc.Value = DBNull.Value; }
if (dataGridView2.Rows[i].Cells["5"].Value == null) { price.Value = DBNull.Value; }
if (dataGridView2.Rows[i].Cells["6"].Value == null) { disc.Value = DBNull.Value; }
// if (dataGridView2.Rows[i].Cells["7"].Value == null) // { // qty.Value = DBNull.Value; // }
for (int q = 7; q <= dataGridView2.Columns.Count; q++) //dataGridView2.Columns.Count {
int w = 1;
w
w++;
comm
comm.Parameters.Add("@qty", SqlDbType.Int).Value = dataGridView2.Rows[w].Cells[q].Value;
comm
comm.Parameters.Clear();
} comm.ExecuteNonQuery();
comm
comm.Parameters.Clear();
}
MessageBox.Show("Save SQL");
//try
//{
// comm.ExecuteNonQuery();
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.ToString());
//}
}
}

6 Replies

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

Rafnas T P7y ago
hi,
you are displaying all details in grid, so you can dirctly insert the data into sql
only with one for loop as you did before
private void button3_Click(object sender, EventArgs e) {
   using (SqlConnection conn = new SqlConnection(";Trusted_Connection=False"))
   using (SqlCommand comm = new SqlCommand())
{
   comm.Connection = conn;
   conn.Open();
   for (int i = 1; i < dataGridView2.Rows.Count; i++) {
      comm.CommandText = "INSERT INTO SOLine (CustID,Bookcode,Barcode,Description,Price,Disc,Qty) VALUES ('2058 KBC',@bookcode,@barcode,@desc,@price,@disc,@qty)";
      SqlParameter bookcode = comm.Parameters.AddWithValue("@bookcode", dataGridView2.Rows[i].Cells["BOOKCODE"].Value);
      SqlParameter barcode = comm.Parameters.AddWithValue("@barcode", dataGridView2.Rows[i].Cells["3"].Value);
      SqlParameter desc = comm.Parameters.AddWithValue("@desc", dataGridView2.Rows[i].Cells["4"].Value);
      SqlParameter price = comm.Parameters.AddWithValue("@price", dataGridView2.Rows[i].Cells["5"].Value);
      SqlParameter disc = comm.Parameters.AddWithValue("@disc", dataGridView2.Rows[i].Cells["6"].Value);
      SqlParameter qty = comm.Parameters.AddWithValue("@qty", dataGridView2.Rows[i].Cells["7"].Value);

   if (dataGridView2.Rows[i].Cells["BOOKCODE"].Value == null) { bookcode.Value = DBNull.Value; }
   if (dataGridView2.Rows[i].Cells["3"].Value == null) { barcode.Value = DBNull.Value; }
   if (dataGridView2.Rows[i].Cells["4"].Value == null) { desc.Value = DBNull.Value; }
   if (dataGridView2.Rows[i].Cells["5"].Value == null) { price.Value = DBNull.Value; }
   if (dataGridView2.Rows[i].Cells["6"].Value == null) { disc.Value = DBNull.Value; }
   if (dataGridView2.Rows[i].Cells["7"].Value == null) {      qty.Value = DBNull.Value; }

   comm.ExecuteNonQuery();

   comm.Parameters.Clear();
}
   MessageBox.Show("Save SQL");

}
}
Nebula7y ago
I managed to solved this :)
Nebula7y ago
Hi Rafnas, as you can see my grid.
Bookcode,ean,desc,price and discount is the static column data in grid that I want to insert in sql with different custid which match with the qty.
What i mean q is for loop to get the qty from different custid column (2016MBP,2058KBC...)..
I have 14 records of bookcode, so each custid have 14 records insert into sql. Hope its clear
Rafnas T P7y ago
Hi,
still i did not get you, can you explain what is your exact requirement,
inside for loop why are you looping again.
for (int i = 1; i < dataGridView2.Rows.Count; i++)
{
comm.CommandText = "INSERT INTO SOLine (CustID,Bookcode,Barcode,Description,Price,Disc,Qty) VALUES (@custid,@bookcode,@barcode,@desc,@price,@disc,@qty)";
for (int q = 5; q <= 14; q++) //dataGridView2.Columns.Count
{
//comm.Parameters.Add(new SqlParameter("@custid", SqlDbType.VarChar)).Value = dataGridView2.Rows[0].Cells[q].Value;
comm.Parameters.AddWithValue("@custid", dataGridView2.Rows[0].Cells[q].Value);
comm.Parameters.AddWithValue("@bookcode", dataGridView2.Rows[i].Cells["BOOKCODE"].Value);
comm.Parameters.AddWithValue("@barcode", dataGridView2.Rows[i].Cells["3"].Value);
comm.Parameters.AddWithValue("@desc", dataGridView2.Rows[i].Cells["4"].Value);
comm.Parameters.AddWithValue("@price", dataGridView2.Rows[i].Cells["5"].Value);
comm.Parameters.AddWithValue("@disc", dataGridView2.Rows[i].Cells["6"].Value);
comm.Parameters.AddWithValue("@qty", dataGridView2.Rows[i].Cells[q].Value);
//comm.Parameters.Clear();
}
comm.ExecuteNonQuery();
comm.Parameters.Clear();
}
MessageBox.Show("Save SQL");
}
Nebula7y ago
Hi, I updated my code and if I didnt put the parameter clears (where i hv commented) this error will pop out https://stackoverflow.com/questions/19535486/the-variable-name-has-already-been-declared-variable-names-must-be-unique-w

I need to loop the qty column as it has different value that match with custId as well. Is there other logic should i do to make it right? thanks for helping.
for (int i = 1; i < dataGridView2.Rows.Count; i++)
{
comm.CommandText = "INSERT INTO SOLine (CustID,Bookcode,Barcode,Description,Price,Disc,Qty) VALUES (@custid,@bookcode,@barcode,@desc,@price,@disc,@qty)";
for (int q = 5; q <= 14; q++) //dataGridView2.Columns.Count
{
//comm.Parameters.Add(new SqlParameter("@custid", SqlDbType.VarChar)).Value = dataGridView2.Rows[0].Cells[q].Value;
comm.Parameters.AddWithValue("@custid", dataGridView2.Rows[0].Cells[q].Value);
comm.Parameters.AddWithValue("@bookcode", dataGridView2.Rows[i].Cells["BOOKCODE"].Value);
comm.Parameters.AddWithValue("@barcode", dataGridView2.Rows[i].Cells["3"].Value);
comm.Parameters.AddWithValue("@desc", dataGridView2.Rows[i].Cells["4"].Value);
comm.Parameters.AddWithValue("@price", dataGridView2.Rows[i].Cells["5"].Value);
comm.Parameters.AddWithValue("@disc", dataGridView2.Rows[i].Cells["6"].Value);
comm.Parameters.AddWithValue("@qty", dataGridView2.Rows[i].Cells[q].Value);
//comm.Parameters.Clear();
}
comm.ExecuteNonQuery();
comm.Parameters.Clear();
}
MessageBox.Show("Save SQL");
}
Rafnas T P7y ago
hi,

as per your code you are taking all the values from grid even qty also you are taking from grid, I did not understand why are you doing seperate for loop for qty and in that loop
you are clearing parameter so that you are getting "Must declare scalar variable" error
for (int q = 7; q <= dataGridView2.Columns.Count; q++) //dataGridView2.Columns.Count {

    int w = 1;
    w

    w++;
    comm

    comm.Parameters.Add("@qty", SqlDbType.Int).Value = dataGridView2.Rows[w].Cells[q].Value;
    comm

    comm.Parameters.Clear();

}
Thank you.