In April, I blogged about adding columns dynamically to a DataGrid. At the time, I was working on a report, so the dynamic columns didn't have to survive postback.
This week, I needed those columns to survive postback, so here's the updated codez:
<HeaderStyle CssClass=Freezing />
<AlternatingItemStyle CssClass=gAltRowsDark />
<Columns>
<asp:BoundColumn DataField=sys_cd_val HeaderText="ID"/>
<asp:BoundColumn DataField=sys_cd_desc HeaderText="Formulary Status" />
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
<asp:TemplateColumn><ItemTemplate><asp:Checkbox runat=server /></ItemTemplate></asp:TemplateColumn>
</Columns>
</asp:DataGrid>
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGrid()
End If
Dim csType As Type = Me.[GetType]()
ClientScript.RegisterClientScriptBlock(csType, "checkboxes", _
"<script language=javascript>" & _
" function check(ctl) {if(ctl.checked){ctl.style.background = '';updateCount(-1);} else {ctl.style.background = '#FF0000';updateCount(1);};}; " & _
" function uncheck(ctl) { if (ctl.checked) { ctl.style.background = '#00FF00'; updateCount(1); } else { ctl.style.background = ''; updateCount(-1);};}; " & _
" function updateCount(iVal) { btn = document.getElementById('" & btnSave.ClientID & "'); iCurr = parseInt(btn.value.replace(' Changes','').replace('Save ','').replace(' Change','')); iCurr = iCurr + parseInt(iVal); if (iCurr > 0) {btn.style.display='';if (iCurr == 1) {btn.value='Save 1 Change';} else {btn.value='Save ' + iCurr + ' Changes';}} else {btn.style.display='none';btn.value='0';};};" & _
"</script>")
End Sub
Private Sub BindGrid(Optional ByVal cn As OleDbConnection = Nothing)
Dim sSQL As String = ""
Dim iCol As Integer = 2
For Each prod In Common.GetDataTable("select * from mkt_prod_xrf where mkt_id=" & ddlMarket.SelectedValue & " order by prod_desc asc", cn).Rows
dgGoodBad.Columns(iCol).Visible = True
dgGoodBad.Columns(iCol).HeaderText = prod("prod_desc").ToString
dgGoodBad.Columns(iCol).HeaderStyle.BackColor = Drawing.ColorTranslator.FromHtml(prod("dsly_clr").ToString)
dgGoodBad.Columns(iCol).ItemStyle.BackColor = Drawing.ColorTranslator.FromHtml(prod("dsly_clr").ToString)
sSQL &= ", (SELECT yes_no_val FROM fact_table WHERE sys_cd_val=c.sys_cd_val AND prodgrp_id=" & prod("prod_id").ToString & ") v_" & prod("prod_id").ToString & " "
iCol += 1
Next
For iCol = iCol To dgGoodBad.Columns.Count - 1
dgGoodBad.Columns(iCol).Visible = False
Next
Common.BindToReader("SELECT dim_id, dim_desc " & sSQL & " FROM dimension_lkp c ORDER BY dim_desc", dgGoodBad)
If bDispose Then cn.Dispose()
btnSave.Style("display") = "none"
btnSave.Text = "0"
End Sub
Protected Sub dgGoodBad_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgGoodBad.ItemDataBound
Select e.Item.ItemType
Case ListItemType.Header
Dim sMarkets As String = ""
Dim dtOtherMarkets As New DataView(Common.GetDataTable("SELECT m.mkt_desc, mp.prodgrp_desc FROM mkt_lkp m, mkt_prod_xrf mp WHERE m.mkt_id=mp.mkt_id AND m.mkt_id != " & ddlMarket.SelectedValue), "", "mkt_desc", DataViewRowState.CurrentRows)
For i As Integer = 2 To dgGoodBad.Columns.Count - 1
If dgGoodBad.Columns(i).Visible Then
sMarkets = ""
dtOtherMarkets.RowFilter = "prod_desc=" & Common.DBString(dgGoodBad.Columns(i).HeaderText)
If dtOtherMarkets.Count > 0 Then
For iMkt As Integer = 0 To dtOtherMarkets.Count - 1
sMarkets &= dtOtherMarkets(iMkt)("mkt_desc").ToString & vbCrLf
Next
End If
If sMarkets > "" Then
e.Item.Cells(i).ForeColor = Drawing.Color.Red
e.Item.Cells(i).ToolTip = sMarkets
End If
Else
Exit For
End If
Next
Case ListItemType.Item, ListItemType.AlternatingItem
For i As Integer = 2 To dgGoodBad.Columns.Count - 1
If dgGoodBad.Columns(i).Visible Then
If e.Item.DataItem(i).ToString = "G" Then
DirectCast(e.Item.Cells(i).Controls(0), CheckBox).Checked = True
DirectCast(e.Item.Cells(i).Controls(0), CheckBox).Attributes.Add("onclick", "document.getElementById('" & hidDirty.ClientID & "').value += '/" & e.Item.DataItem("sys_cd_val") & "/';check(this);")
Else
DirectCast(e.Item.Cells(i).Controls(0), CheckBox).Attributes.Add("onclick", "document.getElementById('" & hidDirty.ClientID & "').value += '/" & e.Item.DataItem("sys_cd_val") & "/';uncheck(this);")
End If
Else
Exit For
End If
Next
End Select
End Sub
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim sSQL As String = "UPDATE fact_table SET yes_no_val='[value]' " & _
" WHERE prod_id=[prod_id] " & _
" AND dim_id=[dim_id] "
Dim cn As OleDbConnection = Nothing
Dim dvOld As New DataView(Common.GetDataTable("SELECT f.*, p.prod_desc FROM fact_tbl f, mkt_prod_xrf p WHERE f.prod_id=p.prod_id", cn), "", "", DataViewRowState.CurrentRows)
Dim sHolder As String = "begin" & vbCrLf
Dim val As String = ""
Common.MaybeOpenConnection(cn)
For Each itm As DataGridItem In dgGoodBad.Items
If itm.ItemType = ListItemType.Item Or ListItemType.AlternatingItem Then
If InStr(" " & hidDirty.Value, "/" & itm.Cells(0).Text & "/") > 0 Then
For iCol As Integer = 2 To dgGoodBad.Columns.Count - 1
If dgGoodBad.Columns(iCol).Visible Then
dvOld.RowFilter = "prod_desc=" & Common.DBString(dgGoodBad.Columns(iCol).HeaderText) & " AND dim_id=" & itm.Cells(0).Text
If dvOld.Count > 0 Then
val = IIf(DirectCast(itm.Cells(iCol).Controls(0), CheckBox).Checked, "G", "B")
If val <> dvOld(0)("yes_no_val").ToString Then
sHolder &= sSQL.Replace("[dim_id]", dvOld(0)("dim_id")).Replace("[prod_id]", dvOld(0)("prod_id")).Replace("[value]", val) & ";"
End If
Else
'Uh oh!
End If
Else
Exit For
End If
Next
End If
End If
Next
dvOld.Dispose()
hidDirty.Value = ""
Common.ExecuteNonQuery(sHolder & vbCrLf & "commit;" & vbCrLf & "end;", cn)
BindGrid(cn)
cn.Dispose()
End Sub
End Class
OK, that was a lot. Let’s go through what’s going on…
In BindGrid, for each product that exists in the database, I set a column to be visible, set up it’s HeaderText, etc. All of the later columns get hidden.
In ItemDataBound, I set the checked values (only for those that should be checked – the default is unchecked) and I decide which javascript is to be called: check() or uncheck(). They are very similar in that they both set the background color and increment/decrement the count of changes as displayed on the Save button. They are reversed to differentiate between checking and unchecking a box that was checked when the page was loaded vs one that was unchecked when the page was loaded.
In Javascript, I’m coloring the background of any changed checkboxes so the user knows what’s been updated. I also put the count of the number of changes into the text of btnSave to help them keep track. I also keep the user from clicking Save twice by re-hiding the button and replacing it with a Saving… message.
Some things I did to improve speed:
- You will see a number of places where I Exit For the column loop once I hit an unused/hidden column.
- hidDirty is a HiddenField that I added which is populated by javascript to contain the dimension IDs of any row that it updated. Then, when the Save button is processing, it can skip any dimension rows that were not updated, rather than checking every checkbox (I have 30 in my code)
- I added dvOld to compare the database’s value to what is currently in the grid. There’s no point in updating values that were not changed. This might not be all that important anymore because I also…
- Moved the updates to a single update at the end, with a single Commit. At one point, I was running each UPDATE statement by itself but there’s really no point. I might move the ExecuteNonQuery to inside the For each itm loop if I ever get a concern that the user might do too many updates at once and OleDB might not like it. :)
