I need to execute two SQL statements one after the other. I’m using MySQL and prepared statement. First I need to select some data from a table and then I need to combine that with some other data and insert that into another table. Somehow doing this causes the debugger to “quit unexpectedly”. If I do one or the other it works fine so I assume there’s something wrong with how I have this structured. I should also add that I’m looping through an array of data and this get performed about 100 times.
What I do is open a connection to the db then I execute the first query.
[code] Dim ps1 As MySQLPreparedStatement
ps1=db.Prepare(“SELECT SLLF_ID
, WorkStateCode
FROM Cvent_Contacts
WHERE Cvent_ID
= ?;”)
ps1.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps1.Bind(0, vCvent_ID) 'Cvent_ID
rs=ps1.SQLSelect[/code]
Then the second one:
[code] Dim ps2 As MySQLPreparedStatement
ps2 = db.Prepare(“INSERT INTO Cvent_Events (event_id, event_code, event_title, event_startdate, event_city, event_state, event_statecode, cvent_contact_id, source_id, sllf_contact_id) VALUES (?,?,?,?,?,?,?,?,?,?);”)
ps2.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(2, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(3, MySQLPreparedStatement.MYSQL_TYPE_DATETIME)
ps2.BindType(4, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(5, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(6, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(7, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps2.BindType(8, MySQLPreparedStatement.MYSQL_TYPE_DOUBLE)
ps2.BindType(9, MySQLPreparedStatement.MYSQL_TYPE_DOUBLE)
ps2.Bind(0, aEventDetails(0)) 'event_id
ps2.Bind(1, aEventDetails(1)) 'event_code
ps2.Bind(2, aEventDetails(2)) 'event_title
ps2.Bind(3, vNewMySQLDateTime) 'event_startdate
ps2.Bind(4, aEventDetails(4)) 'event_city
ps2.Bind(5, aEventDetails(5)) 'event_state
ps2.Bind(6, aEventDetails(6)) 'event_statecode
ps2.Bind(7, aEventContactData(0)) 'cvent_contact_id
ps2.Bind(8, aEventContactData(1)) 'source_id
ps2.Bind(9, vSLLF_ID) 'sllf_contact_id
ps2.SQLExecute[/code]
In trying to solve this I’ve been putting in a db.commit and db.close in a couple of places and that hasn’t helped.
Any suggestions as to how to structure this?
Thanks.