Logon Script

[Bob L]

I'm trying to incorporate the .asp code from the MS Knowledge base article
Q321439 into a website and am having trouble getting info from the logon
database. The database is created in Access 97 and has three columns: User
ID ("UID"), Password ("PWD"), and a Client Number ("CLIENTNO"). All of
these values are stored as text in table "tblUsers".

The select string from Q321439 that is used for the database query works
fine as you would expect since it's directly copied from the article. For
example, for user "Ken" with password "4321" the vbscript generates the
following string: SELECT * FROM tblUsers WHERE (UID='Ken' AND PWD='4321');

Once I have the correct UID and PWD combination entered, I then want to get
the CLIENTNO from the UID. But I'm unable to get the CLIENTNO when I do a
query with this string generated from vbscript: SELECT CLIENTNO FROM
tblUsers WHERE (UID='Ken'); Is there something wrong with this string?

 

[Bob L]

Sorry. I didn't mean to post this a reply.

 

[Bob L]

Cancel that last message. I still need help on the script.

 

[Douglas J Steele]

You could modify ComparePassword to also give you the client number.

Function ComparePassword(UID,PWD, ClientNo)
' Define your variables.
Dim strSQL, objCN, objRS
' Set up your SQL string.
strSQL = "SELECT * FROM " & USERS_TABLE & _
" WHERE (UID='" & ParseText(UID) & _
"' AND PWD='" & ParseText(PWD) & "');"
' Create a database connection object.
Set objCN = Server.CreateObject("ADODB.Connection")
' Open the database connection object.
objCN.Open "driver={Microsoft Access Driver (*.mdb)}; dbq=" & _
Server.MapPath(MDB_URL) & "; uid=admin; pwd="
' Run the database query.
Set objRS = objCN.Execute(strSQL)
' Set the status to true/false for the database lookup.
If objRS.EOF = False Then
ComparePassword = True
ClientNo = objRS.Fields("CLIENTNO")
Else
ComparePassword = False
ClientNo = 0
End If
' Close your database objects.
Set objRS = Nothing
Set objCN = Nothing
End Function

 

[Bob L]

Doug:

I make the changes you suggested and still it doesn't work as the string
"CLIENTNO" has a zero length. I'm pretty sure that the database has the
information in the correct format so I'd guess that the problem is somewhere
in the script. Any more suggestions?

Bob

 

[Douglas J. Steele]

Are you sure that the table is properly populated?

 

[Bob L]

I think it is. When I open it in Access 97, all three columns are there as
text. Also, as a test, I used the Database Results Wizard to display the
database on another page and all three columns displayed properly there too.
But, I'm a novice when it comes to databases, .asp, and vscript though I do
know programming and have developed "c" programs in the past.

 

[Douglas J Steele]

So how are you trying to use the modified function I gave you?

The function itself returns True or False.

If it returns True, then ClientNo should be returned as a parameter:

In other words, you should have something like:

If UCase(Request.ServerVariables("HTTP_METHOD")) = "POST" Then
' If so, check the username/password that was entered.
If ComparePassword(Request("UID"),Request("PWD"), ClientID) Then
' If comparison was good, store the user name and ClientID...
Session("UID") = Request("UID")
Session("ClientID") = ClientID
' ...and redirect back to the original page.
Response.Redirect Session("REFERRER")
End If
End If

 

[Bob L]

I didn't realize that. I thought it returned the actual ClientNo from the
database. How do get the actual database value into ClientNo?

 

[Douglas J Steele]

Look at my example, Bob. See the line of code "Session("ClientID") =
ClientID"? That's how you retrieve the value once you've called the
function.