Excel VBA To Store All ListBox Items To MySQL Workbench

Question

i have the following sql syntax below for storing data in my Excel Userform Listbox to mysql database. The syntax works alright but the issue is that instead of storing all the data in the ListBox, it stores on the first row. I would rather like to store all the values in the ListBox. Is there a away i can save all the values in the ListBox to mysql database? Below are my sql syntax

  'sales data components
  db_date = frmMySales.ListBox3.Column(0, i)
  db_product_name = frmMySales.ListBox3.Column(1, i)
  db_product_category = frmMySales.ListBox3.Column(2, i)
  db_customer_name = frmMySales.ListBox3.Column(3, i)
  db_unit_of_sale = frmMySales.ListBox3.Column(4, i)
  db_qty = frmMySales.ListBox3.Column(5, i)
  db_sales_price = frmMySales.ListBox3.Column(6, i)
  db_total_sales = frmMySales.ListBox3.Column(7, i)
  db_profit = frmMySales.ListBox3.Column(8, i)
  db_total_purchases = frmMySales.ListBox3.Column(9, i)

  For i = 0 To frmMySales.ListBox3.ListCount - 1

 'posting to sales and profit database
  sql = "INSERT INTO mydb_stock.sql_product_sales(sales_transactn_date, invoice_number, " & _
     "product_name_ID, " & _
     "category_ID, " & _
     "customer_ID, " & _
     "unit_ID, " & _
     "product_qty, " & _
     "sales_price, " & _
     "total_sales, " & _
     "total_purchases, " & _
     "profit_loss) " & _
     "VALUES( Now(), '" & db_date & _
     "',(SELECT product_name_ID FROM mydb_stock.sql_product_name WHERE product_name = '" &      db_product_name & _
     "'),(SELECT category_ID FROM mydb_stock.sql_product_category WHERE category_name =  '" & db_product_category & _
     "'),(SELECT customer_ID FROM mydb_stock.sql_customer WHERE customer_name = '" & db_customer_name & _
     "'),(SELECT unit_ID FROM mydb_stock.sql_product_unit WHERE unit_name = '" & db_unit_of_sale & _
     "')," & db_qty & _
     ",  " & db_sales_price & _
     ",  " & db_total_sales & _
     ",  " & db_total_purchases & _
     ",  " & db_profit & ")"
Next i
con.Execute sql, RecordsAffected

con.CommitTrans
MsgBox "Transaction Saved", vbDefaultButton2, "Sales Transaction"

Answer 1

• The lines of code responsible for retrieving data from cells and executing SQL should be included within the For loop.

For i = 0 To frmMySales.ListBox3.ListCount - 1
      'sales data components
      db_date = frmMySales.ListBox3.Column(0, i)
      db_product_name = frmMySales.ListBox3.Column(1, i)
      db_product_category = frmMySales.ListBox3.Column(2, i)
      db_customer_name = frmMySales.ListBox3.Column(3, i)
      db_unit_of_sale = frmMySales.ListBox3.Column(4, i)
      db_qty = frmMySales.ListBox3.Column(5, i)
      db_sales_price = frmMySales.ListBox3.Column(6, i)
      db_total_sales = frmMySales.ListBox3.Column(7, i)
      db_profit = frmMySales.ListBox3.Column(8, i)
      db_total_purchases = frmMySales.ListBox3.Column(9, i)

     'posting to sales and profit database
      sql = "INSERT INTO mydb_stock.sql_product_sales(sales_transactn_date, invoice_number, " & _
         "product_name_ID, " & _
         "category_ID, " & _
         "customer_ID, " & _
         "unit_ID, " & _
         "product_qty, " & _
         "sales_price, " & _
         "total_sales, " & _
         "total_purchases, " & _
         "profit_loss) " & _
         "VALUES( Now(), '" & db_date & _
         "',(SELECT product_name_ID FROM mydb_stock.sql_product_name WHERE product_name = '" &      db_product_name & _
         "'),(SELECT category_ID FROM mydb_stock.sql_product_category WHERE category_name =  '" & db_product_category & _
         "'),(SELECT customer_ID FROM mydb_stock.sql_customer WHERE customer_name = '" & db_customer_name & _
         "'),(SELECT unit_ID FROM mydb_stock.sql_product_unit WHERE unit_name = '" & db_unit_of_sale & _
         "')," & db_qty & _
         ",  " & db_sales_price & _
         ",  " & db_total_sales & _
         ",  " & db_total_purchases & _
         ",  " & db_profit & ")"
    con.Execute sql, RecordsAffected
Next i

con.CommitTrans
MsgBox "Transaction Saved", vbDefaultButton2, "Sales Transaction"