Solved

Update mySQL DB with a SQL Select Statement

Posted on 2013-11-24
11
583 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Prevent certain words from being typed in a form 6 57
Export Data from MySql Using PHP 16 67
Code not executing correctly. 3 44
MySQL TEXT fields 11 16
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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