Confusion regarding nested datagrid - classic problem of persistence

[strangeboy]

All:

Let me first say that I've researched my problem, but I have spent way
too much time (~1.5 weeks), and finally am reaching out to the
community for help. Here's the problem:

I successfully have a nested datagrid showing (typical master/detail
arrangement) using a dynamically created datagrid for the detail
information.

Listboxes outside of the datagrid have autopostback set to true. These
list boxes build off one another to give users options available for
searching a database based on what's actually in the database (to avoid
zero result searches). As you can probably guess, when a user fires the
autopostback from one of these listboxes, the detail datagrid vanishes.

Example at: http://www.aamprogram.org/resources/lessonplan_search3.aspx

Now, having done my homework, I know why this is happening and I've
read of a number of ways to make this work, but I feel like a lion
chasing a datagrid herd. There are a lot of tutorials on how to resolve
this issue, but many of the ones that I can get working make very
expensive calls to the database for every event (postback,
itemdatabound, etc.). Can you offer some advice on using either a
placeholder, user control, etc. for the detail datagrid? Is viewstate
the way to go? I can most likely get this to work if somebody provided
a best practices, step by step, or referred me to a great tutorial. I
don't mind rewriting my code (heck, I've done that 3-4 times now), and
I'll post my current code on request (but I didn't see the value as
it's not working).

Thanks in advance for any direction.

Best regards,

Michael

 

[strangeboy]

Added my code snippet. I've omitted the code that populates the
listboxes (just know they have autopostback set to "true"):

User makes choice(s) from the listboxes on the page, then clicks the
search button

'get the detail information from the database, create a dataset, name
the tables in the dataset

Sub loadDetailData()

Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL As String

objConn = New
SqlConnection(ConfigurationSettings.AppSettings.Get("connSQL"))

strSQL = "SELECT DISTINCT TOP 100 PERCENT
dbo.tblLessonPlan.id, dbo.tblLessonPlanSubjects.subject FROM
dbo.tblLessonPlan INNER JOIN dbo.tblLessonplanLessonToSubject ON
dbo.tblLessonPlan.id = dbo.tblLessonplanLessonToSubject.lessonID INNER
JOIN dbo.tblLessonPlanSubjects ON
dbo.tblLessonplanLessonToSubject.subjectID =
dbo.tblLessonPlanSubjects.subjectID ORDER BY
dbo.tblLessonPlanSubjects.subject; SELECT DISTINCT TOP 100 PERCENT
dbo.tblLessonPlan.id, dbo.tblLessonPlanGradeLevels.gradeLevel,
dbo.tblLessonPlanGradeLevels.gradeLevelID FROM dbo.tblLessonPlan INNER
JOIN dbo.tblLessonplanLessonToSubject ON dbo.tblLessonPlan.id =
dbo.tblLessonplanLessonToSubject.lessonID INNER JOIN
dbo.tblLessonplanSubjectToGrade ON
dbo.tblLessonplanLessonToSubject.lessonToSubjectID =
dbo.tblLessonplanSubjectToGrade.lessonToSubjectID INNER JOIN
dbo.tblLessonPlanGradeLevels ON
dbo.tblLessonplanSubjectToGrade.gradeLevelID =
dbo.tblLessonPlanGradeLevels.gradeLevelID ORDER BY
dbo.tblLessonPlanGradeLevels.gradeLevelID; SELECT DISTINCT TOP 100
PERCENT dbo.tblLessonPlan.id,
dbo.tblLessonplanCurriculumStandards.curriculumStandard FROM
dbo.tblLessonPlan INNER JOIN dbo.tblLessonplanLessonToSubject ON
dbo.tblLessonPlan.id = dbo.tblLessonplanLessonToSubject.lessonID INNER
JOIN dbo.tblLessonplanSubjectToGrade ON
dbo.tblLessonplanLessonToSubject.lessonToSubjectID =
dbo.tblLessonplanSubjectToGrade.lessonToSubjectID INNER JOIN
dbo.tblLessonplanGradeToCurriculum ON
dbo.tblLessonplanSubjectToGrade.subjectToGradeID =
dbo.tblLessonplanGradeToCurriculum.subjectToGradeID INNER JOIN
dbo.tblLessonplanCurriculumStandards ON
dbo.tblLessonplanGradeToCurriculum.curriculumID =
dbo.tblLessonplanCurriculumStandards.curriculumStandardID"

