VBScript recordset help

VBScript recordset help

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 6040 Views - Last Post: 03 August 2007 - 12:24 PM Rate Topic: -----

#1 cool263   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-July 07

VBScript recordset help

Post icon  Posted 30 July 2007 - 10:15 AM

I'm building a web page that has a list of different realestate locations and pulls this info from a database. The user can filter and view the properties by location. My problem is, is that I would like to display a header above the first location, but not have it repeat.

For example, say I have 5 listings in the Northern Heights area. I'd like to display "Nothern Heights" as the first line and the following lines are the listings. When the loop finds the next area, say in the Harbor View location, have it display "Harbor View" and then show the listings for that location and so on for all the locations in my db.

Anyone have any idea how to do this? Any help would be appreciated. This is an ASP page using VBScript.

Is This A Good Question/Topic? 0
  • +

Replies To: VBScript recordset help

#2 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 10:23 AM

View Postcool263, on 30 Jul, 2007 - 10:15 AM, said:

I'm building a web page that has a list of different realestate locations and pulls this info from a database. The user can filter and view the properties by location. My problem is, is that I would like to display a header above the first location, but not have it repeat.

For example, say I have 5 listings in the Northern Heights area. I'd like to display "Nothern Heights" as the first line and the following lines are the listings. When the loop finds the next area, say in the Harbor View location, have it display "Harbor View" and then show the listings for that location and so on for all the locations in my db.

Anyone have any idea how to do this? Any help would be appreciated. This is an ASP page using VBScript.


Is the location being returned with the query? I assume you are showing miltiple areas in a single page and that's whats causing the problem?
Was This Post Helpful? 0
  • +
  • -

#3 cool263   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 10:37 AM

Yes, to both questions.
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 10:40 AM

View Postcool263, on 30 Jul, 2007 - 10:37 AM, said:

Yes, to both questions.


Can you post your code for me, just the loop that displays the listings to the page, and I'll see what I can come up with.
Was This Post Helpful? 0
  • +
  • -

#5 cool263   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 10:48 AM

It's probably not the best code, but here it is:



<%


	sc = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
	sc = sc & Server.MapPath("properties.xls")
	sc = sc & ";Extended Properties=Excel 8.0"

	Set cn = Server.CreateObject("ADODB.Connection")
	cn.Open sc

	Set rs = Cn.Execute("SELECT * FROM [final$]" & tmpOrderBy)

	With (Response)
	
	counter=0

	Do while rs.EOF = False

	strNewCity = Replace(rs("City")," ", "+")
	strNewAddress = Replace(rs("Address")," ", "+")

			.Write "<table border=0 cellspacing=0 cellpadding=0 class=table>"
			.Write "<tr><td colspan=3>"
			.Write "<div class=name>" & rs("Name") & "</div>"
			.Write "</td></tr>"
			.Write "<tr><td class=pad valign=top>"
			.Write "<table border=0 cellpadding=0 cellspacing=0>"
			.Write image1
			.Write "<tr><td class=text><span class=emph>City:</span> " & rs("Location") & "</td></tr>"
			.Write "<tr><td class=text>" & rs("Address") & "</td></tr>"
			.Write "<tr><td class=text>" & rs("City") & ", " & rs("State") & " " & rs("Zip") & "</td></tr>"
			.Write "<tr><td class=text><span class=emph>Phone:</span> " & rs("Phone") & "</td></tr>"
		.Write "</table>"
			.Write "</td>"
			.Write "<td align=right class=pad valign=top>"
			.Write Email
			.Write "<a href=http://www.mapquest.com/maps/map.adp?city=" & strNewCity & "&state=" & rs("State") & "&address=" & strNewAddress & "&country=us&zoom=8 target=new>Get A Map</a> "
			.Write "<a href=http://www.mapquest.com/maps/map.adp?city=" & strNewCity & "&state=" & rs("State") & "&address=" & strNewAddress & "&country=us&zoom=8 target=new><img src=images/map.gif border=0 alt=Map align=absmiddle vspace=1 /></a><br />"
		.Write Url
			.Write "<a href=#top>Top</a> <a href=#top><img src=images/top.gif border=0  alt=Top align=absmiddle vspace=1></a><br />"
		.Write "</td></tr>"
			.Write "<tr><td class=desc colspan=3>" & Desc & "</td></tr>"
			.Write "</table>"
			.Write "<p>"
			rs.MoveNext
	Loop
	End With
	Set rs = Nothing

	cn.Close
	Set cn = Nothing


%>




Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 11:05 AM

I'm assuming rs("Name") is what you're trying to display as a header. Try something like this (it may not work exactly at first but you can tweak it as needed, if you cant let me know and Ill help as much as I can). What you need to do is actually loop within a loop. The first loop is, of course, to display all the properties, the 2nd loop loops through all the properties for the ones for that specific location (rs("Name")), then at the end of that loop it resets the variable for rs("Name") and starts the loop all over again. This should give you a header, all the properties for that header, the next header, and so on.

