MySQL ASP database driver

They have: 105 posts

Joined: Mar 2006

How would I change the following to use a mysql database? Would I just change the driver?

<%Dim Apples
Set Apples = Server.CreateObject("ADODB.Connection")

ConnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; "
ConnStr = ConnStr & "DBQ=" & Server.MapPath("db\users.mdb")
Apples.Open(ConnStr)

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

Your going to have to change more than that as you don't open a .mdb file for MySQL. This should help you out -> http://www.aspdev.org/articles/asp-mysql-connect/

They have: 105 posts

Joined: Mar 2006

Ok going by that link you sent me, how does this look Wink (I will try this when I get home, at work at the moment)

This is the original code with mdb connection: (MS ACCESS Connection)

<?php
Dim Apples
Set Apples
= Server.CreateObject("ADODB.Connection")

ConnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; "
ConnStr = ConnStr & "DBQ=" & Server.MapPath("db\users.mdb")
Apples.Open(ConnStr)

SQLtemp = "SELECT * FROM password WHERE nr = " & Request.Querystring("nr") & " "

Set rs = Apples.Execute(SQLtemp)

SQL = "UPDATE [password] SET [active] = '" & "yes" & "' WHERE nr = " & Request.Querystring("nr") & ""
Apples.Execute(sql)

Set ConnStr = Nothing
rs
.Close
Apples
.Close
set ConnStr
= Nothing
Response
.redirect ("login.asp?msg=Thank+you.+Your+account+was+authorised+ok.")
?>

And this is what I have changed it to: (MySQL Connection)

<?php
Dim Apples
Set Apples
= Server.CreateObject("ADODB.Connection")

ConnStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=database; UID=username; PASSWORD=passwordl; OPTION=3";

ConnStr = ConnStr & "DBQ=" & Server.MapPath("what goes here?")
Apples.Open(ConnStr)

SQLtemp = "SELECT * FROM password WHERE nr = " & Request.Querystring("nr") & " "

Set rs = Apples.Execute(SQLtemp)

SQL = "UPDATE [password] SET [active] = '" & "yes" & "' WHERE nr = " & Request.Querystring("nr") & ""
Apples.Execute(sql)

set ConnStr = Nothing
rs
.Close
Apples
.Close
set ConnStr
= Nothing
Response
.redirect ("login.asp?msg=Thank+you.+Your+account+was+authorised+ok.")
?>

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

I think you would have better luck if you modify the code from that link rather than modifying the code you have.

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

Drop that bit, that's the path to the database file which is irrelevant when connecting to a MySQL database (the connection is performed over the network instead). So your code should look like:

<%Dim Apples
Set Apples = Server.CreateObject("ADODB.Connection")

ConnStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=database; UID=username; PASSWORD=passwordl; OPTION=3";
Apples.Open(ConnStr)

SQLtemp = "SELECT * FROM password WHERE nr = " & Request.Querystring("nr") & " "

Set rs = Apples.Execute(SQLtemp)

SQL = "UPDATE [password] SET [active] = '" & "yes" & "' WHERE nr = " & Request.Querystring("nr") & ""
Apples.Execute(sql)

set ConnStr = Nothing
rs.Close
Apples.Close
set ConnStr = Nothing
Response.redirect ("login.asp?msg=Thank+you.+Your+account+was+authorised+ok.")
%>
'
I found a list of example connection strings to help you along. Smiling

a Padded Cell our articles site!

They have: 105 posts

Joined: Mar 2006

It works now, thanks. The only thing I am having trouble with now is the join.asp script, it uses recordsobjset and I changed the code in the same way for all the other pages (which work) but it doesn't seem to insert the data

how would I change the following: I have tried many combinations!

<?php
       
                        Dim Conn
                     change to the same
as aboveConn = "DRIVER={Microsoft Access Driver (*.mdb)};"
                    
get rid of thisConn = Conn & "DBQ=" & Server.MapPath("db\users.mdb")
                    
?>

<%

Dim I
Dim iRecordAdded
Dim iRecordCount

Dim objRecordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")

Is it someting to with this stuff?

objRecordset.Open "password", Conn, adOpenKeyset, adLockPessimistic, adCmdTable

They have: 105 posts

Joined: Mar 2006

I have changed the above to the following but it doesn't work, can you see why? Do you open the database connection in a different way when using mysql instead of ACCESS when using objRS? I have been trying to work this out for a few hours and still can't get it to work!

<?php
Dim Apples

                     Set Apples
= Server.CreateObject("ADODB.Connection")
                    
                    
Conn = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=database; UID=user;PASSWORD=password; OPTION=3"
           
                    
Apples.Open(Conn)

                    
?>

<%

Dim I
Dim iRecordAdded
Dim iRecordCount

Dim objRecordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")

objRecordset.Open "password", Conn, adOpenKeyset, adLockPessimistic, adCmdTable

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

Did you fix this? So you've changed all the other connections to use MySQL and they work fine? Are all the other tables opened read-only? At what point does the error occur?

Seems like it could be a permissions problem, have you given access to the user defined in the connection string and does it have write (as well as read) access?

a Padded Cell our articles site!

They have: 105 posts

Joined: Mar 2006

Yes I managed to get it working for everything else on my website except for the page where users register their details!

I literally changed the mysql connection from an ACCESS connection to an sql connection. It works fine when it is recording the data to an ACCESS database. I have highlighted in red the part of the code which I think might refer to permissions (in the sql connection code snippet) I don't understand why you would need different permissions or keywords when using a different connection though.

Here is the ACCESS connection

<?php
       
                     Dim Conn
                     Conn
= "DRIVER={Microsoft Access Driver (*.mdb)};"
                    
Conn = Conn & "DBQ=" & Server.MapPath("db\users.mdb")
                    
?>

<?php
                     Dim I
                     Dim iRecordAdded
                     Dim iRecordCount
                     Dim objRecordset
                     Set objRecordset
= Server.CreateObject("ADODB.Recordset")

<
strong>SQL connection</strong>

                   [=
Blue]  <%Dim Apples

                     Set Apples
= Server.CreateObject("ADODB.Connection")
                    
                    
Conn = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=<snip>; DATABASE=<snip>; UID=<snip>;PASSWORD=<snip>; OPTION=3"
                    
Apples.Open(Conn)
                    
?>

<%
Dim I
Dim iRecordAdded
Dim iRecordCount
Dim objRecordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objRecordset.Open "password", Conn, adOpenKeyset, adLockPessimistic, adCmdTable

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

Well thanks for posting your database username/password. I was able to connect and confirm that it's not a permissions issue. Luckily am not a nasty cracker type who enjoys deleting databases! Smiling What I was thinking is that this is the only time you're trying to insert values into the database. If all other connections are just reading the database, but this is an insert, I guessed that the user doing the connecting didn't have the permissions to insert into the table... Seems that isn't a problem though.

By the way: all I did in your db was change line 2 of your profile to read 'testing', hope you don't mind.

So what is the exact error message that it generates? Secondly, are all your objRecordset.Open commands the same as:
objRecordset.Open "password", Conn, adOpenKeyset, adLockPessimistic, adCmdTable' (copied from your post above)

Next thing to try: remove OPTION=3 from your connection string. Don't think you should need that.

a Padded Cell our articles site!

They have: 105 posts

Joined: Mar 2006

Phew! thanks for removing the details for the connection, I didn't realise I did that! Laughing out loud I think I will change it so the register details form is processed by a php script and make it secure.

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.