Error Updating Record

I've made some major modifications to my program, one of them being the whole user registration.

The problem I'm having is when I try update a record (of a mysql database on a server) it gives me an error. I don't understand why as I don't think I've done anything wrong and if I change the recordsets to a MS Access database (not server) it works fine. Anyone got any ideas?

Error -2147467259: "Query-based Update Failed because the row to update could not be found."


Public Sub NUActivate()
Dim UpSQL1 As String
Dim UpSQL2 As String
'activates the new user.
ConnectMySQL

If MySQLError = True Then
'go to error screen
SSTab1.TabEnabled(5) = True
SSTab1.Tab = 5
SSTab1.TabEnabled(1) = False

lblError.Caption = "An Error has occurred trying to Save your details. " & _
"Please make sure you are connected to the internet and try again. " & _
"if the problem persists, please contact technical support."

cmdTryAgain.Tag = "2"
CmdBack.Enabled = False
Command1.Enabled = False
Command3.Enabled = True
DisconnectMySQL
Exit Sub
End If

gPrs.Open "SELECT * FROM Users WHERE User_ID=" & sqlUserID, gCnn, adOpenForwardOnly, adLockOptimistic
If MySQLRecordCount("Users", "User_ID=" & sqlUserID) <= 0 Then
'no records, unsuccessful activation
cmdTryAgain.Tag = 0
SSTab1.TabEnabled(5) = True
SSTab1.Tab = 5
SSTab1.TabEnabled(1) = False
lblError.Caption = "Error: Your User Account cannot be found." & vbCrLf & _
"Please try again or contact technical support."

CmdBack.Enabled = False
Command1.Enabled = False
Command3.Enabled = True
gPrs.Close
DisconnectMySQL
Exit Sub
End If

'if here then ready to save details.

SSTab1.TabEnabled(3) = True
SSTab1.Tab = 3
SSTab1.TabEnabled(2) = False
Command1.Enabled = False
CmdBack.Enabled = False
Command3.Enabled = False
DoEvents

adOpenDynamic, adLockOptimistic

For z = 0 To 5
gPrs(txtUDetails(z).Tag).Value = txtUDetails(z)
Next

gPrs.Update
gPrs.Close
DisconnectMySQL

Hi,
This part of the code raises some concerns in my view:

....
DoEvents

adOpenDynamic, adLockOptimistic ' <-----------------??????

For z = 0 To 5
gPrs(txtUDetails(z).Tag).Value = txtUDetails(z)
Next

gPrs.Update
gPrs.Close
....

Then you might wnat to move Update inside the For ....Next, or better yet
use the ADO connection's Execute method to run the "Update ..." sql statement.


Shurik.

Sorry, slight mistake in the code posted. It should be


...
adOpenForwardOnly, adLockOptimistic
...


This is the recommended way of connecting to a MySQL database server. It's never given me any problems before.

It wouldn't help to move the Update inside the For...Next loop as then I'd be updating the same record multiple times. The For...Next loop is setting values for different fields. Besides that, I've tried changing the code to just set a field with a set value and then immediately updating instead of the for...next loop. e.g.


gPrs("Name")="John"
gPrs.Update


This produces the same error.

Right,

I did not notice you're looping through the fields of teh same record and not throught eh records...

I'd again suggesting to run:


gCnn.Execute "Update Users Set Field1= '" & YourParameter & "',....."


instead of using the recordset object

your select query would probably returning an EOF and BOF. I suggest checking the recordset whether it is EOF and BOF before conducting any updates to the table.

and as already mentioned by Shurik, what does the adOpenForwardOnly, adOpenDynamic doing there (before the for loop statement) ?

Thanks Guys, well after lots of testing I'm just going to have to settle for gcnn.execute "Update ...

To answer duane, I've tested and it reports that there is 1 record and eof is false.

I didn't notice it before, thought you guys were talking about the adOpenForwardOnly, adOpenDynamic in the gPrs.Open statement, I think it was part of a comment that I obviously didn't remove properly when posting. I apologise. ;)