Can't update with ADO - MySQL / 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 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.

 

[aseewald]

There are options in the MyODBC driver available to solve this problem. Open the ODBC control panel, select the DSN you want to use and activate those options :

- Don't optimize column width
- Pad CHAR to full length
- Allow BIG results
- Use manager cursors (experimental)
- Safety


Those will also help you solve some problems:
- Return matching rows

This one will improve the data tranferts
- Use compressed protocol

Those are the options i am using. Hope I've been helping you !

Alex.