Another ASP/Access Problem

Neil P · January 28th, 2005, 11:47 AM

Hello All, I am new to working with Access DB's (believe it or not) and I have developed a application that for some reason will not run on my PC. I am trying to access my login page and get the follwing error:

Error Type:
Microsoft JET Database Engine (0x80004005)
Could not find file 'C:\WINDOWS\system32\site.mdb'.
/ASPLogin/dbconnection.inc, line 6

I have checked my db connection:

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

src = "site.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
adoCon.Open sConnStr

'Set an active connection to the Connection object using DSN
'adoCon.Open "DSN=ASPLOGIN"
%>

The Access DB is in the same directory as my ASP pages, but they will not come up.

Any suggestions?????????

Neil P · January 28th, 2005, 12:05 PM

Oops, I believe I fixed it by changing me code to:

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

src = "C:\Inetpub\wwwroot\ASPlogin\site.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
adoCon.Open(Server.Mappath("C:\Inetpub\wwwroot\ASP login\site.mdb"))

'Set an active connection to the Connection object using DSN
'adoCon.Open "DSN=ASPLOGIN"
%>

But now I am having another problem. I created a form page that writes to an Access DB. Everything seems to work fine with the form. I enter the information and press submit and it takes me to my "thank you" page.

However, the form is not writing to my MS Access DB. Any Suggestions???? I promise... this one I cannot answer myself!

Thanks!

Imar · January 28th, 2005, 12:48 PM

Hi Neil

Server.Mappath("C:\Inetpub\wwwroot\ASPlogin\site.m db")

doesn't make much sense. Server.MapPath is used to transform a virtual path into a physical path. Usually, it's used like this:

Server.MapPath("/Database/site.mdb")

This converts the virtual, root path Database/site.mdb to something like c:\Inetpub\wwwroot\Database/site.mdb. Useful when you don't know the exact physical location of the folder.

To answer your other question you'll need to post some code. Maybe you're not updating the database at all, maybe you're using On Error Resume next etc.

Please post the relevant code so we can take a look.

Cheers,

Imar

Neil P · January 28th, 2005, 01:08 PM

Thanks Imar! I am still cleaning up the code and made your recommended modification.

Below is my ASP code for the login page. It should log the user in by writing to the Access DB:

<%@ LANGUAGE="VBSCRIPT" %>
<%Response.Buffer=TRUE%>

<%
    'Create an ADO recordset object
    Set rsSiteName = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT sitename FROM tblAdmin"

    'Open the recordset with the SQL query
    rsSiteName.Open strSQL, adoCon

    Response.Write ("<title>")
    Response.Write (rsSiteName("sitename"))
    Response.Write ("</title>")

    Set rsSiteName = Nothing
%>

<script language="JavaScript">
    function setFocus()
    {
      Login.txtUserName.focus();
    }
  </script>

<body onload="setFocus();">

<br>
<br>
<%
If Request.querystring("mode") = "suspend" then
Response.Write ("<center>Username is currently suspended</center><br><br>")
End If

If Request.querystring("mode") = "once" then
Response.Write ("<center>Username is currently logged in</center><br><br>")
End If

If Request.querystring("mode") = "invalid" then
Response.Write ("<center>Invalid username or password. Please try again.</center><br><br>")
End If
%>
<form name="Login" method="post" action="login.asp">
  <table width="320" border="0" align="center" cellspacing="0" cellpadding="0" bgcolor="#CCCCCC">
    <tr>
      <td align="right" height="47" valign="bottom" width="94">User name: </td>
      <td height="47" valign="bottom" width="172">
        <input type="text" name="txtUserName">
      </td>
    </tr>
    <tr>
      <td align="right" width="94">Password: </td>
      <td width="172">
        <input type="password" name="txtUserPass">
      </td>
    </tr>
    <tr>
      <td align="right" height="44" width="94"> </td>
      <td height="44" width="172">
        <input type='image' src='images/forward_16.gif' name='Submit' value='Enter' alt='Enter'>
                &n bsp;
        <input type='image' src='images/cancel_16.gif' name='Reset' value='Reset' alt='Reset'>
      </td>
    </tr>

<%
    'Create an ADO recordset object
    Set rsLostPassword = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT lostpassword FROM tblAdmin"

    'Open the recordset with the SQL query
    rsLostPassword.Open strSQL, adoCon

    If rsLostPassword("lostpassword") = 1 then
        Response.Write ("<tr><td align='right' height='44' width='94'> </td><td>Get lost password  <a href=password.asp?mode=enter>here</a></td></tr>")
    End If

    Set rsLostPassword = Nothing

