Hi

I'm hoping that someone can help me with something I have tried to convert several times - and to my growing frustration I can't get to work.

The background is as follows:
I have an ASP site which I am busy converting to ASP.NET. One of the pages is an itinerary page, which pulled out an itinerary from the database and displayed it on the page.

The "Itinerary" database had fields:
TourCodeID
Day 1
Day 2
Day 3 etc...

so one row of the table would look as follows:

TourCodeID Day 1 Day 2 Day 3 Day 4 Day 5
TourA Day 1 Text Day 2 Text Day 4 Text Day 5 Text

My ASP procedure, would go through the recordset and produce the following table:

Day 1: Day 1 Text
Day 2- 3: Day 2 Text
Day 4: Day 4 Text
Day 5: Day 5 Text

(Because TourA has no entry in the database for Day 3, the procedure finds the next Day field with an entry and writes a Day range (ie, in this case Day 3 is empty, but Day 4 is not, so the procedure writes "Day 2-3", then moves on to write "Day 4" entry).

I need to try and get exactly the same thing working with ASP.NET but really am at a loss because I'm not sure how the simplest way to do this is. I have tried to use a Repeater, but couldn't manage to get it to ignore blank days (and consequently write in day ranges, ie "Day 2-3"). I then tried to create a ASP.NET procedure that filled a DataSet with the relevant tour and then went through the DataSet Table cells and wrote each non-blank entry to a table I was creating dynamically. This proved to be a bit too complicated for me though, as the error messages I received seemed to indicate.

I'm therefore at someone's mercy to see if they can help me convert my ASP procedure into an ASP.NET one (or if there is a simpler way to do it, please help me!). Without any further babbling, here is my ASP procedure:

    Sub createItinerary()

    'SQL QUERY
     sqlQuery = "SELECT * FROM Itinerary WHERE VoyageID='TourA'"

    'OPEN RECORDSET
     Set objRS = Server.CreateObject("ADODB.Recordset")
     objRS.Open sqlQuery, strConnect, adOpenStatic, adLockReadOnly, adCmdText

    'WRITE ITINERARY
     Response.Write "<table border=""0"" cellpadding=""4"" cellspacing=""1"" class=""col2"" width=""100%"">"
    'set intCounter to go through each field in the recordset and place value in the appropriate table cell
     If Not objRS.EOF Then
        For intCounter = 0 To (objRS.Fields.Count-1)
            'EXCLUDE BLANK FIELDS
             If Len(objRS.Fields.Item(intCounter).Value) > 0 Then
            'WRITE THE DAY FIELD
                 Response.Write "<tr><td align=""right"" class=""col3"" valign=""top"" width=""20%""><h3>"
            'CALL WRITE DAYS ROUTINE TO ESTIMATE RANGE
                Call WriteDays()
                Response.Write "</h3></td>"
            'WRITE DAY DETAILS
                Response.Write "<td align=""left"" class=""col1"" colspan=""2"" valign=""top"">" & objRS.Fields.Item(intCounter).Value & "</td></tr>"
            End If
        Next
     End If

     objRS.Close
     Set objRS = Nothing
     Response.Write "</table>"

    End Sub

'********************************************* ************************************************** *

Sub WriteDays()
intDay = objRS.Fields.Item(intCounter).Name
intDay = CInt(intDay) 'turn into an integer
intLoop = intCounter + 1 'set intloop to the next itinerary day (to see if there is a value)
    
'check if it is the last field on the recordset
If intLoop-1 = objRS.Fields.Count-1 Then
Response.Write "Day " & CStr(intDay) & ": "
End If

'if it is not the last record, the do while will work
Do While intLoop < objRS.Fields.Count-1
'collect day for the next field
intNextDay = objRS.Fields.Item(intLoop).Name
intNextDay = CInt(intNextDay)
'if the field isn't blank
If Len(objRS.Fields.Item(intLoop).Value) > 0 Then
'if variable is the next day on the itinerary then end the loop and write the day
If intNextDay - 1 = intDay Then
Response.Write "Day " & CStr(intDay) & ": "
'if the itinerary details covers several days, find the range and write it
Else
Response.Write "Day " & CStr(intDay) & " - " & CStr(intNextDay-1) & ": "
End If
'end the loop
intLoop = objRS.Fields.Count-1
'if the field is blank
Else
'if all the remaining fields are blank, write the last day
If IntLoop = objRS.Fields.Count -3 Then
Response.Write "Day " & CStr(intDay) & ": "
intLoop = objRS.Fields.Count-2
Else
intLoop = intLoop + 1
End If
End If
Loop
End Sub


************************************************** ****************

I really hope I've managed to make sense of what I would like to achieve. I'm sorry to have to ask for help from scratch with this, but I've tried several times to do this with so many different error messages that I felt it would be even more confusing to show what I'd tried to do (half of which I deleted out of frustration anyway).

I'll be forever in debt of the person who can help me create an ASP.NET procedure.

Shane

Well, it's amazing what a good night's sleep can do. I had another go at it today, only this time I ditched the idea of trying to create the table dynamically, and I've got it to work with the following:

        Function getItinerary As String
            Dim strbItin As New Stringbuilder("")
            Dim ConnectionString As String = ConfigurationSettings.AppSettings("connectString")
            Dim CommandText As String = "SELECT * FROM itinerary WHERE [it_Id]='TOURA'"
            Dim dbConnection As New OleDbConnection(ConnectionString)
            Dim dataSet As New DataSet("Itinerary")
            Dim dbDataAdapter As New OleDbDataAdapter(CommandText,dbConnection)
                dbDataAdapter.Fill(dataSet, "Itinerary")

                itinTable = dataSet.Tables("Itinerary")
                intRows = itinTable.Rows.Count
                intColumns = itinTable.Columns.Count

            If intRows = 0 Then
                strbItin.Append("Please contact one of our sales team for an itinerary for this tour")
            Else
                strbItin.Append("<table border=""0"" cellpadding=""2"" cellspacing=""0"" width=""100%"">")
                For intCounter = 1 To (intColumns - 1)
                    If itinTable.Rows(0).Item(intCounter).ToString <> String.Empty Then
                        strbItin.Append("<tr><td align=""left"" valign=""top"" width=""20%""><h4>" & getDays & "</h4></td>")
                        strbItin.Append("<td align=""left"" valign=""top"" width=""80%"">" & itinTable.Rows(0).Item(intCounter).ToString & "</td></tr>")
                    End If
                Next
                strbItin.Append("</table>")
            End If
            Return strbItin.ToString
        End Function

        Function getDays As String
            Dim strDay as String
            Dim intLoop As Integer = intCounter
            If intLoop = intColumns - 1 Then
                strDay = "Day " & intCounter
            Else
                Do While intLoop < intColumns - 1
                    If itinTable.Rows(0).Item(intLoop+1).ToString <> String.Empty Then
                        If intCounter = intLoop Then
                            strDay = "Day " & intCounter
                        Else
                            strDay = "Days " & intCounter & "-" & intLoop
                        End If
                        Exit Do
                    Else
                        If intLoop + 1 = intColumns - 1 Then
                            strDay = "Day " & CStr(intLoop)
                            Exit Do
                        Else
                             intLoop += 1
                        End If
                    End If
                Loop
            End If
            Return strDay
        End Function


I'm hoping that this code is okay - seems to work!