how to retrive data from different Ms Access tables using ASP

hi
i have a problem if any one can help me please. i have 2 tables in Access database- Drug_Name and Chemistry. i have to retrive the from DB to the ASP. the Drug_Name has Drug_ID, D_name, Synonyms as the fields. once the user write a name it should check to the Drug_Name with the Synonyms fields and then with help of Drug_ID it should retrive D_name from Drug_Name table and other fields from Chemistry table.....
the code i used is :-

<HTML>
<BODY>
<%
Actionvar=Request.QueryString("actionvar")
strURL = Request.ServerVariables("URL")
strSearch = Request.form("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_Namerequest.form("search")Search, "'", "''") & "'"
response.write sql & "" & vbNewLine
 
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("D_Name").Value %></td>
  <td><%= rstSearch.Fields("S").Value %></td>
  </tr>
  <%
  rstSearch.MoveNext
 Loop
 
 %>
 </table>
 <%
 
 rstSearch.Close
 conn.Close
 Set conn = nothing
 
End If
%>
</BODY>
</HTML>

here only the html codes works but ASP codes dont work
i believe that SQL query is correct must be retrival ASP code is not correct
plz help
thanks
neha

nehashri

Newbie Poster

21 posts since Jan 2007

Reputation Points: 10

Solved Threads: 0

5 Years Ago

0

Can you show us the result of the response.write sql statement

Sitestepper

Newbie Poster

6 posts since May 2007

Reputation Points: 10

Solved Threads: 0

4 Years Ago

0

HEy this code is now working asp codes had some problem thanks for reply
neha

nehashri

Newbie Poster

21 posts since Jan 2007

Reputation Points: 10

Solved Threads: 0

4 Years Ago

0

Hi neha,
Can you show the right code?

retrieving data from two databases... i'm also in a way to display like that.... can you help me out in this regards.??

Hellodear

Junior Poster in Training

53 posts since Feb 2007

Reputation Points: 11

Solved Threads: 0

4 Years Ago

0

hi,
your code is right but the query is not giving any result check your query in access.

MohitB

Newbie Poster

6 posts since May 2007

Reputation Points: 10