Looping through related data..?

[Jim in Arizona]

Usually, If i need special formatting, I don't use the datagrid control and
use a loop that processes a table for each record read from the database (as
in classic asp) like so:

============================================================

Dim strSQL, strResults As String
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objDataReader As SqlDataReader

strResults = ""

strSQL = "SELECT ID, ClientID, Field, Form, Employee, username,"
& _
" ip, probdt, Descript, isnotes, noshow FROM TProblem" &
_
" WHERE ((TProblem.noshow)= NO) ORDER BY ID DESC;"

objConnection = New
SqlConnection(ConfigurationManager.AppSettings("tickets"))
objCommand = New SqlCommand(strSQL, objConnection)

objConnection.Open()
objDataReader = objCommand.ExecuteReader()

Do While objDataReader.Read() = True
strResults += "<table cellpadding=""5"" cellspacing=""0""
border=""1"" bordercolor=""maroon"" width=""95%""><tr>"
strResults += "<td bgcolor=""red"" width=""10%""><b>ID:<br
/><font color=""white"">" & objDataReader("ID").ToString &
"</font></b></td>"
strResults += "<td bgcolor=""silver""
width=""30%""><b>Client ID:</b><br />" & objDataReader("ClientID").ToString
& "</td>"
strResults += "<td bgcolor=""silver""
width=""30%""><b>Field:</b><br />" & objDataReader("Field").ToString &
"</td>"
strResults += "<td bgcolor=""silver""
width=""30%""><b>Form:</b><br />" & objDataReader("Form").ToString & "</td>"

strResults += "</tr><tr>"

strResults += "<td bgcolor=""silver""
width=""10%""><b>Employee Name:<br /><font color=""navy"">" &
objDataReader("Employee").ToString & "</font></b></td>"
strResults += "<td bgcolor=""silver""
width=""30%""><b>Username:</b><br />" & objDataReader("username").ToString &
"</td>"
strResults += "<td bgcolor=""silver"" width=""30%""><b>IP
Address:</b><br />" & objDataReader("ip").ToString & "</td>"
strResults += "<td bgcolor=""silver""
width=""30%""><b>Date/Time:</b><br />" & objDataReader("probdt").ToString &
"</td>"

strResults += "</tr><tr>"

strResults += "<td colspan=""4""><b>Description:</b><br />"
& objDataReader("Descript").ToString & "</td>"
strResults += "</tr><tr>"
strResults += "<td colspan=""4""><b>HHS Info Systems
Notes:</b><br />" & objDataReader("isnotes").ToString & "<br /></td>"
strResults += "</tr></table>"
strResults += "<br />"
Loop
objDataReader.Close()
objConnection.Close()
datagrid1.InnerHTML = strResults
============================================================

I've not worked with related data much so trying to figure out my current
problem is a tough challenge for me.

I have two tables. The second of the table has a foregin key that is linked
to the primary key of the first table. The PK of the first table is auto
incremented (identity field)

My project, simply, is to display a table that displays data posted by our
employess that shows who they are and what their computer problem is along
with date, active directory username and IP. Then, the IT staff has a
seperate page that we view these problems on. From this page we also need to
be able to post messages to each individula employee posted problem so that
each message that we post (comment) is shown within the same html table
showing the employee's computer issue. That's what the related table is for,
so we have one row in the first table (the data that the employee posts) and
many related rows in the second table (our comments we post for each single
post from the employee).

Now, doing a loop to create an html table within the browser window for a
single data table isn't hard, as I show above. But, how would I go about
this with two related tables? I was thinking another loop within the loop to
loop through the related records and create the appropriate <tr>s and <td>s,
but I don't know how to make the relation.

Help!

Thanks,
Jim

 

[Lucas Tam]

Usually, If i need special formatting, I don't use the datagrid
control and use a loop that processes a table for each record read
from the database (as in classic asp) like so:

Hmmm... you should seriously consider using a datagrid. Take a look at
the OnItemDataBound event.

Or at the least... use some ASP.NET objects (i.e. Table, TableCell,
TableRow)...

 

[jasonkester]

Indeed. I'll second the OnItemDataBound suggestion. At the least, you
should look into the Repeater.

Even in classic ASP, I wouldn't go down the road you're travelling
except in extreme cases. And then I'd spin off a couple objects to
break it into managable chunks. But mostly, that kind of string
building went out with Perl & ISAPI.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/