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:
[code=asp]
query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POL ICY.ID AND POLICY.ID=STAF. ID AND Name = 'Joe'"
[/code]
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:

[code=asp]
query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POL ICY.ID AND POLICY.ID=STAF. ID AND Name = '"
query = query & request("nameOf Login") & "'"
[/code]

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:

[code=asp]
query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POL ICY.ID AND POLICY.ID=STAF. ID AND Name = '"
query = query & request("nameOf Login") & "'"
[/code]

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:
[code=asp]
AND D_Name = 'aspirin'
[/code]
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:
[code=asp]
AND D_Name LIKE %aspirin%
[/code]
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:
[code=asp]
response.write "<!-- strSQL: " & strSQL & " -->" & vbNewLine
[/code]

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

[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.......bu t 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.Synon yms 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.
[code=asp]
response.write "<!-- strSQL: " & strSQL & " -->" & vbNewLine[/code]
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.Synon yms 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.Synon yms 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(strSear ch, "'", "''") used for?????? and how is request.QuerySt ring("search") from Replace(strSear ch, "'", "''")

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