Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: comparing fields between two different tables.

    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.

  2. #2
    Join Date
    Mar 2004
    Posts
    6
    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!

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    yes! is it possible?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  5. #5
    Join Date
    Mar 2004
    Posts
    12
    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.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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??

  7. #7
    Join Date
    Mar 2004
    Posts
    12
    yes!!!

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    so why don't you write two seperate queries.... the second query using the data that was gathreed/saved during the first query??

  9. #9
    Join Date
    Mar 2004
    Posts
    12
    ermm.... how do you do that? do you mean using queries instead of tables in microsoft access 2000(yep, i am using access 2000)?

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  11. #11
    Join Date
    Mar 2004
    Posts
    12
    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
    %>

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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)

  13. #13
    Join Date
    Mar 2004
    Posts
    12
    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?

  14. #14
    Join Date
    Mar 2004
    Posts
    12
    yupz, i have set rs = conn.execute(sql). i missed that line when posting. :P

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •