|
Subject:
|
Multiple records on same page
|
|
Posted By:
|
mossimo
|
Post Date:
|
12/4/2003 1:43:16 PM
|
I have a news system with different zones. Each zone is a different news category and I would like to display the most current article for each zone on the same page.
For example I would like something simple like this:
<%=Zone1%> <%=Zone2%>
I built a select case statement for each zone but I don’t know how to call each case on my page without opining a separate connection for each zone?
Select Case Zone
Case zone1
SQL="SELECT * FROM Articles WHERE ArticleZone = 'zone1' AND ArticleActive <= " & SQLDate(Now ) & " ORDER BY ArticleActive DESC"
Case zone2
SQL="SELECT * FROM Articles WHERE ArticleZone = 'zone2' AND ArticleActive <= " & SQLDate(Now ) & " ORDER BY ArticleActive DESC"
Case Else
SQL="SELECT * FROM Articles WHERE ArticleZone = 'zone0' AND ArticleActive <= " & SQLDate(Now ) & " ORDER BY ArticleActive DESC"
End Select
* SQLDate(Now ) is my function for chosing the most current article.
I really hope I made sense here if not let me know I’ll try to more clear.
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/4/2003 2:01:51 PM
|
It is not clear to me what you want. Where do you get the zone (initialization) from, and what format is it!?
I do not completely get your SQL statements, but assuming they are correct and the zone is identified by an integer you would be able to do something like this...'A string holding the zone representation from the database.
Dim strZone
'NOTE: A variable 'zone' should be initialized before this point.
Select Case zone
Case 1
strZone = "zone1"
Case 2
strZone = "zone2"
Case Else
strZone = ""
End Select
If strZone <> "" Then
sql = "SELECT * FROM Articles WHERE ArticleZone = '" & strZone & "' AND ArticleActive <= " & SQLDate(Now) & " ORDER BY ArticleActive DESC"
'Some code for using the sql statement here.
EndIf Is this OK?! (not tested)
Jacob.
|
|
Reply By:
|
mossimo
|
Reply Date:
|
12/4/2003 2:56:46 PM
|
To help clarify hers the db table structure
Table is named Articles and looks like this
ID, ArticleZone, ArticleActive, ArticleData
---------------------------------------------------------
1, 4, 11/25/2003, text/html content
2, 3, 10/24/2003, text/html content
3, 1, 12/22/2003, text/html content
I’m not sure what part of my SQL statements you don’ understand there pretty simple except the <= " & SQLDate(Now ) & part witch just call a function to check the ArticleActive date to display the most recent article but that’s not really important.
What you gave me is very close to what I was thinking I like it and it may work.
What I’m trying to do is display the most current article for each zone on page I’ve giving the zones numbers but they represent topics like zone1=current events and zone2=local news ect… The db field ArticleData contains the actual Article content and it’s the only part that gets displayed.
So using your code how would you call the case and display it? I tried this but my syntax is wrong it may be close. <%=case("1") && rsData("ArticleData")%>
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/4/2003 3:17:37 PM
|
OK, so you want to get the most recent article from each of the zones and display it...!? Is that it?
I think I start to get what you want, but then there is no use for the select statement! And you write...quote:
SQLDate(Now ) is my function for chosing the most current article.
You haven't even selected the articles yet, so how can you choose the most recent one!? Isn't it something like you should have an SQL guru to work out a serious SQL statement, or loop through the zones and query the most recent article for each zone?!
Jacob.
|
|
Reply By:
|
mossimo
|
Reply Date:
|
12/4/2003 3:31:45 PM
|
Is that it
You make it sound so easy I’ve been busting my hump on this for days.
Seriously I’d say forget the part about selecting the most recent I seem to be able to do that just fine.
But if you know how to populate the strZone from
WHERE ArticleZone = '" & strZone & "'
Using a response write like <%=case("1") && rsData("ArticleData")%> or somthing like it
Or just simply show news for each zone I would be grateful.
And thanks for your quick replys.
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/4/2003 3:32:17 PM
|
So it my last post was right, then -- since I am no SQL guru -- this would probably do the trick...Sub WriteMostRecentArticlesInEachZone()
Dim db
Set db = Server.CreateObject ("ADODB.Connection")
db.Open ("[some connection string]")
Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")
Dim zone, sql
zone = 1
While zone > 0
sql = "SELECT * FROM Articles " &
sql = sql & "WHERE ArticleZone=" & zone & " "
sql = sql & "ORDER BY ArticleActive DESC"
rs.Open sql, db
If Not rs.EOF Then
zone = zone + 1
Response.Write "zone = " & zone & ": " & rs("ArticleData") + "<BR>"
Else
zone = -1
End If
rs.Close
Wend
Set rs = Nothing
db.Close
Set db = Nothing
End Sub(it is not tested, so no promises, my VB is a bit rusty)
Call this routine, and it will hopefully give you what you want.
Jacob.
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/4/2003 3:36:06 PM
|
Ups... A small error!
quote:
Response.Write "zone = " & zone & ": " & rs("ArticleData") & "<BR>"
Jacob.
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/4/2003 3:51:33 PM
|
I failed to see that you use strings. I used numbers since that was what you wrote in the table. I have put in the select that maps the integers to strings, however I would reconsider this (the mapping) since it is not a very dynamic structure, to have hardcoded labels in code, which refers to strings in a database, but here it is (same garanties as before)...Sub WriteMostRecentArticlesInEachZone()
Dim db
Set db = Server.CreateObject ("ADODB.Connection")
db.Open ("[some connection string]")
Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")
Dim zone, sql, strZone
zone = 1
While zone > 0
Select Case zone
Case 1
strZone = "zone1"
Case 2
strZone = "zone2"
Case Else
strZone = ""
End Select
sql = "SELECT * FROM Articles " &
sql = sql & "WHERE ArticleZone='" & strZone & "' "
sql = sql & "ORDER BY ArticleActive DESC"
If strZone <> "" Then
rs.Open sql, db
If Not rs.EOF Then
zone = zone + 1
Response.Write "zone: " & strZone & ": " & rs("ArticleData") + "<BR>"
Else
zone = -1
End If
rs.Close
Else
zone = -1
End If
Wend
Set rs = Nothing
db.Close
Set db = Nothing
End SubJacob.
|
|
Reply By:
|
mossimo
|
Reply Date:
|
12/4/2003 4:27:27 PM
|
Beautiful worked right out of the box.
It’s almost perfect but the key is to select what zones to display. If you don’t mind one last question is there a way to pass a variable in the WriteMostRecentArticlesInEachZone to tell it what zones to show?
Like: WriteMostRecentArticlesInEachZone (zone1)
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/4/2003 4:41:06 PM
|
Sub WriteMostRecentArticlesInEachZone(zone)
Dim db, rs
Dim sql, strZone
Select Case zone
Case 1
strZone = "zone1"
Case 2
strZone = "zone2"
Case Else
strZone = ""
End Select
If strZone <> "" Then
sql = "SELECT * FROM Articles " &
sql = sql & "WHERE ArticleZone='" & strZone & "' "
sql = sql & "ORDER BY ArticleActive DESC"
Set db = Server.CreateObject ("ADODB.Connection")
db.Open ("[some connection string]")
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open sql, db
If Not rs.EOF Then
Response.Write "zone: " & strZone & ": " & rs("ArticleData")
Else
Response.Write "No articles for the zone named " & strZone & "."
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Else
Response.Write "The zone name was not specified."
End If
End Sub
|
|
Reply By:
|
mossimo
|
Reply Date:
|
12/4/2003 4:51:34 PM
|
Works great!
Thanks Jacob for your help I really appreciate it.
mossimo 
|
