DaniWeb Forum Index > Web Development > ASP

how to retrive data from different Ms Access tables using ASP

Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2007

Posts: 21

Reputation: nehashri is an unknown quantity at this point

Solved Threads: 0

nehashri

Offline

Newbie Poster

how to retrive data from different Ms Access tables using ASP

May 25th, 2007

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 :-

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
<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>
<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

•

Join Date: May 2007

Posts: 6

Reputation: Sitestepper is an unknown quantity at this point

Solved Threads: 0

Sitestepper Sitestepper is offline

Offline

Newbie Poster

Re: how to retrive data from different Ms Access tables using ASP

May 30th, 2007

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

•

Join Date: Jan 2007

Posts: 21

Reputation: nehashri is an unknown quantity at this point

Solved Threads: 0

nehashri

Offline

Newbie Poster

Re: how to retrive data from different Ms Access tables using ASP

Jun 7th, 2007

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

•

Join Date: Feb 2007

Posts: 53

Reputation: Hellodear is an unknown quantity at this point

Solved Threads: 0

Hellodear

Offline

Junior Poster in Training

Re: how to retrive data from different Ms Access tables using ASP

Jun 9th, 2007

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

anish_pnlr@yahoo.co.in