how to retrive data from different Ms Access tables using ASP

[nehashri]

hi

i am designing a database using Ms Access and ASP. i have 3 tables in access namely 'PERSONAL', other as 'POLICY' and 3rd one is named as 'STAFF'. in the contact table i have ID, Name, Children as fields. Also in policy table the firlds are:- ID, date_of_policy, no_policy, amount_paid, amount_balance and similarly the 3rd ie., staff has few fields. all three are linked my a common field which is ID

all i want is when the user searches for a perticular name (from frontend) it should search through table- 'PERSONAL' (fields- Name and Children). once it get the perticular name with the help of the ID it should retrive the datafrom both 'POLICY' and 'STAFF' of that perticular Name.

i do know how to search and retrive data from the same table but got a bit confused on how to retrive data from different tables using the 'PERSONAL' table just for reference and linking to the other 2 tables

plz help
thanking
neha

[jhardman]

Neha,

You don't need to open the tables separately, try:

query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POLICY.ID AND POLICY.ID=STAF.ID AND Name = 'Joe'"
 

This pulls up all of the data for the same ID as if it was in a single table.

Let me know if this helps.

Jared

[nehashri]

thanks Jared

this helped me but my problem is that i cannt specify name = Joe.....
it depends on the user which name they select on the front end [ASP] the information of that perticular name (typed by user) should be displayed (& there can be thousands of name).
plz help
neha

[jhardman]

Neha,

Right, I just meant for this as an example. The query string is just a string variable arranged in such a way that a db driver can understand it. You assemble the string the same way you assemble any string:

query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POLICY.ID AND POLICY.ID=STAF.ID AND Name = '"
query = query & request("nameOfLogin") & "'"
 

nameOFLogin is whatever you called the login name on the form:
[html]<input type="text" name="nameOfLog in">
[/html]

Please let me know if this makes sense.

Jared

[nehashri]

Neha,

Right, I just meant for this as an example. The query string is just a string variable arranged in such a way that a db driver can understand it. You assemble the string the same way you assemble any string:

query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POLICY.ID AND POLICY.ID=STAF.ID AND Name = '"
query = query & request("nameOfLogin") & "'"
 

nameOFLogin is whatever you called the login name on the form:
[html]<input type="text" name="nameOfLog in">
[/html]

Please let me know if this makes sense.

Jared

hi Jared i have tried your query with changes in name but there is some error comming which i am not able to makeout
the following is the sql i am using:-

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = '"
strSQL = strSQL & request("search") &"' "

when i use this code the result is not shown at all

so i tried doing like this:-

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = "& request("search") &" "     

and the error that is showing is
Expected end of statement
/MyWeb/mylogin/testquery.asp, line 24, column 112
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ ID = Chemistry.Drug_ ID And
Drug_Name.D_Nam e = "strSearch" "
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------
---------------------^

i dont understand what is wrong is these codes
neha

[jhardman]

hi Jared i have tried your query with changes in name but there is some error comming which i am not able to makeout
the following is the sql i am using:-

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = '"
strSQL = strSQL & request("search") &"' "

when i use this code the result is not shown at all

so i tried doing like this:-

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = "& request("search") &" "     

and the error that is showing is
Expected end of statement
/MyWeb/mylogin/testquery.asp, line 24, column 112
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ ID = Chemistry.Drug_ ID And
Drug_Name.D_Nam e = "strSearch" "
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------
---------------------^

i dont understand what is wrong is these codes
neha

The text field should definitely be in single quotes so that the query looks like this:

AND D_Name = 'aspirin'
 

Possibly this isn't bringing up any hits because there are no exact matches, I think this might be case sensitive (an exact match is necessary). Instead you could try:

AND D_Name LIKE %aspirin%
 

This should not be case sensitive, and doesn't produce only exact matches.
After I write the query, to make sure it looks good, I often write the query to an HTML comment:

response.write "<!-- strSQL: " & strSQL & " -->" & vbNewLine
 

This lets me verify that the database is seeing what it should

Let me know if this helps.

Jared

[nehashri]

Jared
thanks for the help
i am very much sure that this is not case sensitive... to retrive data from the same table i have used almost the same coding and it worked perfectly alright....
this is the whole code i am using

<HTML>
<BODY>
<%
Actionvar=Request.QueryString("actionvar")
strURL = Request.ServerVariables("URL")
strSearch = Request.querystring("search")
%>
<p>Search our sample db by first or last name.  (% returns all)</p>
                <form action="<%= strURL %>" method="post">
    <input type = "text" name="search" size = "30" value="<%= strSearch %>" />
    <input type="submit" />
    </form>
    <%   
    If strSearch <> "" Then
                Set conn = server.createobject("adodb.connection")
    DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
                DSNtemp=dsntemp & "DBQ=" & server.mappath("Databases/nprcDrugsDB.mdb")
    conn.Open DSNtemp
 
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = '"
strSQL = strSQL & request.form("search") &"' "
 
Set rstSearch = conn.execute(strSQL)
    %>
    <table border="1">
        <tr>
            <th>Name</th>
            <th>Salts</th>
        </tr>
        <%
            Do While Not rstSearch.EOF
        %>
                   <tr>
            <td><%= rstSearch.Fields("Drug_Name.D_Name").Value %></td>
            <td><%= rstSearch.Fields("Chemistry.Salts").Value %></td>
        </tr>
        <%
        rstSearch.MoveNext
 
            Loop
    %>
    </table>
    <%
    rstSearch.Close
    conn.Close
    Set conn = nothing
    Set strSQL = nothing
End If
%>
</BODY>
</HTML>

i guess i am not declaring some thing .........becaus e when i execute this code only the HTML codes work and not the ASP Codes...... do i have to declare strSQL else where also or some thing is wrong for retrival ASP code of mine
neha

[nehashri]

is my ASP code to retrive data after the SQL query is not correct????? how do i retrive my queried data in a tabular form then?????
neha

[jhardman]

Neha,

It definitely looks OK to me. I don't see any problems. Try using "LIKE" instead of "=" though and put your drug name in "%" instead of "'"

Jared

[nehashri]

hi Jared,
well i have changed my SQL codes as u told

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID AND Drug_Name.D_name LIKE '%" & strSearch & "%' " 

but the result is same only empty tables comes and no information.... .what im thinking is that ASP codes is not working over here or SQL query is not taken in to account........ well im sure of connectivity is correct as its working fine when only one table is involved....... .
what do i do i need to complete this by this weekend and its giving me so much problem...
any waz thank so much for the help u provided
thanks again plz let me know if you get an idea of whats wrong over here.......
thanks again
neha