Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update mySQL DB with a SQL Select Statement

Posted on 2013-11-24
11
Medium Priority
?
589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 25

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 25

Expert Comment

by:chaau
ID: 39673361
Just wondering if your ODBC DSN is configured properly. Does it have a read-only flag?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 25

Assisted Solution

by:chaau
chaau earned 498 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 501 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 501 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 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