<%


	sc = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
	sc = sc & Server.MapPath("properties.xls")
	sc = sc & ";Extended Properties=Excel 8.0"

	Set cn = Server.CreateObject("ADODB.Connection")
	cn.Open sc

	Set rs = Cn.Execute("SELECT * FROM [final$]" & tmpOrderBy)

	With (Response)
	
	counter=0
	Dim sLocation,sLocation2
	Do while rs.EOF = False
	sLocation = rs("Name")
	sLocation2 = sLocation
	strNewCity = Replace(rs("City")," ", "+")
	strNewAddress = Replace(rs("Address")," ", "+")

			.Write "<table border=0 cellspacing=0 cellpadding=0 class=table>"
			.Write "<tr><td colspan=3>"
			.Write "<div class=name>" & sLocation & "</div>"
			.Write "</td></tr>"
			.Write "<tr><td class=pad valign=top>"
			.Write "<table border=0 cellpadding=0 cellspacing=0>"
			.Write image1
	Do While Not rs.EOF and (sLocation = sLocation2)
			.Write "<tr><td class=text><span class=emph>City:</span> " & rs("Location") & "</td></tr>"
			.Write "<tr><td class=text>" & rs("Address") & "</td></tr>"
			.Write "<tr><td class=text>" & rs("City") & ", " & rs("State") & " " & rs("Zip") & "</td></tr>"
			.Write "<tr><td class=text><span class=emph>Phone:</span> " & rs("Phone") & "</td></tr>"
		.Write "</table>"
			.Write "</td>"
			.Write "<td align=right class=pad valign=top>"
			.Write Email
			.Write "<a href=http://www.mapquest.com/maps/map.adp?city=" & strNewCity & "&state=" & rs("State") & "&address=" & strNewAddress & "&country=us&zoom=8 target=new>Get A Map</a> "
			.Write "<a href=http://www.mapquest.com/maps/map.adp?city=" & strNewCity & "&state=" & rs("State") & "&address=" & strNewAddress & "&country=us&zoom=8 target=new><img src=images/map.gif border=0 alt=Map align=absmiddle vspace=1 /></a><br />"
		.Write Url
			.Write "<a href=#top>Top</a> <a href=#top><img src=images/top.gif border=0  alt=Top align=absmiddle vspace=1></a><br />"
		.Write "</td></tr>"
			.Write "<tr><td class=desc colspan=3>" & Desc & "</td></tr>"
			.Write "</table>"
			.Write "<p>"
	  rs.MoveNext
	  If Not rs.EOF Then
		 sLocation2 = rs("Name")
	  End If
	  Loop
			rs.MoveNext
	Loop
	End With
	Set rs = Nothing

	cn.Close
	Set cn = Nothing


Was This Post Helpful? 0
  • +
  • -

#7 cool263   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 11:31 AM

That worked great! Had to make a minor adjustment, but it works perfectly. Thank you very much for your help, I really appreciate it!!
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 11:33 AM

No problem, thats what we're here for :) Have anymore problems feel free to come back.

By the way you dont have to just post programming questions here, the Caffeine Lounge is a great place to hang out.
Was This Post Helpful? 0
  • +
  • -

#9 Amadeus   User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 253
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: VBScript recordset help

Posted 30 July 2007 - 11:35 AM

Good job, Psycho! And I thought I was the only one left here who worked with classic vbscript! ;)
Was This Post Helpful? 0
  • +
  • -

#10 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 11:39 AM

View PostAmadeus, on 30 Jul, 2007 - 11:35 AM, said:

Good job, Psycho! And I thought I was the only one left here who worked with classic vbscript! ;)

I work with it if I have to, otherwise I stick with ASP.Net. Also, I've used classic ASP for so long it's like riding a bicycle, once you learn you never forget. I bet I have every ASP website I've ever created on one of my hard drives (thats 10+ years of coding by the way)
Was This Post Helpful? 0
  • +
  • -

#11 Amadeus   User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 253
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: VBScript recordset help

Posted 30 July 2007 - 11:43 AM

I hear you...VBScript was the language I used when I broke into web programming...seems like a long time ago now!
Was This Post Helpful? 0
  • +
  • -

#12 cool263   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 11:45 AM

One of these years, I'll graduate to .NET, but right now, I'm still learning to program. :)
Was This Post Helpful? 0
  • +
  • -

#13 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 30 July 2007 - 11:48 AM

View Postcool263, on 30 Jul, 2007 - 11:45 AM, said:

One of these years, I'll graduate to .NET, but right now, I'm still learning to program. :)


Baby steps my friend, baby steps. Don't want to overwhelm yourself
Was This Post Helpful? 0
  • +
  • -

#14 cool263   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-July 07

