Update mySQL DB with a SQL Select Statement

First time user of mySQL.  I converted an Access DB to mySQL DB and I'm trying to adjust my classic ASP code to interact with the new DB.  

I'm able to view records from the mySQL DB but I cannot update them.  I'm trying to do so by cycling through a recordset I find using a SQL SELECT statementand updating data along the way (works great for Access DB & SQL DBs I've been working on for years).  

But for mySQL I am told that the record can't be updated because it cannot be found.  When I test the code I actually have it show me the record data I am trying to update and it shows itself fine, it just won't let me update it.  Gives me this error:
"Microsoft OLE DB Provider for ODBC Drivers error '80004005'  Query-based update failed because the row to update could not be found."

Sample connection and code are below, can anyone help tell me what I'm doing wrong?


Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=xxxx; UID=xxxx; PWD=xxxx; OPTION=3 ;"

Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM person WHERE PersonID = 1 ;"
RS.Open SQL,Conn,1,2
Do While Not RS.EOF
          RS("FirstName") = "Joe"
  RS.Update
RS.Movenext
Loop
Set RS = Nothing
MplsjojoAsked:
Who is Participating?
 
sybeCommented:
If you have a blob-field in the table, there is a possible solution here: http://bugs.mysql.com/bug.php?id=19065, it syas that you should not include the blob-field in the select query.

Anyway, do not use "SELECT *", but specify the fields you want to work with.

And, as said, it is much faster and much better to use an UPDATE statement.
0
 
chaauCommented:
I think you need to call RS.Edit first, like this:
Do While Not RS.EOF
  RS.Edit
          RS("FirstName") = "Joe"
  RS.Update
  RS.Movenext
Loop

Open in new window

0
 
MplsjojoAuthor Commented:
Tried this but I get an error back that the "Object doesn't support this property or method: 'Edit'"

It's been suggested by someone else that I can't update the data because I don't have a Primary Key set in my Table, but when I look at the table I see a field marked as Primary, it's also set as an Auto-Increment field.  I believe both of these values occurred when we converted from the MS Access DB to match the structure we had there.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
chaauCommented:
Just wondering if your ODBC DSN is configured properly. Does it have a read-only flag?
0
 
MplsjojoAuthor Commented:
I had my hosting company involved and they said the connection is set up for read & write.
0
 
fritz_the_blankCommented:
I recommend simplifying to isolate the problem. What happens if you do this:

SQL = "Update person set FirstName ='Joe' WHERE PersonID = 1 ;"
0
 
fritz_the_blankCommented:
If that works, then you know that you can write to the DB. If not, then you know for certain that it's a permissions issue.
0
 
MplsjojoAuthor Commented:
Yes, I was able to write to the DB using your test.  So I know it is updatable.  

I have a few thousand queries in the system (no lie) and I don't want to have to rewrite every single one with an UPDATE in the middle of the recordset.  Since we know the DB can be updated, any idea in the code what is wrong?  I've tried changing the cursortype with no luck.  What else could it be?

The code again is:

Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=xxxx; UID=xxxx; PWD=xxxx; OPTION=3 ;"

Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM person WHERE PersonID = 1 ;"
RS.Open SQL,Conn,1,2
Do While Not RS.EOF
          RS("FirstName") = "Joe"
  RS.Update
RS.Movenext
Loop
Set RS = Nothing
0
 
chaauCommented:
What happens if you change the option to 4  in your connection string?
Also, try to use different locking method:
RS.Open SQL,Conn,1,3
0
 
fritz_the_blankCommented:
Okay good. I have to ask, however, why you would need to cycle through a record set for an update. Doing so is really inefficient. Wouldn't just be easier to execute the one UPDATE command rather than setting a field for each record one at a time? In effect, this:

SQL = "Update person set FirstName ='Joe' WHERE PersonID = 1 ;"

accomplishes the same thing in one step.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.