Solved

Update mySQL DB with a SQL Select Statement

Posted on 2013-11-24
11
580 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
problems with mysql ODBC Connector and vbscript 4 31
updating table data with inner join 9 38
PHP: Insert Data into MySQL 5 42
Passing Parameter to Stored Procedure 4 24
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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