Ad:

ASP Discussion Thread
Unsolved
Views: 5101

May 25th, 2007

how to retrive data from different Ms Access tables using ASP

Expand Post »

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

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

Similar Threads

retrive data from different tables of Ms access in ASP
retrive data from different tables of Ms access in ASP
Using Visual Fox Pro data tables in asp.net application in ASP.NET

nehashri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

21 posts
since Jan 2007

Permalink

May 30th, 2007

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

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

Sitestepper

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since May 2007

Permalink

Jun 7th, 2007

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

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

nehashri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

21 posts
since Jan 2007

Permalink

Jun 9th, 2007

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

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

Reputation Points: 11

Solved Threads: 0

Junior Poster in Training

Offline

53 posts
since Feb 2007

Permalink

Jun 9th, 2007

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

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

MohitB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since May 2007

Permalink

Feb 5th, 2010

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

hi,

I am shital I want to write login in html page. please help me out

shital050384

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Feb 2010

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP Forum Timeline: email sending form....help

Next Thread in ASP Forum Timeline: advice on timesheetmanager

DaniWeb Message

Cancel Changes

User Name
Password