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="nameOfLogin">
[/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="nameOfLogin">
[/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_Name = "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_Name = "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 .........because 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

[nehashri]

hey Jared

i was trying the codes til now now my code is working with

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

and few changes with ASP codes too...
now im getting information from 2 different tables. but there is still a thing that is it is showing one 1 match per query ie., if i type 'aceta' then only the first column comes. but i want all the matches to come. is it b'coz of LIKE?????
neha

[jhardman]

No "LIKE" should give all of them. The "%" should not be used with single quotes, though,

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

but I don't thnik that should cause the problem.

Jared

[nehashri]

No "LIKE" should give all of them. The "%" should not be used with single quotes, though,

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

but I don't thnik that should cause the problem.

Jared

hey jared
i tried using the SQl as above and didnot use % with single quotes.......but with that the following error is coming

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'Drug_Name.Drug_ID = Chemistry.Drug_ID AND Drug_Name.Synonyms LIKE %%'.
/MyWeb/mylogin/test3.asp, line 29

also with the sinle Quotes around % the first data ie., 1st row of data(from the database) is shown as default.........i mean when i excute the asp file there should be a text box and submit button...button along with these two 1st row od data is also coming........

can u think of what must be wrong here....
thanks
neha

[jhardman]

neha,

Ahh, yes. The variable "strSearch" is empty. Are you sure you passed it data? After you create the query I like to print it out for troubleshooting purposes just to make sure it looks correct.

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

This should print out (in an HTML comment)
[html]
<!-- strSQL: SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID AND Drug_Name.Synonyms LIKE %aceta% -->
[/HTML]
but I bet yours will print out like:
[html]
<!-- strSQL: SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID AND Drug_Name.Synonyms LIKE %% -->
[/HTML]
You just need to figure out why strSearch is empty.

Jared

[nehashri]

Jared

Thank you so much for your help thanks
well some how i figured out y my strsearch is empty the code for strsearch was written wrong now i am getting exactly what i needed..........my code is working perfectly well
thank you

but 2 more things
1. can u plz explain me what is - Replace(strSearch, "'", "''") used for?????? and how is request.QueryString("search") from Replace(strSearch, "'", "''")

2. is it possible to do the same search pattern in a drop down menu?????? i mean can i use drop down menu to search from two different tables......all the tutorials and other sites for html shows only select and show some msg when a menu from dropdown is selected.......

thanks again
regards
neha

[jhardman]

Thank you so much for your help thanks
well some how i figured out y my strsearch is empty the code for strsearch was written wrong now i am getting exactly what i needed..........my code is working perfectly well
thank you

You're welcome. I'm glad I could help

but 2 more things
1. can u plz explain me what is - Replace(strSearch, "'", "''") used for??????

Replace(string1, string2, string3) searches through string1 and replaces every instance of string2 with string3. In this case it is replacing every apostrophe (') with two apostrophes (''). This conversion is necessary for SQL (an apostrophe is a special character akin to a quote mark), without it the query would generate an error if the strSearch contained an apostrophe.

and how is request.QueryString("search") from Replace(strSearch, "'", "''")

You mean how are they different? Well, they're not much alike at all. the replace function is as I described it above. The request variables are the most standard way to pass data from one web page to another. to use both together (which may be a good idea) you need to do something like this:

strSearch = replace(request.querystring("search"), "'", "''")

2. is it possible to do the same search pattern in a drop down menu?????? i mean can i use drop down menu to search from two different tables......all the tutorials and other sites for html shows only select and show some msg when a menu from dropdown is selected.......

umm... I'm not sure I follow you. You want the user to only be able to search the db based on a <select> drop down box? Please clarify. What would you like the user to do and how should the asp page respond?

Jared

[nehashri]

hey Jared
hmm sorry my Q was not straight.
well along with the search v have been discussing i also want a search that has a drop down menu. when a user selects a menu from the list and click submit it should show the data of that perticular menu. here i dont want to search for a perticular drug but all the drugs having that info should appear.

lets say.....in a list (drop-down) there are oral, skin, intraveneous, etc (as menu in drop-down)......ven the user selects oral....then all the drugs name with 'oral' as there function should appear.......
if im not wrong ill hv to use the same sql query na.......
i really hope im some what clear now.............
neha

[jhardman]

hey Jared
hmm sorry my Q was not straight.
well along with the search v have been discussing i also want a search that has a drop down menu. when a user selects a menu from the list and click submit it should show the data of that perticular menu. here i dont want to search for a perticular drug but all the drugs having that info should appear.

lets say.....in a list (drop-down) there are oral, skin, intraveneous, etc (as menu in drop-down)......ven the user selects oral....then all the drugs name with 'oral' as there function should appear.......
if im not wrong ill hv to use the same sql query na.......
i really hope im some what clear now.............
neha

So the user could select a function from a drop down list in order to narrow a search? I would set up the form like this:
[html]
Function: <select name="function">
<option value="">(optional)</option>
<option value="oral">Oral</option>
<option value="Intravenous">Intravenous</option>
<option value="cutaneous">Cutaneous</option>
</select>
[/html]
then when setting up the query statement:

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug_ID = Chemistry.Drug_ID AND "
strSQL = strSQL & "Drug_Name.Synonyms LIKE  %" & strSearch & "%"
if request.form("function") <> "" then
   strSQL = strSQL & " AND Chemistry.function = '" & request.form("function") & "'"
end if
 

Remember that while you are in troubleshooting mode it is a good idea to print your query at this point:

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

Jared

[nehashri]

no no no no i think u still didnt understand......see here, there is no text box ok... i mean user dont have to type any thing.......now oral, Intravenous, Cutaneous are not fields but it is data of a field named administration

So the user could select a function from a drop down list in order to narrow a search? I would set up the form like this:
[html]
Function: <select name="function">
<option value="">(optional)</option>
<option value="oral">Oral</option>
<option value="Intravenous">Intravenous</option>
<option value="cutaneous">Cutaneous</option>
</select>
[/html]

ok to be more clear c i have 2 tables Drug_name n Chemistry.... ok....... Chemistry has a field named as administration (which has the data such as oral, Intravenous, Cutaneous etc.,) each drug has either one of them or 2 or 3 of them....... when a user selects say oral all those drugs which have this (oral) in their column should be displayed.......now the problem is that in the drop down this search should occur both Drug_Name and Chemistry as Drug name has to b displayed(Name of the drug is in tbl Drug_ID)

then when setting up the query statement:

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug_ID = Chemistry.Drug_ID AND "
strSQL = strSQL & "Drug_Name.Synonyms LIKE  %" & strSearch & "%"
if request.form("function") <> "" then
   strSQL = strSQL & " AND Chemistry.function = '" & request.form("function") & "'"
end if
 

here this code should not work as synonyms has not to b matched but menu from the drop down should match

as before i had to search from name to info now i need a search from info to drug.........Drug_ID been the common link b/w the 2 tables......
mi clear now Jared??????
thanks
neha

[jhardman]

mi clear now Jared??????
thanks
neha

No, I don't follow you at all. What should the user see and do, and what do you expect the script to do?

One thing that would make you more clear is if you used real words. For example "mi" is not a word as far as I know. Also there is no case in English where it is correct to use more than one question mark at the end of a sentence.

Jared

[jhardman]

neha,

I apologize for the brusque tone of my last post. Try this:

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug_ID = Chemistry.Drug_ID AND "
strSQL = strSQL & "Synonyms LIKE  %" & strSearch & "%"
if request.form("administration") <> "" then
   strSQL = strSQL & " AND administration LIKE %" & request.form("function") & "%"
end if

A couple of things you should notice:

1- This query opens all of the fields in these two tables (notice the asterisk after SELECT)

2- The two tables are related in the line "WHERE Drug_Name.Drug_ID = "...

3- After the relational statement, the next line adds a condition: only those records where the variable strSearch is found in the Synonyms field are put in the recordset

4- The query will execute correctly at this point, it doesn't need any additional info

5- IF the user selected a method of administration then a second condition is added to the query statement: " AND administration LIKE %"... This means that the query returns fewer records to the recordset, all entries include strSearch in the Synonyms field and request.form("administration") in the administration field.

6- Notice you do not need to specify chemistry.administration as long as the other table doesn't have a field of the same name. It is OK to refer to the field as just "administration".

I would like to draw your attention to the logic behind the second conditional statement. I set up my example so that If the user selects an administration method from a drop down list, this selection is added to the query. If the user should always select an administration method then the conditional statement should not be in an "if" statement.

If you mean that the user should either search by drug synonym or by administration method, then this can be done like this:

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug_ID = Chemistry.Drug_ID"
if request("search") <> "" then
   strSQL = strSQL & " AND Synonyms LIKE  %" & strSearch & "%"
end if
if request.form("administration") <> "" then
   strSQL = strSQL & " AND administration LIKE %" & request.form("function") & "%"
end if

This is essentially what I wrote last time, and I apologize if I wasn't clear. Remember that the query just needs to be built like any string variable, and after it is put together it is sent to the db. you can modify it in any way you want, add new conditions and statements, etc.

Jared

[jhardman]

neha,

Or are you saying you want this in an entirely new query?

strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug_ID = Chemistry.Drug_ID"
strSQL = strSQL & " AND administration LIKE %" & request.form("administration") & "%"

Searching for the value of a <select> drop down is no different from searching for the data entered by the user in a text box except that you know it will be in the correct format.

Jared

[nehashri]

hey Jared
thanks for the free English tutorials last time.
I thought you understand these small short forms so I was Writting that way. I will make sure to write correct english next time. SORRY

well I am not a computer person. I have to write or make this database for my marks and it is important. So please excuse me for my silly Questions.

in another example as to what i want to do is as follows

this is a fresh query that is it a different query altogether and is not related to synonyms at all user has to choose from admistration drop down.


for example: have a tables say contacts and info. contacts has the list of countries(country) and info has list of names(person) both tables are linked by ID.
if a drop down menu which has menus - india, burma, canada, italy etc., all i want is when india is selected all the people staying in india should be displayed. here in my database table list of country is the information or the fields of the table. that means from the contacts it should check through info

i have not tried what ever you have suggested...I will do it and let you know
thanks and regards
take care
neha

[nehashri]

hey Jared

i am trying the drop down for some other search.
but before that i need to do one thing.The search that i had done eariler the (synonyms one), now once the results are displayed in the browser i want to have an option for the user to save the result in text format or view result in text format. i have started a new thread for this but i still wanted to ask you hence i wrote in the thread hope you dont mind me repeating over here...

is it possible in asp
please let me know
thanks
neha