Link to home
Start Free TrialLog in
Avatar of nbeaman
nbeaman

asked on

ADO RS.AddNew does not work with every service provider.

I am using MySQL (with ODBC) in my ASP pages.  Over the years I have noticed that the AddNew method does not work with some service providers (yet I can use SQL statments fine).  I'm testing with hostmysite.com now and I get the error:

ODBC driver does not support the requested properties.

/s.asp, line 15

I need to find a service provider where the AddNew method works, any suggestions on what component/pluggin I need to ask for in order for this to work.  I have tons of code using AddNew to add new records.

Here is my code:

<%
'======================[ OPEN CONNECTION TO DATABASE ]======================

set conn = Server.CreateObject("ADODB.Connection")
conn.open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=walnut.safesecureweb.com; PORT=3306; DATABASE=nbeaman; UID=nbeaman; PWD=test; OPTION=3;"

Set Rs = server.createobject("ADODB.Recordset")
set Rs.ActiveConnection = Conn

'===========================================================================

Rs.Open "test", conn, 1, 3

Rs.AddNew
Rs("test").value=50
Rs.Update
      
Rs.Close
Conn.CLose
set Rs=nothing
set Conn=nothing
%>

I've search the web over for the answer for a long time - be my hero and tell me what the secret is :)

Thank's in advance.
<%
'======================[ OPEN CONNECTION TO DATABASE ]======================
 
set conn = Server.CreateObject("ADODB.Connection")
conn.open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=walnut.safesecureweb.com; PORT=3306; DATABASE=nbeaman; UID=nbeaman; PWD=test; OPTION=3;" 
 
Set Rs = server.createobject("ADODB.Recordset")
set Rs.ActiveConnection = Conn
 
'===========================================================================
 
Rs.Open "test", conn, 1, 3
 
Rs.AddNew
Rs("test").value=50
Rs.Update
	
Rs.Close
Conn.CLose
set Rs=nothing
set Conn=nothing
%>

Open in new window

Avatar of kevp75
kevp75
Flag of United States of America image

Honestly...  I've found that the RecordSet object is not that reliable, or efficient.

This should work:
<%
'======================[ OPEN CONNECTION TO DATABASE ]======================
set conn = Server.CreateObject("ADODB.Connection")
    conn.open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=walnut.safesecureweb.com; PORT=3306; DATABASE=nbeaman; UID=nbeaman; PWD=test; OPTION=3;" 
'===========================================================================
        conn.Execute("INSERT INTO Table (test) VALUES (50)")
    conn.Close
set conn = nothing
%>

Open in new window

I guess the real answer is...

You won't be able to find one for MySQL.  My suggestion would be to either switch to MS SQL, and use the OLE provider, or change your code to what I posted above.
Avatar of nbeaman
nbeaman

ASKER

Thanks for the reply kevp75.  Your code did work.  However it doesn't really solve my problem, because I have tons of code that I have developed over the years that uses ADO record sets.

You wrote I "won't be able to find one for MySQL"? I'm not sure what you mean.  Are you saying that I can not use the AddNew method with MySQL?  Are record sets unreliable with {MySQL ODBC 3.51 Driver}?

Thanks again for your help.
the recordset object is unreliable with any provider.  I believe more so with MySQL because it is not a Micro$oft product.

Connection Object is faster, more reliable, and works well with any database.  Where as, Recordset object is more memory intensive, and does not play well with all databases.

I know it's probably a pain, and a ton of work, but in an instance like this, it is probably your best bet to update all the code.    Or...switch the db to MS SQL, may be a temp cure as well... however, still the best way would be to update the code...

Avatar of nbeaman

ASKER

Your are right Kevp75.  I see what you mean about RecordSets being buggy.  I found this article on the MySQL web site that says there is a bug when trying to open a table name with an ADODB RecordSet.

Rs.Open "TableName", Conn, 3, 1  'DOES NOT WORK

- but -

Rs.Open "Select * from TableName", Conn, 3, 1  'DOES WORK

Here the article for anyone having a similar problem  http://bugs.mysql.com/bug.php?id=28098

I'll take your first advice and switch to MS SQL.  Someday I will have to change my code also.

Thank you very much for your help.
ASKER CERTIFIED SOLUTION
Avatar of kevp75
kevp75
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good to see a solution to my exact same problem. I had a site up and running no problem for a few years on Fasthosts and all of a sudden it has broke. Thanks for the fix