PDA

View Full Version : Logging procedure using MS Access database


ABC123

PieterSijl
10-27-2004, 03:02 AM
I am working on a web application with a Login procedure build in it. Something like the example from the Web_Applications_Demo. I am using a MS Access database with a User-Password table.
I’ve tried several modifications on the example but nothing works. Can someone help me?

Selwyn Rabins
10-27-2004, 06:29 AM
New Page 2




If you look at the code in the Validate event for
the LoginDialog component, you will see the following event handler

if user_name = "" .or.
password = "" then
currentform.has_error = .t.
currentform.error_message = "User name or password cannot be
blank."
else
dim correct_password as c
correct_password =
lookup("[PathAlias.ADB_Path]\user_names","user_name=" +
quote(user_name),"password")
if correct_password "" password then
currentform.has_error = .t.
currentform.error_message = "Invalid
user name or password."
end if
end if

The key line here that looks up the correct password in the user_names table (a
.dbf file) is the one in green.

If you want to use an Access table, then you must
replace this line in green with code that is similar to what I show below.
Let's assume that you have an Access MDB file
called c:\data\users_names.MDB.
Assume that this database contains a table called
"UserID_Password", and that the table contains fields "UserID" and "Password".
The dialog component has a field called 'user_name'
that is entered by the user.
You will need to use ADO to get the data out of
the User_names.MDB file.
Here is the code you can use:
dim cs as c
cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\user_names.MDB;Persist
Security Info=False"
'DIM an object that is an 'ADO Connection'
dim cn as ole::adodb.connection
'DIM an object that is an 'ADO Recordset'
dim rs as ole::adodb.recordset
'Specify the SQL query to get the password for the specified User_name
dim sqltxt as c
sqltxt = "select Password from UserID_Password where UserID = '" + user_name +
"'"
'Open the connection to the Access Database
cn.open(cs)
'Execute the query, creating a recordset which holds the results
rs.open(sqltxt,cn)
if rs.eof = .t. then
correct_password = ""
else
correct_password = rs.fields.item(0).value
'note: you could also have said: correct_password =
rs.fields.item("password").value
end if
'Close the connection to the Database
cn.close()

The code is easier to read if I remove comments, so here is the same code with
the comments removed:
dim cs as c
cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\user_names.MDB;Persist
Security Info=False"
dim cn as ole::adodb.connection
dim rs as ole::adodb.recordset
dim sqltxt as c
sqltxt = "select Password from UserID_Password where UserID = '" + user_name +
"'"
cn.open(cs)
rs.open(sqltxt,cn)
if rs.eof = .t. then
correct_password = ""
else
correct_password = rs.fields.item(0).value
end if
'Close the connection to the Database
cn.close()

Selwyn Rabins
10-27-2004, 06:30 AM
when copying the above code, be careful with line breaks. i see that the message above shows some line breaks that should not be there.

PieterSijl
10-27-2004, 07:02 AM
It works!

Thanks,

Pieter