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 & "<br>" & 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

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

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.??

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

This article has been dead for over six months. Start a new discussion instead.