%>
<%

    'Create an ADO recordset object
    Set rsRegister = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT register FROM tblAdmin"

    'Open the recordset with the SQL query
    rsRegister.Open strSQL, adoCon

    If rsRegister("register") = 1 then
        Response.write ("<tr><td align='right' height='44' width='94'> </td><td>Register for an account  <a href=register.asp?mode=new>here</a></td></tr>")
    End If

    Set rsRegister = Nothing
    Set adoCon = Nothing

%>
  </table>
</form>

</body>

Thanks for taking a look!

Neil

Imar · January 28th, 2005, 06:34 PM

Can you explain where you expect the database to be updated (where in the code, that is)? What is the page supposed to do?

All I see is SELECT code, that gets records from the database....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Neil P · January 31st, 2005, 11:18 AM

Hello Imar, this page will query the DB to verify that the user is registered via a users table, which will distinguish them between a regular user and an admin. The query is supposed to write to the database and record the user as active. This in turn will redirect them to the appropiate home page (ie user or admin). The problem I am having is that it is not writing to the DB, so the next page just stays blank.

I ran some tests over the weekend and it seems to execute on my laptop, but not my PC. Both machines are using Windows XP, with IIS V5.1.

I am not sure what the problem is???? :(

Neil

Imar · January 31st, 2005, 02:42 PM

But it looks like another page is involved, no? The code you have posted does not update a database anywhere, so there must be another(include??) file that does the insert/update, right?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Neil P · January 31st, 2005, 03:06 PM

Yes, another page is involved. I should have included it earlier:

<%
'Dimension variables
Dim rsCheckUser 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Dim strUserName 'Holds the user name

'Initalise the strUserName variable
strUserName = Request.Form("txtUserName")

'Create an ADO recordset object
Set rsCheckUser = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT password, ID, username, admin, suspend FROM tblUsers WHERE username ='" & strUserName & "'"

'Open the recordset with the SQL query
rsCheckUser.Open strSQL, adoCon

strActiveUser = "Select * From Active_Users Where User_ID='" & strUserName & "'"
Set rs1 = adoCon.Execute (strActiveUser)

'If the recordset finds a record for the username entered then read in the password for the user
If NOT rsCheckUser.EOF Then

    If NOT rs1.EOF Then
        Response.Redirect ("default.asp?mode=once")
    'Read in the password for the user from the database
    Else If md5(Request.Form("txtUserPass")) = rsCheckUser("password") Then

         If rsCheckUser("suspend") = 0 Then
         'Write the HTML to display the current record in the recordset

            Session("userGood") = True
            Session("userID") = rsCheckUser("ID")
            Session("userName") = rsCheckUser("username")
            Session("userAdmin") = rsCheckUser("admin")
                        strUsername = rsCheckUser("username")
                        strLoginSql = "UPDATE tblusers SET last_login='" & now() & "' WHERE username ='" & strUsername & "'"
                        adoCon.execute (strLoginSql)

         Else
         Response.Redirect("default.asp?mode=suspend")
         End If

    'Reset server objects
    rsCheckUser.Close
    Set rsCheckUser = Nothing
    Set adoCon = Nothing

    'Redirect to the authorised user page and send the users name
        Response.Redirect"main.asp"

    End If
    End If
End If

'Reset server objects
rsCheckUser.Close
Set rsCheckUser = Nothing
Set adoCon = Nothing

'If the script is still running then the user must not be authorised
Session("userGood") = False

'Redirect to the unautorised user page
Response.Redirect"default.asp?mode=invalid"
%>

Imar · January 31st, 2005, 03:16 PM

Ah, I see.

Unfortunately, I cannot see a clear reason for an error, so I think you'll need to do some debugging.

What happens when you add some Response.Write and Response.End statements to the page you just posted? Does it get to the code you expect it to reach? Maybe there is a difference in the MD5 functionality? Maybe Now() is in american date format on one server and English / Swedish / Whatever on the other.

There are too many potential problem areas, so I guess you'll need to figure out where it breaks first....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Neil P · February 3rd, 2005, 09:52 AM

[b]Thanks for the feedback Imar. I am still working on it. All my other ASP pages work fine. It is only the ones that have database connections that are not executing.

I am thinking that it may have something to do with the ODBC driver. I am not familiar enough to say what it is specifically. I remember reading in one of the threads here, that the Microsoft Jet OLE DB or ODBC driver had to be purchased from Microsoft, or ASP pages had to be converted to ASP.NET.

Any other tips would be a great help.

Thanks![b]