Re: VBScript recordset help

Posted 31 July 2007 - 12:59 PM

PsychoCoder (or anyone else for that matter) -

I was hoping maybe you could help me one more time.

This time, I've added a counter to yesterday's code. The counter assigns an ID number to a link, which when clicked, (via Javascript) opens a hidden <div> tag displaying more information. It works great except when the page is in its Location filter view. The counter doesn't work with the properties listed under the same Location header.

Here's the code:

<%


	sc = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
	sc = sc & Server.MapPath("properties.xls")
	sc = sc & ";Extended Properties=Excel 8.0"

	Set cn = Server.CreateObject("ADODB.Connection")
	cn.Open sc

	Set rs = Cn.Execute("SELECT * FROM [final$]" & tmpOrderBy)

	With (Response)

	Dim sLocation,sLocation2,loc

	counter=0

	Do while rs.EOF = False 

		sLocation = rs("Location")
		sLocation2 = sLocation

	counter = counter + 1

	If strOrderBy = "city" Then
		loc = "<table border=0><tr><td class=header colspan=3>" & sLocation & "</td></tr></table>"
	Else
		loc = " "
	End If

	strNewCity = Replace(rs("City")," ", "+")
	strNewAddress = Replace(rs("Address")," ", "+")
			.Write loc
			Do While Not rs.EOF and (sLocation = sLocation2)
			.Write "<table border=0 cellspacing=0 cellpadding=0 class=table>"
			.Write "<tr><td colspan=3>"
			.Write "<div class=name>" & rs("Name") & "</div>"
			.Write "</td></tr>"
			.Write "<tr><td class=pad valign=top>"
			.Write "<table border=0 cellpadding=0 cellspacing=0>"
			.Write image1
			.Write "<tr><td class=text><span class=emph>City:</span> " & rs("Location") & "</td></tr>"
			.Write "<tr><td class=text>" & rs("Address") & "</td></tr>"
			.Write "<tr><td class=text>" & rs("City") & ", " & rs("State") & " " & rs("Zip") & "</td></tr>"
			.Write "<tr><td class=text><span class=emph>Phone:</span> " & rs("Phone") & "</td></tr>"
		.Write "</table>"
			.Write "</td>"
			.Write "<td align=right class=pad valign=top>"
			.Write "<a href=http://www.mapquest.com/maps/map.adp?city=" & strNewCity & "&state=" & rs("State") & "&address=" & strNewAddress & "&country=us&zoom=8 target=new>Get A Map</a> "
			.Write "<a href=http://www.mapquest.com/maps/map.adp?city=" & strNewCity & "&state=" & rs("State") & "&address=" & strNewAddress & "&country=us&zoom=8 target=new><img src=images/map.gif border=0 alt=Map align=absmiddle vspace=1 /></a><br />"
			.Write "<a href=#top>Top</a> <a href=#top><img src=images/top.gif border=0  alt=Top align=absmiddle vspace=1></a><br />"
		.Write "</td></tr>"
			.Write "<tr><td class=desc colspan=3>" & Desc & "</td></tr>"
			.Write "</table>"
			.Write "<p>"
			.Write "<table class=table>"
			.Write "<tr>"
			.Write "<td colspan=3>"
			.Write "<a href=java script:MI('" & counter & "') onmouseover=""window.status=''; return true"" onmouseout=""window.status=''; return true""><img id=moreinfoimg_" & counter & " src=images/more_info_down.gif border=0 width=79 height=36 alt=More Info /></a><p>"
			.Write "<div style=display:none; id='moreInfo_" & counter & "'>"
			.Write "<table align=right>"
			.Write image2
			.Write image3
			.Write "</table>"
			.Write "<p>"
			.Write "<span class=text>" & rs("More") & "</span>"
			.Write "</div>"
			.Write "</td></tr>"	
			.Write "</table>"
			.Write "<p>"
	  rs.MoveNext
	  If Not rs.EOF Then
		 sLocation2 = rs("Location")
	  End If
	  Loop

	Loop
	End With
	Set rs = Nothing

	cn.Close
	Set cn = Nothing


%>




Any ideas? I'm sure it's probably pretty simple.
Was This Post Helpful? 0
  • +
  • -

#15 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1660
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: VBScript recordset help

Posted 31 July 2007 - 03:38 PM

View Postcool263, on 31 Jul, 2007 - 12:59 PM, said:

PsychoCoder (or anyone else for that matter) -

I was hoping maybe you could help me one more time.

This time, I've added a counter to yesterday's code. The counter assigns an ID number to a link, which when clicked, (via Javascript) opens a hidden <div> tag displaying more information. It works great except when the page is in its Location filter view. The counter doesn't work with the properties listed under the same Location header.

Here's the code:

Any ideas? I'm sure it's probably pretty simple.


I'm sorry, I don't understand the problem or the question?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2