Inserting listbox data using Mysql Preparestatement

Databases
1

Hi,

I want to insert the content of a listbox into mysql database. this code shows no error but not inserting,

If mDB.Connect Then
  mIsConnected = True
Else
  mIsConnected = False
End If

dim ps as MySQLPreparedStatement
ps =  mDB.Prepare("INSERT INTO transaksi (item_code,item_name) VALUES (?,?)")

dim i as integer
for i = 0 to Listbox1.ListCount - 1
  
  dim v1,v2 as String
  v1 = l_serial.text
  v2 = Listbox1.Cell(i,2)
  
  
  ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.Bind(0, v1)
  
  ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.Bind(1, v2)
  
  ps.SQLExecute
  
  
next i

if i am using other query like deleting, it works, but when I do inserting multi rows, its not working.

any helps?

thanks
arief

2

I believe that prepared statements are transactional even if you don’t explicitly call START TRANSACTION

Try adding mDB.SQLExecute("COMMIT") after your loop to commit the transaction.

3

Hi,

I have trying add after loop, but still not working.
I am using older version of xojo 2021 r.1.1

thanks
arief

4

Have you checked for errors after SQLExecute?

if mDB.Error then
  dim error as string = mDB.ErrorMessage
  break
end