Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

asp& sql how-to display records from tbl 1

Status
Not open for further replies.
matejv

matejv

Programmer
Jul 30, 2003
14
0
0
SI
Hello,

let's say we have a table in Access with two fields: MyName, MyArea.

This is probably a very newbie question (but we all have to learn sometime) - what I would like to achieve is:

(- get the records from db with an SQL query)
(- put them in a recordset)
- then display existing areas with respective names in alphabetical order.

I know how to do this in general (by calling every area individually and then displaying the records that belong there), but I would very much prefer a simpler, shorter solution... I am almost sure it exists. Thanks for your help!
 
Sort by date Sort by votes
Here's a simple example from Microsoft using the Authors database:
Code: 
<HTML>
    <HEAD>
        <TITLE>Simple ADO Query</TITLE>
    </HEAD>

    <BODY BGCOLOR=&quot;White&quot; topmargin=&quot;10&quot; leftmargin=&quot;10&quot;>

		<!-- Display Header -->

		<font size=&quot;4&quot; face=&quot;Arial, Helvetica&quot;>
		<b>Simple ADO Query with ASP</b></font><br>
    
		<hr size=&quot;1&quot; color=&quot;#000000&quot;>

		Contacts within the Authors Database:<br><br>

		<%
			Dim oConn		
			Dim oRs		
			Dim filePath		
			Dim Index		

			
			' Map authors database to physical path
			filePath = Server.MapPath(&quot;authors.mdb&quot;)


			' Create ADO Connection Component to connect
			' with sample database
			

			
			Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
			oConn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & filePath
			
			
			' Execute a SQL query and store the results
			' within recordset
			
			Set oRs = oConn.Execute(&quot;SELECT * From authors&quot;)
		%>


		<TABLE border = 1>
		<%  
			Do while (Not oRs.eof) %>

				<tr>
					<% For Index=0 to (oRs.fields.count-1) %>
						<TD VAlign=top><% = oRs(Index)%></TD>
					<% Next %>
				</tr>
            
				<% oRs.MoveNext 
			Loop 
		%>


		</TABLE>


		<%   
			oRs.close
			oConn.close 
		%>

	</BODY>
</HTML>

To modify the code simply change the referenced database and field names. Also you can change the sql statement to include an order by statement so that they are displayed alphabetically. e.g.
Code: 
Set oRs = oConn.Execute(&quot;SELECT * From authors&quot;)
would become:
Code: 
Set oRs = oConn.Execute(&quot;SELECT * From authors order by name&quot;)

Let us know if you need any further help.


----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty ( or read FAQ222-2244 on how to get better results.
 
Upvote 0 Downvote
Thank you, both. I wasn't clear enough. I know how to get records from a db and how to order them.

The problem i am having is this:

1. Example records in db:

MyName / MyArea
Jack / Sports
Jill / Sports
Mary / Cars
Minnie / Cars
Lou / Cars
John / Chess
Mike / Chess

2. Preferred output:

Sports
- Jack
- Jill

Cars
- Mary
- Minnie
- Lou

Chess
- John
- Mike

3. Challenges (what the script should do):
- check to see what areas are there connected to names in db (there could be theoretically &quot;n&quot; names and &quot;n&quot; areas)
- display areas and respective names

4. The alternative (I know how to make):

'conn to db
'make rsAreas and &quot;SELECT MyArea From MyDB ORDER BY MyArea DESC&quot;

For iArea = 0 to rsAreas.RecordCount

Response.Write rsAreas(&quot;MyArea&quot;)

'make rsNames and get names where MyArea equals rsAreas(&quot;MyArea&quot;) and then write them out one after the other and close rsNames

rsAreas.Movenext

Next

'close everything

5. Question
- is there a simpler, more efficent way to achieve the goal?


Thanks so much!

 
Upvote 0 Downvote
Yes.

<%
strSQL=&quot;SELECT myArea, myName FROM myTable Order by myArea, myName&quot;

set conn=server.createobject(&quot;ADODB.CONNECTION&quot;)
conn.open YourConnectionString
currentArea = &quot;&quot;
set rs = conn.execute(sql)
do while not rs.eof
if rs(&quot;myArea&quot;) <> currentArea then
response.write rs(&quot;myArea&quot;) & &quot;<p>&quot;
currentArea = rs(&quot;myArea&quot;)
end if
response.write rs(&quot;myName&quot;) & &quot;<br>&quot;
rs.movenext
loop
set rs=nothing
conn.close
set conn=nothing
%>
 
Upvote 0 Downvote
Thanks,

your reply was helpful, after I jumbled it around a bit.
This was the &quot;simpler&quot; solution that I was looking for.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
99
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
116
xwb
xwb
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
92
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
79
leifoet
leifoet
Swi
  • Locked
  • Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
112
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top