Insert data into MySQL - Urgent help needed

[s2001]

hello all,

I really need help on this. can't figure out the problem.

I am developing an app which talks to SQL Server and MySQL database.

Here's the code which works well with SQL server but fails with MySQL:

Private sub Populate1()

Dim stri as string
dim oConn as new ADODB.Connection
Dim rs As New ADODB.Recordset

sTri = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=; DATABASE=NEWSAI; UID=;PWD=; PORT="
oConn.ConnectionString = sTri
oConn.Open

rs.open "Select * from test",oconn,2,3
rs.fields("Test23&quot.value="MyValue"
rs.update
Set rs = Nothing
Set oConn = Nothing

end sub


When i run the above code against MySQL i get the following error:

"Multiple step ole db operation generated errors. check each ole db status value, if available. No work done."

Please note that the field size for test23 is 255.

And also i don't get the above error when i run the same code against SQL Server

BUT, the following code WORKS with MySQL.

Private sub Populate2()

Dim stri as string
dim oConn as new ADODB.Connection
dim sQry as string

sTri = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=; DATABASE=NEWSAI; UID=;PWD=; PORT="

oConn.ConnectionString = sTri
oConn.Open
sQry = " Insert into test (test23) values ('MyValue')"
oConn.Execute sQry

Set oConn = Nothing

end sub

I need to use the proceedure listed in populate1, since the code has been written that way and we are adding support for MySQL.

Any support would be highly appreciated.

Thanks in Advance.

Murali Bala

 

[bascy]

Those multiple step errors fromt the ADO objects are on of the less informatives there are

We had those before and if i remember correctly they are generated because the ADO object sees differences in the resultset and the values in the database. i think we solved them by experimenting with the cursortype and the cursorlocation.

Bascy
Software developer at

http://www.cbis.nl

 

[s2001]

thanks for the response. i was able to solve the problem by changing the connection string the sub proceedure 'Populate1':


sTri = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=; DATABASE=NEWSAI; UID=;PWD=; PORT=; OPTION=3"

I am yet to find out what is OPTION=3. it is not listed in the mysql website.

If anybody know, please do let me know.




Murali Bala

 

[bascy]

Those options are listed on the MySQL site ...

http://www.mysql.com/doc/en/MyODBC_connect_parameters.html

Bascy
Software developer at

http://www.cbis.nl

 

[s2001]

thanks bascy



Murali Bala