VB6 mySQL update problem

Question

Hey all, i am having a weird problem with trying to update a record in my mySQL 5 database using VB6.

This is my code when i log in:

connDB
Set rst = New ADODB.Recordset

strSQL = "SELECT id, fName, lName, theCode, theDate, clockin FROM clockinout WHERE theCode = '" & theUsersUniqueID & "' AND theDate = '" & Format(Now, "YYYY/MM/DD") & "'"
          rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic

If rst.EOF Then
    rst.AddNew
    rst!FName = userFNmae
    rst!LName = userLName
    rst!theCode = theUsersUniqueID
    rst!theDate = Format(Now, "YYYY/MM/DD")
    rst!clockin = Format(Now, "YYYY/MM/DD HH:MM:SS")
Else
    rst!clockin = Format(Now, "YYYY/MM/DD HH:MM:SS")
End If

rst.Update
rst.Close
Set rst = Nothing
conn.Close

This works just fine without any errors. However, when i log out using this code:

 connDB
 Set rst = New ADODB.Recordset

 strSQL = "SELECT id, fName, lName, theCode, theDate, clockout FROM clockinout WHERE theCode = '" & theUsersUniqueID & "' AND theDate = '" & Format(Now, "YYYY/MM/DD") & "'"
 rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic

 If Not rst.EOF Then
     rst!clockout = Format(Now, "YYYY/MM/DD HH:MM:SS")
 End If

 rst.Update
 rst.Close
 Set rst = Nothing
 conn.Close

It comes out with an error saying its EOF since it can not find the record for some reason... Its in there because it worked on the first login. Strangely, when i take out clockout and replace it with clockin it FINDS THE RECORD but can not update it since rst!clockout was not in the query!!!! But when i do put it back into the query in place of clockin, it gives me the EOF error....

My database record looks like this:

 ID | fName | lName  | theCode | theDate    | clockin             | clockout         |
 26  Bob     Barker   5810      2010/08/02   2010-08-02 02:44:28   0000-00-00 00:00:00

Any help would be great as i have no idea why this simple update is giving me such a hard time..

David

I have also tested the query in the mysql query browser and all works fine...

SELECT id, fName, lName, theCode, theDate, clockin, clockout FROM clockinout WHERE theCode = '5810' AND theDate = '2010/08/02';

Answer 1

I believe that all you'll need is to add clockout in your original query, but it shouldn't be replacing clockin. You don't have to do anything with clockout until you reach the log out section, but it does need to be in the query from the start.

EDIT: Based on the error you gave in your comment, it sounds like the problem lies in the existing value for clockout in your database. I think 0000-00-00 00:00:00 might be the source of the problem; try using some other date, or using NULL instead.

http://forums.mysql.com/read.php?37,49727,54030#msg-54030