objCmd = New SqlCommand(strSQL, objConn)

Try

objConn.Open()


Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL,
objConn)
da.Fill(_dataset)

'map the tables to names
_dataset.Tables(0).TableName = "subjectDetails"
_dataset.Tables(1).TableName = "gradeLevelDetails"
_dataset.Tables(2).TableName = "curriculumDetails"

Catch ex As Exception

End Try
End Sub


Protected Sub datagrid1_OnItemDataBound(ByVal sender As Object,
ByVal e As DataGridItemEventArgs) Handles datagrid1.ItemDataBound

Try
If e.Item.ItemType = ListItemType.Item Or
e.Item.ItemType = ListItemType.AlternatingItem Then

bindSubjectDetails(e)

bindGradeDetails(e)

End If

Catch ex As Exception
lblError.Text = ex.Message

End Try

End Sub


Protected Sub bindSubjectDetails(ByVal e)
dtgSubjectDetails = New DataGrid

With dtgSubjectDetails
'.Width = Unit.Pixel(720)
.EnableViewState = True
.BorderWidth = Unit.Pixel(1)
.CellPadding = 2
.CellSpacing = 0
.GridLines = GridLines.Horizontal
.BorderColor = Color.FromName("Black")
.HeaderStyle.BackColor = Color.FromName("Black")
.HeaderStyle.ForeColor = Color.FromName("White")
.HeaderStyle.Font.Bold = True
.HeaderStyle.Font.Size = FontUnit.XSmall
.ItemStyle.Font.Name = "Verdana"
.ItemStyle.Font.Size = FontUnit.XSmall
.AlternatingItemStyle.BackColor =
Color.FromName("Gainsboro")
.AutoGenerateColumns = False
End With

Dim _boundColumn As BoundColumn = New BoundColumn

_boundColumn.HeaderText = "Subject(s)"
_boundColumn.DataField = "subject"
dtgSubjectDetails.Columns.Add(_boundColumn)

Dim rowID As String
rowID = CType(e.Item.FindControl("lblID"), Label).Text

Dim _dataView As DataView =
_dataset.Tables("subjectDetails").DefaultView

_dataView.RowFilter = "id='" & rowID & "'"

dtgSubjectDetails.DataSource = _dataView
dtgSubjectDetails.DataBind()

e.Item.Cells(3).Controls.Add(dtgSubjectDetails)
End Sub


Protected Sub bindGradeDetails(ByVal e)
dtgGradeDetails = New DataGrid

With dtgGradeDetails
'.Width = Unit.Pixel(720)
.EnableViewState = True

.BorderWidth = Unit.Pixel(1)
.CellPadding = 2
.CellSpacing = 0
.GridLines = GridLines.Horizontal
.BorderColor = Color.FromName("Black")
.HeaderStyle.BackColor = Color.FromName("Black")
.HeaderStyle.ForeColor = Color.FromName("White")
.HeaderStyle.Font.Bold = True
.HeaderStyle.Font.Size = FontUnit.XSmall
.ItemStyle.Font.Name = "Verdana"
.ItemStyle.Font.Size = FontUnit.XSmall
.AlternatingItemStyle.BackColor =
Color.FromName("Gainsboro")
.AutoGenerateColumns = False
End With

Dim _boundColumn2 As BoundColumn = New BoundColumn

_boundColumn2.HeaderText = "Grade Level"
_boundColumn2.DataField = "gradeLevel"
dtgGradeDetails.Columns.Add(_boundColumn2)

'Dim rowID As String

Dim rowID As String
rowID = CType(e.Item.FindControl("lblID"), Label).Text

Dim _dataView2 As DataView =
_dataset.Tables("gradeLevelDetails").DefaultView


_dataView2.RowFilter = "id='" & rowID & "'"

dtgGradeDetails.DataSource = _dataView2
dtgGradeDetails.DataBind()

e.Item.Cells(3).Controls.Add(dtgGradeDetails)
End Sub



Hopefully the included code will give somebody insight into my problem.
Thanks, again, in advance!!

-Michael

 

[Elton Wang]

Hi Strangeboy,

You can use SessionState, ApplicationState, or ViewState to store your data
source and improve performance. However, in some cases it makes thing worse.

HTH