Updating MySql Tables. SELECT vs. UPDATE

[dstrange]

Hi,
I have a MySQL db with a vb6 app that connects using ADODB. I've been getting some notices about multiple testers adding new/editing records in the same db but the information is getting crossed over to what another user was recording. So basically User A's saved info is getting put into User B's info.

I can't seem to replicate this process but it has been reported a few times so now have to look into it. Would it have something to do with adding my records?
Initially I've been using the 'SELECT fields FROM table' and then .addnew and the finishing off with .update

Should I change this to an update statement instead?
Any advice? Thanks

 

[JoeAtWork]

Can't do any diagnosis without seeing your code.

However, recommended practice is not to use recordsets for updating or inserting, instead create SQL statements and then use the Execute method of the ADO Connection object.

Better yet, if this is supported in MySQL, create stored procedures and then call those with ADO Command objects.


Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[PeterWallace]

How are the records locked ?

or are they ?

Peter

 

[dstrange]

/* example - it's not pretty btw*/

Dim billRec As ADODB.Recordset
Set billRec = New ADODB.Recordset
billRec.CursorLocation = adUseClient
billRec.Properties("Update Criteria") = adCriteriaKey

billRec.Open "SELECT * FROM TABLE WHERE ref = -1 AND visitID= 1 ", myConnection, 3, 3

billRec.AddNew
billRec!pat_num = tempPN
billRec!visit_num = tempVN
billRec.update        
billRec.Close

 

[JoeAtWork]

Well, you are doing an insert, so I don't see how user A could be updating user B's record. Perhaps it is a perception issue - user A and B both insert a record with the same pat_num. Then A opens the newest record with that pat_num, and would get B's record, and assumes B updated his record.

Anyways, a much more efficient way of inserting records:

myConnection.Execute "INSERT INTO Table (pat_num, visit_num) VALUES (" & tempPN & "," & tempVN & ")"

Note - I am assuming both fields are numeric, otherwise I would have put quotes around them. Also, I'm not sure the SQL is valid for MySQL - I am more familiar with MS SQL Server.

Joe Schwarz
Custom Software Developer

http://www.plandev.net