how to retrive data from different Ms Access tables using ASP

[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(strSear ch, "'", "''") 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.QuerySt ring("search") from Replace(strSear ch, "'", "''")

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="">(optio nal)</option>
<option value="oral">Or al</option>
<option value="Intraven ous">Intravenou s</option>
<option value="cutaneou s">Cutaneous </option>
</select>
[/html]
then when setting up the query statement:
[code=asp]
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug _ID = Chemistry.Drug_ ID AND "
strSQL = strSQL & "Drug_Name.Syno nyms LIKE %" & strSearch & "%"
if request.form("f unction") <> "" then
strSQL = strSQL & " AND Chemistry.funct ion = '" & request.form("f unction") & "'"
end if
[/code]
Remember that while you are in troubleshooting mode it is a good idea to print your query at this point:
[code=asp]
response.write "<!-- strSQL: " & strSQL & " -->" & vbNewLine
[/code]
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="">(optio nal)</option>
<option value="oral">Or al</option>
<option value="Intraven ous">Intravenou s</option>
<option value="cutaneou s">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:
[code=asp]
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE "
strSQL = strSQL & "Drug_Name.Drug _ID = Chemistry.Drug_ ID AND "
strSQL = strSQL & "Drug_Name.Syno nyms LIKE %" & strSearch & "%"
if request.form("f unction") <> "" then
strSQL = strSQL & " AND Chemistry.funct ion = '" & request.form("f unction") & "'"
end if
[/code]

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.........Dr ug_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("a dministration") in the administration field.

6- Notice you do not need to specify chemistry.admin istration 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(count ry) 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