Modifying ASP/MS Access connection script

Quetzal · #1 (**permalink**) 10-27-08, 13:24

Hello

I have picked up the following which is part of an ASP log-in script. Users' name and password are stored in an MS Access database which has the following connection path:

Code:

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

src = Server.MapPath("db/login.mdb")
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
adoCon.Open  sConnStr
%>

My Web server, however, seems to recognise only the following type of path:

Code:

<%
Dim conn

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\login.mdb;"
%>

How would I 'translate' the first script so that I can use it on my server, given that some statements in the first script, for example adoCon, are used elsewhere in my ASP log-in script.

For instance, there is a file which informs users when they last logged-on and this file uses: adoCon.Execute (StrSql), while another file uses Set adoCon = Nothing.

Is it possible for me simply to use:

Code:

<%
Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\login.mdb;"
%>

Thanks for any advice.

Steve

gvee · #2 (**permalink**) 10-27-08, 14:06

Hey Steve,

There's nothing stopping you from having more than one connection object, or more than one command object against a single connection.

They key is that you're don't try and re-assign an object when it is in use.

I hope this answers your question, if not then I'm sorry I don't follow!

Quetzal · #3 (**permalink**) 10-27-08, 14:26

Hello georgev

Thanks for your post.

Sorry for not being clearer.

OK, my server likes scripts which connect to the MS Access databases it holds to look like this:

Code:

<%
Dim conn

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\login.mdb;"
%>

There is nothing here of related to Server.MapPath or Microsoft.Jet.OLEDB.4.0for instance.

This is a bit of a problem because the script I have to connect to a MS Access database does use these, as in:

Code:

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

src = Server.MapPath("db/login.mdb")
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
adoCon.Open  sConnStr
%>

What, therefore, I was hoping to achieve was some kind of modification of the second script, that is, the one which does use Server.MapPath and Microsoft.Jet.OLEDB.4.0 in order that I may connect using a script which my server likes.

I have just tried this, hoping it might do the trick:

Code:

<%
Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\login.mdb;


'src = Server.MapPath("db/login.mdb")
'sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
'adoCon.Open  sConnStr

%>

but I get the following error:

Expected statement

/NewLog/includes/dbconnection.inc, line 4

Hope I've made myself a little clearer.

Thanks again.

Steve

gvee · #4 (**permalink**) 10-27-08, 15:06

Aha, I see!

Well, the string variable that you're assigning on adoCon.Open is just the connection string - basically "where are you Mr (or Mrs) Database and how do I open you?". I'm simplifying things, but I hope it makes sense.

Try write the sConnStr to screen and you'll see what I mean - it's just a string!

Code:

<%

'You were missing an end quote
connection_string = "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\login.mdb;"

adoCon.Open connection_string

%>

I've practically confused myself, but hope it helps you in some way

Quetzal · #5 (**permalink**) 10-28-08, 15:29

Hello georgev

Thanks again for your post.

I think I may have resolved that connection issue now (with your kind help). At least I do not get any server errors from that particular file. I've used this:

Code:

<%
Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

sConnStr = "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\login.mdb;"

adoCon.Open sConnStr

%>

Unfortunately, this has generated another error (in another ASP file):

Code:

ADODB.Field error '800a0bcd' 

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. 

/NewLog/includes/settings.asp, line 5

You can see that the error is generated in the settings.asp file. This is what that file holds:

Code:

<%
Set rsGetEmail = Server.CreateObject("ADODB.Recordset")
strSQLAdminEmail = "SELECT [siteemail], [emailserver], [register], [lostpassword], [sitename] from tblAdmin"
rsGetEmail.Open strSQLAdminEmail, adoCon
vSiteEmail = rsGetEmail("siteemail")
vEmailServer = rsGetEmail("emailserver")
vRegister = rsGetEmail("register")
vLostPassword = rsGetEmail("lostpassword")
vSiteName = rsGetEmail("sitename")
Set rsGetEmail = Nothing
%>

I'm not too sure about the error generated, but it looks as if the server is expecting the database to contain some data.

Is there a way to overcome this, or should I just fill in '0' in every database table field just to comply with its wishes?!

Thanks.

Steve

gvee · #6 (**permalink**) 10-29-08, 04:39

What you need to do, is after you've opened the connection you need to test to see if any records exist in the recordset.

Code:

<%
Set rsGetEmail = Server.CreateObject("ADODB.Recordset")
strSQLAdminEmail = "SELECT [siteemail], [emailserver], [register], [lostpassword], [sitename] from tblAdmin"
rsGetEmail.Open strSQLAdminEmail, adoCon

If (rsGetEmail.EOF = True) Or (rsGetEmail.BOF = True) Then
    vSiteEmail = ""
    vEmailServer = ""
    vRegister = ""
    vLostPassword = ""
    vSiteName = ""
Else
    vSiteEmail = rsGetEmail("siteemail")
    vEmailServer = rsGetEmail("emailserver")
    vRegister = rsGetEmail("register")
    vLostPassword = rsGetEmail("lostpassword")
    vSiteName = rsGetEmail("sitename")
End If

Set rsGetEmail = Nothing
%>