|
Subject:
|
OLEDB connection string variation
|
|
Posted By:
|
Edward Stephen
|
Post Date:
|
10/18/2004 1:23:05 PM
|
I am developing a small website and am having difficulty connecting to an Access database in an administration page where I want password protection. I have used the following code to access the page, which is done successfully. It provides True if found or False if RS.EOF is reached
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb")
'Response.Write "Source=" & server.mappath("/i/impact/data.mdb") & "<br>"
strSQL = "Select * FROM Account " &_
"WHERE Name='" & username & "' " &_
"AND Password='" & password & "'"
SET RS = Con.Execute( strSQL )
If NOT RS.EOF Then
do something like expose connections to other pages
End If
Set RS = nothing
Con.Close
Set Con = nothing
End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
however when it comes to editing the passwords used an error is generated saying that I require an updateable query?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If newpassword = confnewpassword Then
If (thisusername = username) and (password = pword) Then ' where username & pword is passed from the previous page
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb")
' Con.Open = OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb"))
'Response.Write "Source=" & server.mappath("/i/impact/data.mdb") & "<br>"
sqlString = "UPDATE Account " &_
"SET password='" & newpassword & "'" &_
" WHERE name='" & thisusername & "'"
'Response.Write "SqlString=" & sqlString & "<br>"
Con.Execute sqlString
Response.Write "Your changes have been successfully writen"
End If
Else%>
<p><b>
You have not confirmed your new password Correctly</b><p>
<%End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have approached my server provider who has given me a different connection string used on this server:
OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=path to database.mdb;");
This doesn't work on my computer, however I have added it to my .asp file and uploaded it onto the server, there I have received error messages which reduced the code to:
OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=path to database.mdb")
Now the error message received is very unfourthcoming and reads: HTTP 500 - Internal server error Internet Explorer
Has anyone seen this form of OLEDB connection string before and does anyone know how to make it work?
|
|
Reply By:
|
Imar
|
Reply Date:
|
10/18/2004 2:03:30 PM
|
Hi Edward,
Could this be a security error? That is, is your Web server allowed to write to the folder where the database resides?
To find out, try this:
First, change the settings for your browser so you see the real error message:
http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=264
Next, change your Connection string back to how it was as it looked pretty good to me. For more details on Access / Jet connections, look here:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet
Then run the page. If you get an error like 80004005, take a look here:
http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=263 and here: http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=290
If all this doesn't help, can you post the error you get?
HtH,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
Edward Stephen
|
Reply Date:
|
10/29/2004 7:25:47 AM
|
Hi Imar, I have adjusted my browser as suggested to remove 'Friendly Error' reports from the server and have now got the message below:
New Error message:
Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.
/12cooperdrive/revisePassword.asp, line 38
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have further adjusted my .asp file editing out the Execute command and reading instead the response.write sqlString which would have been processed, to me it looks OK so the problem is most likely to be as you suggest a server security issue.
I will take this up with my server provider for comment and report back the outcome.
revised code:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If not confnewpassword = " " Then
If newpassword = confnewpassword Then
If (thisusername = username) and (password = pword) Then
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb")
' Con.Open "aqmsDSN"
' Con.Open = OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb"))
'Response.Write "Source=" & server.mappath("/i/impact/data.mdb") & "<br>"
sqlString = "UPDATE Account " &_
"SET password='" & newpassword & "'" &_
" WHERE name='" & thisusername & "'"
Response.Write "SqlString=" & sqlString & "<br>"
' Con.Execute sqlString
Response.Write "Your changes have been successfully writen"
End If
Else%>
<p><b>
You have not confirmed your new password Correctly</b><p>
<%End If
End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Revised page revisepassword.asp (29th October)
returns: SqlString=UPDATE Account SET password='Richard' WHERE name='Edward Stephen'
Many thanks Imar - you are unique in so far that you are never put off and always come back with a solution.
Edward
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/30/2004 12:49:07 AM
|
PASSOWRD is a reserved keyword. So enclose that within []sUPDATE [Account] SET [password]='Richard' WHERE [name]='Edward Stephen' This should solve the problem.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
Edward Stephen
|
Reply Date:
|
11/2/2004 9:26:35 AM
|
Hi Imar & Vijay G, Your solution is correct. I have received a list of Access DB reserved words from my Server Provider giving the same information, I have added the table name to the field name ie. AccountName & AccountPassword which is achieving the same result. It is now working - many thanks to you both.
Cheers!
Edward.
|
