Classic ASP & Access DB - FROM Clause Error

Question

it's the first time I post here and hopefully my english will be understood by everyone, I'm trying to create a site in VBScript (classic ASP) based on MS Access, with CSS and Some Ajax/Jquery.

I got to the point of managing logins & sessions, so created a pop-up form in the main page and submited datas to another asp page to validate the user and eventually open a session, just a problem with this: there is an error with the FROM Clause, and I actually tryed the same query on the DB and it does work!

Most of my code is in italian for presentation purpose: "nome" = name, "cognome" = surname, "DataNascita" = birthdate, "amministratore" = administrator.

As for the DB, names are different from table to table because I tryed to write different-names foreign key in order to exclude every option.

HTML FORM CODE:

<form Action="authenticate.asp" Method="Post">
                    <div class="row">
                        <div class="cells"> 
                            <span> Username </span> 
                        </div>
                        <div class="celld">
                            <Input Type="Text" Name="TxtUsername" Placeholder="Username"> 
                        </div>
                    </div>
                    <div class="row">
                        <div class="cells"> 
                            <span> Password </span>
                        </div>
                        <div class="celld">
                            <Input Type="Password" Name="TxtPassword" Placeholder="Password">  
                        </div>
                    </div>
                    <div class="row">
                        <div class="cellrowspan"> 
                            <Input Type="Submit" Value="Login"> 
                        </div>
                    </div>
                </form>

CAN'T POST IMAGES SO THAT'S THE STRUCTURE OF MY DB

Table (Account) - AccountID (Pk) [Auto.Inc], Username [String], Password [String]

Table (User) - UserID (Pk) [Auto.Inc], ProfiloID (Fk. of Account) [Long Integer], Nome [String], Cognome [String], Data_Nascita [Data], E_Mail [String], LivelloID (Fk. of Livello) [Long Integer].

Table (Livello) - PrivilegioID (Pk) [Auto.Inc], Categoria [String]

FINALLY THE authenticate.asp PAGE IN RESPONSE TO THE FORM

<%
Dim Username, Password, Nome, Cognome, DataNascita, Email, IDLevel, StrConn, Conn, Rs
    Username = Trim(Request.Form("TxtUsername"))
    Password = Trim(Request.Form("TxtPassword"))

    IF Username <> "" AND Password <> "" THEN
        StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("Users.accdb")

        Set Conn = Server.CreateObject("ADODB.Connection")
        Set Rs = Server.CreateObject("ADODB.Recordset")

        Conn.Open StrConn
        Set Rs = Conn.Execute ("SELECT User.Nome, User.Cognome, User.Data_Nascita, User.E_Mail, User.LivelloID FROM (Account INNER JOIN User ON Account.AccountID=User.ProfiloID) INNER JOIN Livello ON User.LivelloID=Livello.PrivilegioID WHERE Account.Username='" & Username & "' AND Account.Password='" & Password & "'")
            Nome = Rs.Fields("Nome")
            Cognome = Rs.Fields("Cognome")
            DataNascita = Rs.Fields("Data_Nascita")
            Email = Rs.Fields("E_Mail")
            IDLevel = Rs.Fields("LivelloID")

        Rs.Close
        Conn.Close
        Set Rs = Nothing
        Set Conn = Nothing

        Session("NomeUtente") = Nome
        Session("CognomeUtente") = Cognome
        Session("DataNascita") = DataNascita
        Session("Email") = Email
        Session("Authenticated") = 1
        IF IDLevel = 1 THEN 
            Session("Amministratore") = "True"
        ELSE 
            Session("Amministratore") = "False"
        END IF
    END IF

Response.Redirect ("homepage.asp")
%>

What error are you getting? if the SQL work in Access then it should work in your VBScript so it looks as though the error is there. If you can give us the error itself that would help. — Nick Green, May 30 '15 at 14:18
Do you have the ACE OLEDB driver installed? http://stackoverflow.com/questions/14401729/difference-between-microsoft-jet-oledb-and-microsoft-ace-oledb — John, May 30 '15 at 14:23

score 1 · Accepted Answer · answered May 30 '15 at 14:51

You have used a lot of reserved words of access to the field names. If you make a query inside access the system run at the same, but if you pass the query using asp will be in error. You should either change the names of the tables fields.

Es.: the table "user" became "utenti", etc...

Classic ASP & Access DB - FROM Clause Error

1 Answers1