Hi, being a newbie in ASP, i wonder if it is possible to compare 2 fields in 2 different tables?

for example, this is my first table named data, it goes like:

userID userName userPassword
1 user1 123456
2 user2 456789
3 user3 654321


this is my second table named data2, its pretty much like the 1st one, it goes like:

loginID name password
1 user1 123456
2 user4 aaaaaa
3 user5 232323

with the results in the first table, i want the first table to check with the second table if there are identical fields(in this case the first field of the two tables is the same). if there the results are found true, say sumthing like "there is a record!", if false then, "no such record!".

is this possible? any help would be very much appreciated, thx in advance.

Are you wanting to step through the first table and see if the value exists in the second table?

ex
data1
1 value22
2 value23
3 value24

data1(1) is value22

Is it in data2

step through data2
data2
1 value24
2 value23
3 value22

return TRUE!

yes! is it possible?

Yeah, you can do it.... but depending on exactly what you want to achieve with this process it might be better to do this directly at the database rather then getting the ASP to do it.

Exactly what are you trying to do?? What is the purpose of finding out where the matches are....

ok, currently i am doing my school project. the project is about an online booking system for users to book facilities from my school.

the purpose why i want to compare matching fields is for users to check whether they have bookings.

when they come to the school, they are required to scan their card and their user name and password is stored in a new database. the new database is then compared with the the other database(the database that stores the bookings made earlier).

if there are matches, then response.write "you have a booking!", else response.write "sorry, you do not have a booking."

what i am trying to do here is provide some sort of validation for the users and display the results on an ASP page.

so,... your flow of logic would go something like this...

collect card data
save card data to db1
search db2 for data where username matches card username...

yes??

yes!!!

so why don't you write two seperate queries.... the second query using the data that was gathreed/saved during the first query??

ermm.... how do you do that? do you mean using queries instead of tables in microsoft access 2000(yep, i am using access 2000)?

No, what I mean is learning how to use ASP to connect to the access database and then getting asp (probably via ADO/OLEDB) to execute a SQL query against the tables you have in your Access DB.

What you want to start looking up/reading about is how to execute Access database queries in ASP using ADO.

Once you have an idea of how this works then we will be able to help you with specific questions/problems...

ohh.. i see.. so you mean something like the following:

<%
' Set the path
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=C:\MyDatabases\database.mdb;DefaultDir=;UID=; PWD=;"

Dim Connection
Set Connection = Server.CreateObject("ADODB.Connection")

Connection.Open ConnectionString

' Create a RecordSet Object
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")

' Retrieve the records and get always the last record only
Dim sql
sql = "SELECT * FROM database WHERE LoginID = (SELECT MAX(LoginID) FROM database)", Connection

'Print out the contents of our recordset
Do While Not rs.EOF
Response.Write "<b>Name: " & rs("name")
Response.Write "<BR><b> Password : " & rs("password")
Response.Write "<HR>"

rs.MoveNext 'Move to the next record

loop

rs.Close
set rs = nothing

Connection.Close
Set Connection = nothing
%>

Yup, basically that is what you want... but is max(LoginID) going to work all the time???

I mean you are going to have users that come back more then once right?

Won't they use the same loginID each time??

also you want something like....

Set RS = Connection.Execute(sql)

heres something i tried earlier, but failed.

<%
' rs("name") is from the first table, pcrs("userName") is from the second.
if rs("name") = pcrs("userName") then
response.write "You have a booking!"
else
response.write "Sorry, you do not have a booking."
end if
%>

my lecturer then told me sumthin about compare strings.. it goes like..

<%
if StrComp (AA,BB,1)=0 then
response.write ("Yes")
else
response.write ("No")
end if
%>

can i directly swap AA with rs("name") and BB with pcrs("userName")?
or do i need to perform CStr to convert them to string first?

yupz, i have set rs = conn.execute(sql). i missed that line when posting. :P

From my brief understanding of the StrComp function yes.... I might be wrong though...

When you say it failed, what exactly was the problem? Did you get an error or did it simply never find a matching combination?