MySQL ASP database driver
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 posted this at 20:20 — 25th July 2007.
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/
drew22299 posted this at 08:29 — 26th July 2007.
They have: 105 posts
Joined: Mar 2006
Ok going by that link you sent me, how does this look
(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 posted this at 14:16 — 26th July 2007.
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 posted this at 00:53 — 27th July 2007.
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.
a Padded Cell our articles site!
drew22299 posted this at 12:05 — 29th July 2007.
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
drew22299 posted this at 13:01 — 29th July 2007.
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 posted this at 14:52 — 1st August 2007.
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!
drew22299 posted this at 11:20 — 3rd August 2007.
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 posted this at 16:03 — 3rd August 2007.
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!
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!
drew22299 posted this at 18:08 — 3rd August 2007.
They have: 105 posts
Joined: Mar 2006
Phew! thanks for removing the details for the connection, I didn't realise I did that!
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.