drew22299 — Fri, 03 Aug 2007 18:08:50 +0000

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.

JeevesBond — Fri, 03 Aug 2007 16:03:56 +0000

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.

drew22299 — Fri, 03 Aug 2007 11:20:20 +0000

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 — Wed, 01 Aug 2007 14:52:23 +0000

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?

drew22299 — Sun, 29 Jul 2007 13:01:50 +0000

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

drew22299 — Sun, 29 Jul 2007 12:05:29 +0000

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

JeevesBond — Fri, 27 Jul 2007 00:53:26 +0000

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.

pr0gr4mm3r — Thu, 26 Jul 2007 14:16:14 +0000

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

drew22299 — Thu, 26 Jul 2007 08:29:49 +0000

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 — Wed, 25 Jul 2007 20:20:47 +0000

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/