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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group Display

Status
Not open for further replies.
jamiecottonuk

jamiecottonuk

Technical User
Oct 29, 2007
12
GB
Hi would like to display data from a database within groups the following

Department 1
file namea
file namec

Department 2
file nameb
file named

Based unpon the following query

sSQL="SELECT * FROM search WHERE search_name LIKE ""%" & frmSearch & "%""

I have the following which prints out the headings for the departemnts

Code: 
Do while Not recordset2.eof
Response.Write "<table><tr><th>"
Response.Write (recordset2("department"))
Response.Write "</th></tr>"
Response.Write "<tr><td>"
Response.Write "</td></tr>"
Response.Write "</table>"
recordset2.MoveNext
loop
But how do I get the filenames associated with the departments under the corresponding headings?
 
Sort by date Sort by votes
Perhaps you could use an ORDER BY clause in your SQL query together with a temp variable in your Do loop.
Code: 
[...]  [green]'assume the top part of page defines ADO connection and frmSearch[/green] 

sSQL="SELECT * FROM search WHERE search_name LIKE '%" & frmSearch & "%' [red]ORDER BY department[/red]"

Set recordset2= cn.Execute(sSQL)

Dim LastDepartment
LastDepartment = ""

Response.Write "<table>"
Do while Not recordset2.eof
  IF LastDepartment <> recordset2("department") & "" THEN
    Response.Write "<tr><th>" & recordset2("department") & "&nbsp;</th></tr>"
    LastDepartment = recordset2("department") & "" 
  END IF
  Response.Write "<tr><td>" & recordset2("search_name ") & "&nbsp;</td></tr>"

  recordset2.MoveNext
Loop

Response.Write "</table>"

[...]  [green]'remainder of page below[/green]

So the department header is only printed when the value of field "department" on the current row is different from the previous value of a temp variable named LastDepartment.
 
Upvote 0 Downvote
This is the complete page and I get

"Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/jamies_test_site/result.asp, line 33"

But i dont know why???

Code: 
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% 
	'declare your variables
	Dim connection, recordset2, frmSearch
	Dim sSQL, sConnString
	
	frmSearch = Request.Form("input")
	
	'declare SQL statement that will query the database
	sSQL="SELECT * FROM search WHERE search_name LIKE ""%" & frmSearch & "%"" ORDER BY department"
	
	'create an ADO connection and recordset object
	Set connection = Server.CreateObject("ADODB.connection") 
	Set recordset2 = Server.CreateObject("ADODB.Recordset")
	
	'define the connection string, specify database 
	'driver and the location of database 
	sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("search.mdb") 
	
	'Open the connection to the database
	Connection.Open sConnString
	
%>	
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<title>Untitled Document</title>
	</head>
	<body>
		<%
			Set recordset2= cn.Execute(sSQL)
			
			Dim LastDepartment
			LastDepartment = ""
			
			Response.Write "<table>"
			Do while Not recordset2.eof
				IF LastDepartment <> recordset2("department") & "" THEN
					Response.Write "<tr><th>" & recordset2("department") & "&nbsp;</th></tr>"
					LastDepartment = recordset2("department") & "" 
				END IF
				Response.Write "<tr><td>" & recordset2("search_name ") & "&nbsp;</td></tr>"
				
				recordset2.MoveNext
			Loop
			
			Response.Write "</table>"			
		%>
	</body>
</html>
 
Upvote 0 Downvote
Oh in my first post I was just assuming your connection variable was named cn but now that you've posted the full code I can see it is named Connection.
 
Upvote 0 Downvote
now if i change cn to Connection like
Code: 
Set recordset2= Connection.Execute(sSQL)

It complains about line 45

"ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal."

This line is
Code: 
Response.Write "<tr><td>" & recordset2("search_name ") & "&nbsp;</td></tr>"
 
Upvote 0 Downvote
So apparantly you have no column named search_name

Which brings questions about the SQL query.
 
Upvote 0 Downvote
Just noticed it should be ("search_name") and not ("search_name ")

No space.


Thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Swi
  • Locked
  • Question
Best/Quickest Grid to display data
Replies
1
Views
179
Swi
Swi
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
147
leifoet
leifoet
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
175
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
iis woos (not displaying pages correctly)
Replies
1
Views
181
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to display the contents of a drop-down list in a column structure ?
Replies
2
Views
100
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top