473,847 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to retrive data from different Ms Access tables using ASP

nehashri
49 New Member
hi

i am designing a database using Ms Access and ASP. i have 3 tables in access namely 'PERSONAL', other as 'POLICY' and 3rd one is named as 'STAFF'. in the contact table i have ID, Name, Children as fields. Also in policy table the firlds are:- ID, date_of_policy, no_policy, amount_paid, amount_balance and similarly the 3rd ie., staff has few fields. all three are linked my a common field which is ID

all i want is when the user searches for a perticular name (from frontend) it should search through table- 'PERSONAL' (fields- Name and Children). once it get the perticular name with the help of the ID it should retrive the datafrom both 'POLICY' and 'STAFF' of that perticular Name.

i do know how to search and retrive data from the same table but got a bit confused on how to retrive data from different tables using the 'PERSONAL' table just for reference and linking to the other 2 tables

plz help
thanking
neha
May 16 '07 #1
23 3435
jhardman
3,406 Recognized Expert Specialist
Neha,

You don't need to open the tables separately, try:
Expand|Select|Wrap|Line Numbers
  1. query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POLICY.ID AND POLICY.ID=STAF.ID AND Name = 'Joe'"
  2.  
This pulls up all of the data for the same ID as if it was in a single table.

Let me know if this helps.

Jared
May 17 '07 #2
nehashri
49 New Member
thanks Jared

this helped me but my problem is that i cannt specify name = Joe.....
it depends on the user which name they select on the front end [ASP] the information of that perticular name (typed by user) should be displayed (& there can be thousands of name).
plz help
neha
May 21 '07 #3
jhardman
3,406 Recognized Expert Specialist
Neha,

Right, I just meant for this as an example. The query string is just a string variable arranged in such a way that a db driver can understand it. You assemble the string the same way you assemble any string:

Expand|Select|Wrap|Line Numbers
  1. query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POLICY.ID AND POLICY.ID=STAF.ID AND Name = '"
  2. query = query & request("nameOfLogin") & "'"
  3.  
nameOFLogin is whatever you called the login name on the form:
[html]<input type="text" name="nameOfLog in">
[/html]

Please let me know if this makes sense.

Jared
May 24 '07 #4
nehashri
49 New Member
Neha,

Right, I just meant for this as an example. The query string is just a string variable arranged in such a way that a db driver can understand it. You assemble the string the same way you assemble any string:

Expand|Select|Wrap|Line Numbers
  1. query = "SELECT * FROM PERSONAL, POLICY, STAFF, WHERE PERSONAL.ID=POLICY.ID AND POLICY.ID=STAF.ID AND Name = '"
  2. query = query & request("nameOfLogin") & "'"
  3.  
nameOFLogin is whatever you called the login name on the form:
[html]<input type="text" name="nameOfLog in">
[/html]

Please let me know if this makes sense.

Jared
hi Jared i have tried your query with changes in name but there is some error comming which i am not able to makeout
the following is the sql i am using:-

Expand|Select|Wrap|Line Numbers
  1. strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = '"
  2. strSQL = strSQL & request("search") &"' "
when i use this code the result is not shown at all

so i tried doing like this:-
Expand|Select|Wrap|Line Numbers
  1. strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = "& request("search") &" "     
and the error that is showing is
Expected end of statement
/MyWeb/mylogin/testquery.asp, line 24, column 112
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ ID = Chemistry.Drug_ ID And
Drug_Name.D_Nam e = "strSearch" "
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------
---------------------^

i dont understand what is wrong is these codes
neha
May 24 '07 #5
jhardman
3,406 Recognized Expert Specialist
hi Jared i have tried your query with changes in name but there is some error comming which i am not able to makeout
the following is the sql i am using:-

Expand|Select|Wrap|Line Numbers
  1. strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = '"
  2. strSQL = strSQL & request("search") &"' "
when i use this code the result is not shown at all

so i tried doing like this:-
Expand|Select|Wrap|Line Numbers
  1. strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = "& request("search") &" "     
and the error that is showing is
Expected end of statement
/MyWeb/mylogin/testquery.asp, line 24, column 112
strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ ID = Chemistry.Drug_ ID And
Drug_Name.D_Nam e = "strSearch" "
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------
---------------------^

i dont understand what is wrong is these codes
neha
The text field should definitely be in single quotes so that the query looks like this:
Expand|Select|Wrap|Line Numbers
  1. AND D_Name = 'aspirin'
  2.  
Possibly this isn't bringing up any hits because there are no exact matches, I think this might be case sensitive (an exact match is necessary). Instead you could try:
Expand|Select|Wrap|Line Numbers
  1. AND D_Name LIKE %aspirin%
  2.  
This should not be case sensitive, and doesn't produce only exact matches.
After I write the query, to make sure it looks good, I often write the query to an HTML comment:
Expand|Select|Wrap|Line Numbers
  1. response.write "<!-- strSQL: " & strSQL & " -->" & vbNewLine
  2.  
This lets me verify that the database is seeing what it should

Let me know if this helps.

