Nebula

Nebula

  • 1.8k
  • 36
  • 900

Looping column data insert into SQL Server database

May 5 2019 7:23 PM
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:
  1. private void button3_Click(object sender, EventArgs e) {   
  2.    using (SqlConnection conn = new SqlConnection(";Trusted_Connection=False"))   
  3.    using (SqlCommand comm = new SqlCommand())   
  4. {   
  5.    comm.Connection = conn;   
  6.    conn.Open();   
  7.    for (int i = 1; i < dataGridView2.Rows.Count; i++) {    
  8.       comm.CommandText = "INSERT INTO SOLine (CustID,Bookcode,Barcode,Description,Price,Disc,Qty) VALUES ('2058 KBC',@bookcode,@barcode,@desc,@price,@disc,@qty)";   
  9.       SqlParameter bookcode = comm.Parameters.AddWithValue("@bookcode", dataGridView2.Rows[i].Cells["BOOKCODE"].Value);   
  10.       SqlParameter barcode = comm.Parameters.AddWithValue("@barcode", dataGridView2.Rows[i].Cells["3"].Value);   
  11.       SqlParameter desc = comm.Parameters.AddWithValue("@desc", dataGridView2.Rows[i].Cells["4"].Value);   
  12.       SqlParameter price = comm.Parameters.AddWithValue("@price", dataGridView2.Rows[i].Cells["5"].Value);   
  13.       SqlParameter disc = comm.Parameters.AddWithValue("@disc", dataGridView2.Rows[i].Cells["6"].Value);   
  14.       //SqlParameter qty = comm.Parameters.AddWithValue("@qty", dataGridView2.Rows[i].Cells["7"].Value);   
  15.    
  16.    if (dataGridView2.Rows[i].Cells["BOOKCODE"].Value == null) { bookcode.Value = DBNull.Value; }   
  17.    if (dataGridView2.Rows[i].Cells["3"].Value == null) { barcode.Value = DBNull.Value; }   
  18.    if (dataGridView2.Rows[i].Cells["4"].Value == null) { desc.Value = DBNull.Value; }   
  19.    if (dataGridView2.Rows[i].Cells["5"].Value == null) { price.Value = DBNull.Value; }   
  20.    if (dataGridView2.Rows[i].Cells["6"].Value == null) { disc.Value = DBNull.Value; }   
  21. // if (dataGridView2.Rows[i].Cells["7"].Value == null)  // {  //       qty.Value = DBNull.Value;  // }   
  22.    
  23.    for (int q = 7; q <= dataGridView2.Columns.Count; q++) //dataGridView2.Columns.Count {   
  24.       int w = 1;                       
  25.       w  
  26.       w++;                       
  27.       comm  
  28.       comm.Parameters.Add("@qty", SqlDbType.Int).Value = dataGridView2.Rows[w].Cells[q].Value;                       
  29.       comm  
  30.       comm.Parameters.Clear();   
  31.    }                     comm.ExecuteNonQuery();                   
  32.    comm  
  33.    comm.Parameters.Clear();   
  34. }   
  35.    MessageBox.Show("Save SQL");   
  36.    
  37. //try   
  38. //{    
  39. //    comm.ExecuteNonQuery();   
  40. //}   
  41. //catch (Exception ex)   
  42. //{   
  43. //    MessageBox.Show(ex.ToString());   
  44. //}   
  45. }   
  46. }

Answers (6)