Multiple records on same page

mossimo · December 4th, 2003, 02:43 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.

jacob · December 4th, 2003, 03:01 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...

Code:

'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.

mossimo · December 4th, 2003, 03:56 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")%>

jacob · December 4th, 2003, 04:17 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:

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.

mossimo · December 4th, 2003, 04:31 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.

jacob · December 4th, 2003, 04:32 PM

So it my last post was right, then -- since I am no SQL guru -- this would probably do the trick...

Code:

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.

jacob · December 4th, 2003, 04:36 PM

Ups... A small error!

Quote:

quote:Response.Write "zone = " & zone & ": " & rs("ArticleData") & "<BR>"

Jacob.

jacob · December 4th, 2003, 04:51 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)...

Code:

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 Sub

Jacob.

mossimo · December 4th, 2003, 05: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)

jacob · December 4th, 2003, 05:41 PM

Code:

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