Looping column data insert into SQL Server database

Question

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 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 { 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()); //} } }

Rafnas T P · Answer

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 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" ); } }

Nebula · Answer

I managed to solved this :)

Nebula · Answer

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 P · Answer

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 { 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 //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" ); }

Nebula · Answer

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 { 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 //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 P · Answer

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 { 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.