Update mySQL DB with a SQL Select Statement
Posted on 2013-11-24
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 ;"
Do While Not RS.EOF
RS("FirstName") = "Joe"
Set RS = Nothing