Solved

Update mySQL DB with a SQL Select Statement

Posted on 2013-11-24
11
578 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:Mplsjojo
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39673317
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
 

Author Comment

by:Mplsjojo
ID: 39673344
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
 
LVL 24

Expert Comment

by:chaau
ID: 39673361
Just wondering if your ODBC DSN is configured properly. Does it have a read-only flag?
0
 

Author Comment

by:Mplsjojo
ID: 39673379
I had my hosting company involved and they said the connection is set up for read & write.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 39675557
I recommend simplifying to isolate the problem. What happens if you do this:

SQL = "Update person set FirstName ='Joe' WHERE PersonID = 1 ;"
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 39675562
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
 

Author Comment

by:Mplsjojo
ID: 39676443
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
 
LVL 24

Assisted Solution

by:chaau
chaau earned 166 total points
ID: 39676466
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
 
LVL 46

Assisted Solution

by:fritz_the_blank
fritz_the_blank earned 167 total points
ID: 39677801
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
 
LVL 28

Accepted Solution

by:
sybe earned 167 total points
ID: 39680100
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Creating and Managing Databases with phpMyAdmin in cPanel.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now