Updates of MySQL database fail using ADO / ASP

[qmi]

I've written an ASP application in VBScript that uses ADO to connect to, read from and update a MySQL database. The read (select) type queries work fine but any attempt to update a table fails with a variety of fairly generic error messages.
eg.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Invalid string or buffer length

The application works fine when pointed to an MS Access database (including updates) but fails with MySQL.

I have all the latest drivers and software on my PC (running Windows NT) and have just installed the version 3.23.36 of MySQL (running on a Unix machine). I've also tried older versions of MySQL (3.22 and 3.21).

An example piece of code follows -

sSQL = "SELECT * FROM UserLogon WHERE ..."

set m_Conn = server.CreateObject("adodb.connection&quot
m_Conn.Open DB_DSN

' open recordset and find user's logon details
set rs = server.CreateObject("adodb.recordset&quot
rs.CursorLocation = adUseServer
rs.Open sSQL, m_Conn, adOpenKeyset, adLockOptimistic

if rs.EOF then ' user name not found
sAttemptLogin = "User name or password invalid"

elseif bCheckExpired(rs("Expires&quot) then
sAttemptLogin = "Your account has expired. Please contact... "

elseif rs("Frozen&quot then
sAttemptLogin = "Your account has been frozen. Please contact..."

elseif rs(&quot;Password&quot <> trim(Request.Form(&quot;Password&quot) then
sAttemptLogin = &quot;User name or password invalid&quot;
rs(&quot;Attempts&quot = rs(&quot;Attempts&quot + 1
if rs(&quot;Attempts&quot >= 5 then
sAttemptLogin = &quot;Too many failed attempts - account frozen. Contact...&quot;
rs(&quot;Frozen&quot = 1
end if
rs.Update

else ' Login successful
rs(&quot;Attempts&quot = 0
Session(&quot;LastOn&quot = rs(&quot;LastOn&quot
rs(&quot;LastOn&quot = Now
rs.Update
sAttemptLogin = &quot;OK&quot;
end if

' clean up recordset and close database connection
rs.Close
set rs = nothing
m_Conn.close
set m_Conn = nothing
-----------

I've tried client-side cursors as well as server-side cursors, different lock types and a variety of other parameters on the recordset open but nothing works. The application fails on the &quot;rs.Update&quot; statement.

Application only fails with MySQL - works perfectly on Access 97 and Access 2000.

Tim.

 

[link9]

Well, the code here looks good, and if it works perfectly with Access, then I would have to say it's a problem in the database --

Maybe double check your data types and such -- make sure that you are allowing nulls where appropriate -- all that sort of stuff --

Compare the structure of the tables from the two databases to ensure that they are exactly the same --

I can't make sense of that error message... are you getting any others? You say you get several generics -- what are some others?


Paul Prewett

 

[qmi]

I checked all the datatypes and other table attributes such as 'required field?' and 'permit zero length string' etc. when I first received the error and it all looked OK.

There should be no errors in that regard - the recordset is opened with an initial query that retrieves a row from the table. During this process, the software layer between the database server and the application (ADO) should derive all attributes for the table and its columns (ie. data type and size plus whether NULLs are allowed etc.) so there should be no subsequent datatype conflicts. Also, the application is not setting any fields to NULL, assigning zero length strings or any other assignment that may cause a problem. From memory, if you attempt to assign an invalid value to a field, the application fails during the assignment statement, not at the update.

I couldn't find/reproduce the other error messages - they were something like &quot;Command failed, no work done&quot;.

I've tried updating the database by executing SQL queries such as &quot;INSERT INTO ...&quot; or &quot;DELETE ...&quot; instead of the ADO Update method and they work OK but have a nasty habit of putting junk messages into the outgoing HTML, so the user sees messages like &quot;Query successful&quot; interspersed throughout the web page.

 

[karaman]

hi ..

have you tried to update it using SQL statement?

like SQL = &quot;Update TableName set FirstField = ' &quot; & Var1 & &quot;', SecondField = '&quot; & Var2 & &quot;' &quot; and so on...

Let me know if you can at huseyink@geo-comm.com

 

[dianal]

Hello, qmi,
May be I'm not right, but I have heard that MySQL do not support dynamic cursors, which you need for updating.
You could check in your case.
Comment line rs.Update and
and see what is the result of
bool = rs.Supports(adUpdate)
If it is false, you have to use other options.
I was pretty sure that MySQL has a command to hide those annoying messages after getting the result but I couldn't find till now.
Hope this helps.
D.

 

[Guitian]

Hi,

I have quite the same problem, when trying to update a mySQL DB. The code stops while trying to set cursor an lock type...

[OLE DB Provider for ODBC Drivers (0x80040E21)
This ODBC driver doesn't handle requested properties]

Did someone succeed in updating such a DB with ASP forms ?

 

[qmi]

Thanks for the tips. I ended up using SQL commands to insert, update, delete etc. rows from the database. The &quot;Query successful&quot; messages I mentioned earlier disappeared after I removed some diagnostic code another programmer working on the project had inserted deep in the bowels of the code.

I tried dianal's
bool = rs.Supports(adUpdate)
code and it returns TRUE. I have also found that the rs.Update works perfectly on tables that have no char or text type columns.

Guitian - some combinations of lock type, cursor location and cursor type are not valid. Try -
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
for a simple Update type transaction.