Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    72

    Unanswered: Modifying ASP/MS Access connection script

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2006
    Posts
    72
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2006
    Posts
    72
    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    %>
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    Many thanks for that.

    I had an idea it was

    Code:
    If EOF = True)
    
    variables
    
    End If
    but I wasn't sure how to script it.

    It works - it gives me the log-in page now and no errors!!

    Cheers for all your patience and help.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •