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