Jared
May 24 '07 #6
nehashri
49 New Member
Jared
thanks for the help
i am very much sure that this is not case sensitive... to retrive data from the same table i have used almost the same coding and it worked perfectly alright....
this is the whole code i am using
Expand|Select|Wrap|Line Numbers
  1. <HTML>
  2. <BODY>
  3. <%
  4. Actionvar=Request.QueryString("actionvar")
  5. strURL = Request.ServerVariables("URL")
  6. strSearch = Request.querystring("search")
  7. %>
  8. <p>Search our sample db by first or last name.  (% returns all)</p>
  9.                 <form action="<%= strURL %>" method="post">
  10.     <input type = "text" name="search" size = "30" value="<%= strSearch %>" />
  11.     <input type="submit" />
  12.     </form>
  13.     <%   
  14.     If strSearch <> "" Then
  15.                 Set conn = server.createobject("adodb.connection")
  16.     DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
  17.                 DSNtemp=dsntemp & "DBQ=" & server.mappath("Databases/nprcDrugsDB.mdb")
  18.     conn.Open DSNtemp
  19.  
  20. strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID And Drug_Name.D_Name = '"
  21. strSQL = strSQL & request.form("search") &"' "
  22.  
  23. Set rstSearch = conn.execute(strSQL)
  24.     %>
  25.     <table border="1">
  26.         <tr>
  27.             <th>Name</th>
  28.             <th>Salts</th>
  29.         </tr>
  30.         <%
  31.             Do While Not rstSearch.EOF
  32.         %>
  33.                    <tr>
  34.             <td><%= rstSearch.Fields("Drug_Name.D_Name").Value %></td>
  35.             <td><%= rstSearch.Fields("Chemistry.Salts").Value %></td>
  36.         </tr>
  37.         <%
  38.         rstSearch.MoveNext
  39.  
  40.             Loop
  41.     %>
  42.     </table>
  43.     <%
  44.     rstSearch.Close
  45.     conn.Close
  46.     Set conn = nothing
  47.     Set strSQL = nothing
  48. End If
  49. %>
  50. </BODY>
  51. </HTML>
i guess i am not declaring some thing .........becaus e when i execute this code only the HTML codes work and not the ASP Codes...... do i have to declare strSQL else where also or some thing is wrong for retrival ASP code of mine
neha
May 25 '07 #7
nehashri
49 New Member
is my ASP code to retrive data after the SQL query is not correct????? how do i retrive my queried data in a tabular form then?????
neha
May 25 '07 #8
jhardman
3,406 Recognized Expert Specialist
Neha,

It definitely looks OK to me. I don't see any problems. Try using "LIKE" instead of "=" though and put your drug name in "%" instead of "'"

Jared
May 25 '07 #9
nehashri
49 New Member
hi Jared,
well i have changed my SQL codes as u told
Expand|Select|Wrap|Line Numbers
  1. strSQL= "SELECT * FROM Drug_Name, Chemistry WHERE Drug_Name.Drug_ID = Chemistry.Drug_ID AND Drug_Name.D_name LIKE '%" & strSearch & "%' " 
but the result is same only empty tables comes and no information.... .what im thinking is that ASP codes is not working over here or SQL query is not taken in to account........ well im sure of connectivity is correct as its working fine when only one table is involved....... .
what do i do i need to complete this by this weekend and its giving me so much problem...
any waz thank so much for the help u provided
thanks again plz let me know if you get an idea of whats wrong over here.......
thanks again
neha
May 28 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

16
3047
by: D Witherspoon | last post by:
I am developing a Windows Forms application in VB.NET that will use .NET remoting to access the data tier classes. A very simple way I have come up with is by creating typed (.xsd) datasets. For example dsParts.xsd and including that in the data tier. I then will create a class that looks like this Public Class CPart Inherits dsParts
32
3236
by: Neil Ginsberg | last post by:
We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC linked tables. I recently created a new set of tables for the app, and users are complaining that unsaved data is being lost when they move to a new record. This seems to be the case when there are multiple users. When there is a single user using it, we don't seem to have that problem. It seems that we had this problem when we first converted from an MDB back end...
5
3756
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for Office 2000. I am creating a database to track student athletes. I have created the following tables. The table title is to the far left, with fields under each. The common field will be the StudentID field, which is their student number assigned...
5
2268
by: Nico | last post by:
My database have 20 tables and many users. I wish to store encrypted data in 3 tables and have only 3 users have access to them, walking into tables or using forms. Can someone point me a direct help or something to read? Tnx Nico
2
1667
by: Terry | last post by:
Any .dll or COM+ for .Net can help me retrive data from a Access file? The table contain several columns and the last one is a container which I store Article; because each article is a little bit huge, so when I store it, it ask me if I want to pack it (the article) into a object then I click 'Yes'. But now I need to retrive data from the file and show it by datagrid, and now I don't know what kind of data type it is (when I click it, it...
9
4032
by: Anil Gupte | last post by:
After reading a tutorial and fiddling, I finally got this to work. I can now put two tables created with a DataTable class into a DataRelation. Phew! And it works! Dim tblSliceInfo As New DataTable("SliceInfo") Dim tblSliceRatings As New DataTable("SliceRatings") '.... All the adding datacolumns, datarows, etc. goes here.. DatasetInit.Tables.Add(tblSliceInfo)
1
3384
by: amritranjan | last post by:
How to retrive image file from MS access database and display this in another JSPpage -------------------------------------------------------------------------------- This is my Jsp code for image upload in database: -----------Upload.jsp---------------- <html> <head> <title>Account Details </title>
6
2426
by: Wesley Peace | last post by:
I hate to cross post, but I've gotten no answer yet on a problem I'm having with visual studio 2008. I've created a series of forms with controls to access a Access database tables. The connection string works fine and the tables are added to the project without a problem. When I create the tables they appear to bind and I am able to preview the data in the database in design mode; however, at runtime no data is displayed and the...
0
9881
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10982
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10645
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10331
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9480
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7880
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5718
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5909
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.