Here is my code. As I said, it's pretty long. Basically it consists of a paginated Datagrid using template columns bound to data pulled back from the GetAllData() function. We store pagination info in our own session object (which we use across multiple applications.) We also use a DAL for data access.
Here is the codebehind. The aspx is is a separate post.
' ----------------------------------------------------------------------------------------------------------
' Function Name: Page_Load
' Description: Overridden local Page_Load sub. Takes the base Page_Load one step further and runs the
' specific processing for this page.
' ----------------------------------------------------------------------------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
lblMessage.Visible = False
If Not IsPostBack Then
'don't need to see the data controls yet
dgProvUpdate.Visible = False
tblNav.Visible = False
'for now store the key pagination variables in the ViewState
ViewState("PageNumber") = 1
ViewState("PageCount") = 0
ViewState("FirstOnPage") = -1
'the first time we load the page we'll order by date and get all data
ViewState("SortField") = "PROV_LAST ASC, PROV_FIRST ASC, PROV_MI"
ViewState("SortDirection") = "ASC"
ViewState("RequeryList") = True
'Get the pagination variables from ViewState
mblnRequeryList = ViewState("RequeryList")
mintPageNumber = ViewState("PageNumber")
mintNumPages = ViewState("PageCount")
mintFirstOnPage = ViewState("FirstOnPage")
mstrOrderBy = ViewState("SortField")
mstrOrderDir = ViewState("SortDirection")
mintNumItems = ViewState("ItemCount")
'here the user came here from the search provider page
If Len(Request.QueryString("ProvKey")) Then
Dim strProvKey As String, strSpecID As String
Dim intKeyPage As Integer
mstrCustomerID = Request.QueryString("customerid")
strProvKey = Request.QueryString("provkey")
strSpecID = Request.QueryString("specid")
ViewState("RequeryList") = False
'make sure the data controls are visible
dgProvUpdate.Visible = True
tblNav.Visible = True
'get the provider data
Call GetAllData(mstrCustomerID, strProvKey, intKeyPage)
Call BindGrid()
'Set the page no. to the page found in intKeyPage from GetAllData
ViewState("PageNumber") = intKeyPage
mintPageNumber = ViewState("PageNumber")
Call BindGrid()
Dim dgi As DataGridItem
Dim lblTemp As Label
Dim intRowID As Integer = 0
'Loop through the datagrid and find the provkey. When we do, mark it as editable.
For Each dgi In dgProvUpdate.Items
lblTemp = dgi.FindControl("NUM")
If lblTemp.Text = strProvKey Then
dgProvUpdate.EditItemIndex = intRowID
'Get the TaxID Type so we can use it to preselect the correct choice in the TaxIDType dropdown
ViewState("TaxIDType") = UCase(CType(dgi.FindControl("lblTaxIDType"), Label).Text)
ViewState("SpecID") = UCase(CType(dgi.FindControl("SPECID"), Label).Text)
're-bind the grid
If mintNumItems > 0 Then
Call BindGrid()
Call LoadJumpToCombo()
End If
Exit For
End If
intRowID += 1
Next
'Load the cust spec drop down and pre-select the current spec
Call LoadCustSpecCombo()
Dim i As Integer
For i = 0 To cboCustSpec.Items.Count - 1
If cboCustSpec.Items(i).Value = strSpecID Then
cboCustSpec.Items(i).Selected = True
Exit For
End If
Next
hfnAction.Value = "SetFocus_" & intRowID
End If
Else
mstrCustomerID = Request.Form("txtCustomerID")
ViewState("RequeryList") = False
'Get the pagination variables from ViewState
mblnRequeryList = ViewState("RequeryList")
mintPageNumber = ViewState("PageNumber")
mintNumPages = ViewState("PageCount")
mintFirstOnPage = ViewState("FirstOnPage")
mstrOrderBy = ViewState("SortField")
mstrOrderDir = ViewState("SortDirection")
mintNumItems = ViewState("ItemCount")
'user elected to do a group enroll/unenroll
If Request.Form("hfnAction") = "UpdateEnrollments" Then
Call UpdateEnrollments()
hfnAction.Value = ""
End If
'User elected to delete one or more providers
If Request.Form("hfnAction") = "DeleteProvider" Then
Call DeleteProvider()
hfnAction.Value = ""
End If
'Load cboCustSpec for this customer
If Request.Form("hfnAction") = "GetSpec" And Len(mstrCustomerID) Then
'make sure the data controls are invisible
dgProvUpdate.Visible = False
tblNav.Visible = False
Call LoadCustSpecCombo()
hfnAction.Value = "SpecFocus"
End If
End If
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: UpdateEnrollments
'Description:
'Returns: Nothing
'-----------------------------------------------------------------------------------------------------------
Protected Sub UpdateEnrollments()
Dim strSql As String, strEnrollKeyList As String = "", strUnEnrollKeyList As String = "", _
strReason1KeyList As String = "", strReason2KeyList As String = "", strReason3KeyList As String = "", _
strReason4KeyList As String = ""
Dim objDAL As Object
Dim dgiTemp As DataGridItem
Dim chkTemp As CheckBox
Dim cboTemp As DropDownList
Dim objRS As ADODB.Recordset
mstrCustomerID = Request.Form("txtCustomerID")
strEnrollKeyList = ""
strUnEnrollKeyList = ""
strSql = ""
'get the value of the ENROLLED checkbox
For Each dgiTemp In dgProvUpdate.Items
chkTemp = dgiTemp.FindControl("chkEnroll")
cboTemp = dgiTemp.FindControl("cboEnrolledReason")
'if it's checked, add it to the enroll list. Otherwise, add it to the unenroll list
If chkTemp.Checked = True Then
strEnrollKeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
Else
strUnEnrollKeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
End If
If chkTemp.Checked = True Then
strReason1KeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
Else
'if the item is not checked, get the value anyway
Select Case cboTemp.SelectedItem.Value
Case 1
strReason1KeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
Case 2
strReason2KeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
Case 3
strReason3KeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
Case 4
strReason4KeyList += "'" & CType(dgiTemp.FindControl("NUM"), Label).Text & "',"
End Select
End If
Next
'If the keylist exists, lop off the extra comma and create the SQL for it.
If strEnrollKeyList.Length > 0 Then
strEnrollKeyList = strEnrollKeyList.Remove(strEnrollKeyList.Length - 1, 1)
strSql += "Update Provider set Enrolled = 'Y' where PROVKEY in (" & strEnrollKeyList & ") and customerid = '" & mstrCustomerID & "';"
End If
If strUnEnrollKeyList.Length > 0 Then
strUnEnrollKeyList = strUnEnrollKeyList.Remove(strUnEnrollKeyList.Length - 1, 1)
strSql += "Update Provider set Enrolled = 'N' where PROVKEY in (" & strUnEnrollKeyList & ") and customerid = '" & mstrCustomerID & "';"
End If
If strReason1KeyList.Length > 0 Then
strReason1KeyList = strReason1KeyList.Remove(strReason1KeyList.Length - 1, 1)
strSql += "Update Provider set EnrolledReasonID = 1 where PROVKEY in (" & strReason1KeyList & ") and customerid = '" & mstrCustomerID & "';"
End If
If strReason2KeyList.Length > 0 Then
strReason2KeyList = strReason2KeyList.Remove(strReason2KeyList.Length - 1, 1)
strSql += "Update Provider set EnrolledReasonID = 2 where PROVKEY in (" & strReason2KeyList & ") and customerid = '" & mstrCustomerID & "';"
End If
If strReason3KeyList.Length > 0 Then
strReason3KeyList = strReason3KeyList.Remove(strReason3KeyList.Length - 1, 1)
strSql += "Update Provider set EnrolledReasonID = 3 where PROVKEY in (" & strReason3KeyList & ") and customerid = '" & mstrCustomerID & "';"
End If
If strReason4KeyList.Length > 0 Then
strReason4KeyList = strReason4KeyList.Remove(strReason4KeyList.Length - 1, 1)
strSql += "Update Provider set EnrolledReasonID = 4 where PROVKEY in (" & strReason4KeyList & ") and customerid = '" & mstrCustomerID & "';"
End If
'instantiate the DAL
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
Try
'run the query
objDAL.RundsnBSGSystemSQL(strSql)
Catch e As Exception
Throw New ApplicationException(e.ToString)
Finally
'release any objects
If Not objDAL Is Nothing Then
Runtime.InteropServices.Marshal.ReleaseComObject(objDAL)
End If
End Try
'requery the database and rebind the grid
Call GetAllData()
If mintNumItems > 0 Then
Call BindGrid()
End If
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: Form_Submit()
'Description:
'Returns: Nothing
'History:
' 04-25-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Protected Sub Form_Submit(ByVal sender As Object, ByVal e As EventArgs)
'if a provider was marked for updating and the user shifted gears, reset the edititemindex to -1
dgProvUpdate.EditItemIndex = -1
'reset the current pagenumber
ViewState("PageNumber") = 1
mintPageNumber = ViewState("PageNumber")
Call GetAllData()
If mintNumItems > 0 Then
Call BindGrid()
Call LoadJumpToCombo()
Else
lblMessage.Visible = True
lblMessage.Text = "No records found."
End If
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: GetAllData
'Description:
'Returns: Nothing
'History:
' 04-25-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Private Sub GetAllData(Optional ByVal strCustomerID As String = "", _
Optional ByVal strProvKey As String = "", _
Optional ByRef intKeyPage As Integer = 0)
Dim strSql As String, strWhereClause As String = ""
Dim objDAL As Object
Dim objRS As ADODB.Recordset
'if we didn't pass it in get the customerID from the Request object
If Len(strCustomerID) = 0 Then
mstrCustomerID = Request.Form("txtCustomerID")
End If
'Build the where clause, if any
If Len(Request.Form("txtPayerID")) Then
strWhereClause += " AND p.PayerID = '" & Request.Form("txtPayerID") & "'"
End If
If Len(Request.QueryString("specid")) Then
strWhereClause += " AND p.SpecID = '" & Request.QueryString("specid") & "'"
ElseIf Len(Request.Form("cboCustSpec")) And Request.Form("cboCustSpec") <> "ALL" Then
strWhereClause += " AND p.SpecID = '" & Request.Form("cboCustSpec") & "'"
End If
If Len(Request.Form("cboEnrolled")) Then
strWhereClause += " AND p.Enrolled = '" & Request.Form("cboEnrolled") & "'"
End If
'instantiate the DAL
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
'If ordering by last name, add first name and middle initial to the query
If mstrOrderBy = "PROV_LAST" Then
mstrOrderBy = "PROV_LAST " & mstrOrderDir & ", PROV_FIRST " & mstrOrderDir & ", PROV_MI"
End If
'if ordering by anything other than name, append name to the order by clause
If mstrOrderBy <> "PROV_LAST " & mstrOrderDir & ", PROV_FIRST " & mstrOrderDir & ", PROV_MI" And mstrOrderBy <> "PROV_LAST" Then
mstrOrderBy += " " & mstrOrderDir & ", PROV_LAST " & mstrOrderDir & ", PROV_FIRST " & mstrOrderDir & ", PROV_MI"
End If
Try
objRS = objDAL.GetProviderInfo(mstrCustomerID, mstrOrderBy, mstrOrderDir, strWhereClause)
'This section here is used to get the pagenumber of a provider to mark as editable if we
'came here from the Search Provider screen.
strProvKey = "{" & strProvKey & "}"
Do While Not objRS.EOF
If objRS.Fields("NUM").Value = strProvKey.ToUpper Then
Exit Do
End If
intKeyPage += 1
objRS.MoveNext()
Loop
'divide the index by the number of items per page to get the page no.
Dim dblTemp As Double
dblTemp = intKeyPage / RECORDSPERPAGE
'round down to the nearest whole number
intKeyPage = Math.Floor(dblTemp)
intKeyPage += 1
'get the number of items
mintNumItems = objRS.RecordCount
ViewState("ItemCount") = mintNumItems
'Save the pagination info
fobjState.CreatePaginationList(objRS)
'save the session
fobjState.SaveSession()
Catch ex As Exception
Throw New ApplicationException(ex.ToString)
Finally
If Not objDAL Is Nothing Then
'release the DAL
System.Runtime.InteropServices.Marshal.ReleaseComObject(objDAL)
End If
End Try
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: BindGrid()
'Description: Retrieves the current pages' recordset from the WUSMAP. The recordset is retrieved as an
' ADO recordset, then is converted to a dataset so it can be bound to the datagrid.
'Returns: Nothing
'History:
' 04-25-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Private Sub BindGrid()
Dim dsTemp As DataSet = New DataSet()
Dim odaTemp As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
Dim objRS As ADODB.Recordset
'first default all nav buttons to enabled
cmdFirst.Enabled = True
cmdNext.Enabled = True
cmdPrev.Enabled = True
cmdLast.Enabled = True
'make sure these are blank before we repopulate them
mstrJumpToOption = ""
mstrJumpToOptionText = ""
Try
'if we are sorted by a combination of first-middle-last, use only last name to build the drop down list
If mstrOrderBy = "PROV_LAST " & mstrOrderDir & ", PROV_FIRST " & mstrOrderDir & ", PROV_MI" Then
mstrOrderBy = "PROV_LAST"
End If
'if the sort field is a combo of a field and then first-middle-last, remove the first-middle-last
'string from the end of the sort field
Dim strRemoveText As String = " " & mstrOrderDir & ", PROV_LAST " & mstrOrderDir & ", PROV_FIRST " & mstrOrderDir & ", PROV_MI"
If mstrOrderBy.IndexOf(strRemoveText) > 0 Then
mstrOrderBy = mstrOrderBy.Remove(mstrOrderBy.IndexOf(strRemoveText), strRemoveText.Length)
End If
'get the pagination info from the WUSMAP
objRS = fobjState.GetPaginationRS(RECORDSPERPAGE, mintPageNumber, mintFirstOnPage, mintNumPages, mintNumItems, _
mstrJumpToOption, mstrJumpToOptionText, mstrItemList, mstrOrderBy)
'get the number of records on this page, only needed in case we delete all items on the last page to
'adjust the page number accordingly
mintItemsOnPage = objRS.RecordCount
'disable the nav button as appropriate
If mintPageNumber = mintNumPages Then
cmdLast.Enabled = False
cmdNext.Enabled = False
End If
If mintPageNumber = 1 Then
cmdFirst.Enabled = False
cmdPrev.Enabled = False
End If
'use the OLEDbDataAdapter to fill the ADO recordset from the ADO.NET dataset.
odaTemp.Fill(dsTemp, objRS, "Provdata")
'ensure the data controls are visible
tblNav.Visible = True
dgProvUpdate.Visible = True
'bind the grid
dgProvUpdate.DataSource = dsTemp
dgProvUpdate.DataBind()
Catch e As Exception
Throw New ApplicationException(e.ToString)
Finally
'release any objects
odaTemp.Dispose()
dsTemp.Dispose()
If Not objRS Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(objRS)
End If
End Try
'update the pagecount from WUSMAP
ViewState("PageCount") = mintNumPages
End Sub
' -----------------------------------------------------------------------------
' Function Name: TaxIDType()
' Description: Function used to retrieve the tax ID's. Called when the
' user edits a row in the datagrid and populates the cboTaxIDType
' with the available types. Tax ID types are returned in the form
' of an ADO.NET data table.
' Returns: ADO.NET DataTable
' -----------------------------------------------------------------------------
Public Function TaxIDType() As DataTable
Dim dtTemp As DataTable = New DataTable()
Dim dcTemp As DataColumn = New DataColumn()
Dim drTemp As DataRow
'Create and add the columns to the datatable
dcTemp.ColumnName = "TAX_ID_VAL"
dtTemp.Columns.Add(dcTemp)
dcTemp = New DataColumn()
dcTemp.ColumnName = "TAX_ID_NAME"
dtTemp.Columns.Add(dcTemp)
'create and add the rows to the datatable
drTemp = dtTemp.NewRow()
drTemp("TAX_ID_VAL") = ""
drTemp("TAX_ID_NAME") = ""
dtTemp.Rows.Add(drTemp)
drTemp = dtTemp.NewRow()
drTemp("TAX_ID_VAL") = "E"
drTemp("TAX_ID_NAME") = "EIN"
dtTemp.Rows.Add(drTemp)
drTemp = dtTemp.NewRow()
drTemp("TAX_ID_VAL") = "S"
drTemp("TAX_ID_NAME") = "SSN"
dtTemp.Rows.Add(drTemp)
'return the temporary table
Return dtTemp
End Function
' -----------------------------------------------------------------------------
' Function Name: GetReasonCode()
' Description: Function used to retrieve the enrolled/unenrolled reason codes.
' Called when the user edits a row in the datagrid and populates
' the cboReasonCode with the available types.
' Returns: ADO.NET DataTable
' -----------------------------------------------------------------------------
Public Function GetReasonCode() As DataTable
Dim strSql As String
Dim objDAL As Object
Dim objRS As ADODB.Recordset
'get the customerID from the Request object
mstrCustomerID = Request.Form("txtCustomerID")
'if it wasn't in the form, it may be in the querystring if we came from the search screen
If Len(mstrCustomerID) = 0 Then
mstrCustomerID = Request.QueryString("customerid")
End If
'build the sql
strSql = "Select * from EnrolledReason"
'instantiate the DAL
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
objRS = objDAL.RundsnBSGSystemSQLReturnRS(strSql)
'create the ADO.NET objects
Dim dt As DataTable = New DataTable()
Dim oda As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
'Fill the DataTable with the recordset data
oda.Fill(dt, objRS)
'return the DataTable
Return dt
End Function
' -----------------------------------------------------------------------------
' Function Name: GetSpec()
' Description: Function used to retrieve the specs.
' Called when the user edits a row in the datagrid and populates
' the cboReasonCode with the available types.
' Returns: ADO.NET DataTable
' -----------------------------------------------------------------------------
Public Function GetSpec() As DataTable
Dim strSql As String
Dim objDAL As Object
Dim objRS As ADODB.Recordset
objRS = GetSpecData()
'create the ADO.NET objects
Dim dt As DataTable = New DataTable()
Dim oda As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
'Fill the DataTable with the recordset data
oda.Fill(dt, objRS)
'return the DataTable
Return dt
End Function
' -----------------------------------------------------------------------------
' Function Name: GetSpecData
' Description: Sub Routine to run page specific logic
' -----------------------------------------------------------------------------
Protected Function GetSpecData() As ADODB.Recordset
Dim strSql As String = ""
Dim objDAL As Object
Dim objRS As ADODB.Recordset
'get the customerID from the Request object
mstrCustomerID = Request.Form("txtCustomerID")
'if it wasn't in the form, it may be in the querystring if we came from the search screen
If Len(mstrCustomerID) = 0 Then
mstrCustomerID = Request.QueryString("customerid")
End If
'build the sql statement
strSql = "Select c.specid as specid,s.specname as specname " & _
"from CUSTSPEC c INNER JOIN SpcIDRef s on c.specid = s.specid where customerid='" & mstrCustomerID & _
"' and s.specid not in (select unknownspecid from formtypeinfo) order by s.specname"
Try
'instantiate the DAL
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
objRS = objDAL.RundsnBSGSystemSQLReturnRS(strSql)
'if there were no records check in Local
If objRS.RecordCount = 0 Then
strSql = "Select c.specid,s.specname,s.spec_assign_id,s.spec_assign_id2,s.ProvTablePayerIDReq " & _
"from CUSTSPEC c INNER JOIN dsnBSGSystem.dbo.SpcIDRef s on c.specid = s.specid where customerid='" & mstrCustomerID & _
"' and s.specid not in (select unknownspecid from dsnBSGSystem.dbo.formtypeinfo) order by s.specname"
Runtime.InteropServices.Marshal.ReleaseComObject(objDAL)
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
'run the sql
objRS = objDAL.RundsnBSGSystemSQLReturnRS(strSql)
End If
'return the recordset
Return objRS
objRS.ActiveConnection = Nothing
Catch ex As Exception
Throw New ApplicationException(ex.ToString)
Finally
'release any objects
If Not objDAL Is Nothing Then
Runtime.InteropServices.Marshal.ReleaseComObject(objDAL)
End If
End Try
End Function
' -----------------------------------------------------------------------------
' Function Name: LoadCustSpecCombo()
' Description: Called to load the Cust Spec drop down list with LOBs
' -----------------------------------------------------------------------------
Protected Sub LoadCustSpecCombo()
Dim objRS As ADODB.Recordset
'call GetSpecData to get a recordset of LOBs
objRS = GetSpecData()
'clear out the drop down so we don't have any dupes
cboCustSpec.Items.Clear()
cboCustSpec.Items.Add(New ListItem("ALL", "ALL"))
'loop through the recordset and add items to the CustSpec drop down
Do While Not objRS.EOF
cboCustSpec.Items.Add(New ListItem(objRS.Fields("specname").Value, objRS.Fields("specid").Value))
objRS.MoveNext()
Loop
End Sub
' -----------------------------------------------------------------------------
' Function Name: PrePageDisplayLogic
' Description: Sub Routine to run page specific logic
' -----------------------------------------------------------------------------
Protected Overrides Sub PrePageDisplayLogic()
Try
'set the objects fields for the current page
fstrPageTitle = "Update Provider"
fstrPageName = "UpdateProvider"
fblnPrint = False
fblnShowMenu = True
'DNR. The base class will need these objects
flitHeadHeader = litHeadHeader
flitHeadFooter = litHeadFooter
Catch
RaisePageError(Err, "PageDisplayLogic")
End Try
End Sub
' -----------------------------------------------------------------------------
' Function Name: dgProvUpdate_EditCommand()
' Description: Event fired when an item in the datagrid is bound to it's
' datasource.
' -----------------------------------------------------------------------------
Private Sub dgProvUpdate_EditCommand(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Handles dgProvUpdate.EditCommand
'this tells the datagrid to render one row (the selected one) as editable fields
dgProvUpdate.EditItemIndex = e.Item.ItemIndex
'Get the TaxID Type so we can use it to preselect the correct choice in the TaxIDType dropdown
ViewState("TaxIDType") = UCase(CType(e.Item.FindControl("lblTaxIDType"), Label).Text)
ViewState("SpecID") = UCase(CType(e.Item.FindControl("SPECID"), Label).Text)
ViewState("EnrolledReason") = CType(e.Item.FindControl("cboEnrolledReason"), DropDownList).SelectedItem.Value
're-bind the grid
Call BindGrid()
hfnAction.Value = "SetFocus_" & e.Item.ItemIndex
End Sub
' -----------------------------------------------------------------------------
' Function Name: DeleteProvider()
' Description: Called when one or more items in the datagrid are marked for deletion.
' -----------------------------------------------------------------------------
Private Sub DeleteProvider()
Dim strSql As String, strProvKey As String, strDeleteList As String
Dim objDAL As Object
Dim rsDeleteList As ADODB.Recordset
Dim dgiTemp As DataGridItem
Dim chkTemp As CheckBox
'get the relevant provkey value
For Each dgiTemp In dgProvUpdate.Items
chkTemp = CType(dgiTemp.FindControl("chkDelete"), CheckBox)
If chkTemp.Checked = True Then
strProvKey += CType(dgiTemp.FindControl("NUM"), Label).Text & ","
End If
Next
'LOP off the last comma and insert the single quotes for the IN clause
strProvKey = strProvKey.Remove(strProvKey.Length - 1, 1)
strProvKey = strProvKey.Replace(",", "','")
Try
'instantiate the DAL
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
'get a recordset of prov_id's to be deleted
strSql = "SELECT 'Provider[' + PROV_ID + '];SpecID[' + Str(SPECID,3) + ']' from provider WHERE PROVKEY IN ('" & strProvKey & "')"
rsDeleteList = objDAL.RundsnBSGSystemSQLReturnRS(strSql)
'create a comma-delimited list of provider info
strDeleteList = rsDeleteList.GetString(ADODB.StringFormatEnum.adClipString, , " ", ",")
strDeleteList = Left(strDeleteList, Len(strDeleteList) - 1)
rsDeleteList.Close()
'delete the providers
strSql = "DELETE From Provider Where PROVKEY in ('" & strProvKey & "')"
objDAL.RundsnBSGSystemSQL(strSql)
'make an entry into tracklog
AddTrackLog(Request.Form("txtCustomerID"), " deleted " & strDeleteList & " for " & Request.Form("txtCustomerID") & ".", "UpdateProvider.aspx")
Catch ex As Exception
Throw New ApplicationException(ex.ToString)
Finally
If Not objDAL Is Nothing Then
'release the DAL
System.Runtime.InteropServices.Marshal.ReleaseComObject(objDAL)
End If
End Try
'requery the database
Call GetAllData()
If mintNumItems > 0 Then
'if there are items remaining, re-bind the datagrid
Call BindGrid()
Call LoadJumpToCombo()
Else
'if the last item has been deleted, notify the user and hide the datagrid and nav controls.
lblMessage.Visible = True
lblMessage.Text = "No records found."
dgProvUpdate.Visible = False
tblNav.Visible = False
End If
End Sub
' -----------------------------------------------------------------------------
' Function Name: dgProvUpdate_CancelCommand()
' Description: Event fired when an item in the datagrid is bound to it's
' datasource.
' -----------------------------------------------------------------------------
Private Sub dgProvUpdate_CancelCommand(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Handles dgProvUpdate.CancelCommand
'setting EditItemIndex = -1 sets all rows back to non editable
dgProvUpdate.EditItemIndex = -1
Call BindGrid()
End Sub
' -----------------------------------------------------------------------------
' Function Name: dgProvUpdate_UpdateCommand()
' Description: Event fired when an editable item in the datagrid is updated
' -----------------------------------------------------------------------------
Private Sub dgProvUpdate_UpdateCommand(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Handles dgProvUpdate.UpdateCommand
Dim strProvKey As String, strSql As String, strSpecID As String, strProvID As String, strProvGroupNo As String, _
strProvLast As String, strProvFirst As String, strProvMI As String, strProvName As String, strProvTaxID As String, _
strProvTaxIDType As String, strSpecAssignID As String, strSpecAssignID2 As String, strBatch As String, _
strPayerID As String, strEnrolled As String, strEnrolledReasonID As String
Dim objRS As ADODB.Recordset
Dim objDAL As Object
Try
'get all the values from the datagrid
strProvKey = Trim$(UCase(CType(e.Item.FindControl("NUM"), Label).Text))
strSpecID = Trim$(UCase(CType(e.Item.FindControl("cboSpec"), DropDownList).SelectedItem.Value))
strProvID = Trim$(UCase(CType(e.Item.FindControl("PROV_ID"), TextBox).Text))
strProvGroupNo = Trim$(UCase(CType(e.Item.FindControl("PROV_GROUPNO"), TextBox).Text))
strProvLast = Trim$(UCase(CType(e.Item.FindControl("PROV_LAST"), TextBox).Text))
strProvFirst = Trim$(UCase(CType(e.Item.FindControl("PROV_FIRST"), TextBox).Text))
strProvMI = Trim$(UCase(CType(e.Item.FindControl("PROV_MI"), TextBox).Text))
strProvName = Trim$(UCase(CType(e.Item.FindControl("SITENAME"), Label).Text))
strProvTaxID = Trim$(UCase(CType(e.Item.FindControl("PROV_TAXID"), TextBox).Text))
strProvTaxIDType = Trim$(UCase(CType(e.Item.FindControl("PROV_TAXID_TYPE"), DropDownList).SelectedItem.Value))
strSpecAssignID = Trim$(UCase(CType(e.Item.FindControl("SPEC_ASSIGN_ID"), TextBox).Text))
strSpecAssignID2 = Trim$(UCase(CType(e.Item.FindControl("SPEC_ASSIGN_ID2"), TextBox).Text))
strBatch = Trim$(UCase(CType(e.Item.FindControl("NSFBatching"), TextBox).Text))
strPayerID = Trim$(UCase(CType(e.Item.FindControl("PAYERID"), TextBox).Text))
If CType(e.Item.FindControl("chkEnroll"), CheckBox).Checked = True Then
strEnrolled = "Y"
Else
strEnrolled = "N"
End If
'if Enrolled is Yes then default the reason to "Enrolled"
If strEnrolled = "Y" Then
strEnrolledReasonID = "1"
Else
strEnrolledReasonID = Trim$(UCase(CType(e.Item.FindControl("cboEnrolledReason"), DropDownList).SelectedItem.Value))
End If
'build the sql, if fields are blank then set the value to NULL
strSql = "UPDATE Provider set "
If strSpecID.Length = 0 Then
strSql += "SPECID = NULL, "
Else
strSql += "SPECID = '" & strSpecID & "', "
End If
If strProvID.Length = 0 Then
strSql += "PROV_ID = NULL, "
Else
strSql += "PROV_ID = '" & strProvID & "', "
End If
If strProvGroupNo.Length = 0 Then
strSql += "PROV_GROUPNO = NULL, "
Else
strSql += "PROV_GROUPNO = '" & strProvGroupNo & "', "
End If
If strProvLast.Length = 0 Then
strSql += "PROV_LAST = NULL, "
Else
If strProvLast.IndexOf("'") > -1 Then
strProvLast = strProvLast.Replace("'", "''")
End If
strSql += "PROV_LAST = '" & strProvLast & "', "
End If
If strProvFirst.Length = 0 Then
strSql += "PROV_FIRST = NULL, "
Else
If strProvFirst.IndexOf("'") > -1 Then
strProvFirst = strProvFirst.Replace("'", "''")
End If
strSql += "PROV_FIRST = '" & strProvFirst & "', "
End If
If strProvMI.Length = 0 Then
strSql += "PROV_MI = NULL, "
Else
strSql += "PROV_MI = '" & strProvMI & "', "
End If
If strProvName.Length = 0 Then
strSql += "PROV_NAME = NULL, "
Else
If strProvName.IndexOf("'") > -1 Then
strProvName = strProvName.Replace("'", "''")
End If
strSql += "PROV_NAME = '" & strProvName & "', "
End If
If strProvTaxID.Length = 0 Then
strSql += "PROV_TAXID = NULL, "
Else
strSql += "PROV_TAXID = '" & strProvTaxID & "', "
End If
If strProvTaxIDType.Length = 0 Then
strSql += "PROV_TAXID_TYPE = NULL, "
Else
strSql += "PROV_TAXID_TYPE = '" & strProvTaxIDType & "', "
End If
If strSpecAssignID.Length = 0 Then
strSql += "SPEC_ASSIGN_ID = NULL, "
Else
strSql += "SPEC_ASSIGN_ID = '" & strSpecAssignID & "', "
End If
If strSpecAssignID2.Length = 0 Then
strSql += "SPEC_ASSIGN_ID2 = NULL, "
Else
strSql += "SPEC_ASSIGN_ID2 = '" & strSpecAssignID2 & "', "
End If
If strBatch.Length = 0 Then
strSql += "NSFBatching = NULL, "
Else
strSql += "NSFBatching = '" & strBatch & "', "
End If
If strPayerID.Length = 0 Then
strSql += "PAYERID = NULL, "
Else
strSql += "PAYERID = '" & strPayerID & "', "
End If
If strEnrolled.Length = 0 Then
strSql += "ENROLLED = NULL, "
Else
strSql += "ENROLLED = '" & strEnrolled & "', "
End If
If strEnrolledReasonID.Length = 0 Then
strSql += "EnrolledReasonID = NULL, "
Else
strSql += "EnrolledReasonID = '" & strEnrolledReasonID & "', "
End If
strSql = strSql.Remove(strSql.Length - 2, 1)
strSql += " WHERE PROVKEY = '" & strProvKey & "'"
objDAL = CreateObject("PayerpathDAL.dsnBSGSystem")
objDAL.rundsnBSGSystemSQL(strSql)
'make an entry into tracklog
AddTrackLog(Request.Form("txtCustomerID"), " updated provider [" & strProvFirst & " " & strProvMI & _
" " & strProvLast & ": " & strProvID & "] for " & Request.Form("txtCustomerID") & ".", "UpdateProvider.aspx")
'reset the rows to non-editable
dgProvUpdate.EditItemIndex = -1
Catch ex As Exception
Throw New ApplicationException(ex.ToString())
Finally
Runtime.InteropServices.Marshal.ReleaseComObject(objDAL)
End Try
'Re-query the database and re-bind the datagrid
Call GetAllData()
If mintNumItems > 0 Then
'if there are items remaining, re-bind the datagrid
Call BindGrid()
Call LoadJumpToCombo()
Else
'if the last item has been deleted, notify the user and hide the datagrid and nav controls.
lblMessage.Visible = True
lblMessage.Text = "No records found."
dgProvUpdate.Visible = False
tblNav.Visible = False
End If
End Sub
' -----------------------------------------------------------------------------
' Function Name: dgProvUpdate_ItemDataBound()
' Description: Event fired when an item in the datagrid is bound to it's
' datasource.
' -----------------------------------------------------------------------------
Private Sub dgProvUpdate_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs) Handles dgProvUpdate.ItemDataBound
Dim cboTemp As DropDownList
'When an item is edited (ListItemType.EditItem) preselect the value of the TaxIDType drop down
'based on the value of the PROV_TAXID_TYPE field (ViewState("TaxIDType"))
If e.Item.ItemType = ListItemType.EditItem Then
cboTemp = CType(e.Item.FindControl("PROV_TAXID_TYPE"), DropDownList)
cboTemp.SelectedIndex = cboTemp.Items.IndexOf(cboTemp.Items.FindByValue(ViewState("TaxIDType")))
cboTemp = CType(e.Item.FindControl("cboSPEC"), DropDownList)
cboTemp.SelectedIndex = cboTemp.Items.IndexOf(cboTemp.Items.FindByValue(ViewState("SpecID")))
cboTemp = CType(e.Item.FindControl("cboEnrolledReason"), DropDownList)
cboTemp.SelectedIndex = cboTemp.Items.IndexOf(cboTemp.Items.FindByValue(ViewState("EnrolledReason")))
End If
'when each row is drawn, find the checkbox. If the value of the ENROLLED field (lblEnrolled) is
'"Y", then check the checkbox
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.EditItem Then
Dim chkTemp As CheckBox
'get the value of the ENROLLED field indicated by lblEnrolled
ViewState("Enrolled") = UCase(CType(e.Item.FindControl("lblEnrolled"), Label).Text)
'get a reference to the current chkEnroll checkbox
chkTemp = CType(e.Item.FindControl("chkEnroll"), CheckBox)
'check the checkbox if appropriate
If ViewState("Enrolled") = "Y" Then
chkTemp.Checked = True
Else
chkTemp.Checked = False
End If
'Find the enrolled reason dropdown. Get the reason code (from lblReasonID) and preselect it in the dropdown.
ViewState("EnrolledReason") = CInt(UCase(CType(e.Item.FindControl("lblReasonID"), Label).Text))
'get a reference to the dropdown
cboTemp = CType(e.Item.FindControl("cboEnrolledReason"), DropDownList)
cboTemp.SelectedIndex = ViewState("EnrolledReason") - 1
End If
End Sub
' -----------------------------------------------------------------------------
' Function Name: dgProvUpdate_ItemCreated()
' Description: Event fired when a datagrid item is created
' -----------------------------------------------------------------------------
Public Sub dgProvUpdate_ItemCreated(ByVal sender As Object, ByVal e As DataGridItemEventArgs) Handles dgProvUpdate.ItemCreated
Dim strTemp As String
Dim tcTemp As TableCell = New TableCell()
Dim dgiTemp As DataGridItem
Dim lnkTemp As LinkButton
dgiTemp = e.Item
tcTemp = CType(dgiTemp.Controls(0), TableCell)
If e.Item.ItemType = ListItemType.EditItem Then
lnkTemp = tcTemp.Controls(0)
lnkTemp.Attributes.Add("onclick", "Javascript:if(ValidateReason(" & e.Item.ItemIndex & ")){return true;}else{return false;}")
End If
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: SortGrid()
'Description: Event handler for the DataGridOnSortCommand event. Determines the direction of the sort,
' sets the sort field, and then calls GetAllData, BindGrid, and LoadJumpToCombo to requery
' the database and refresh the grid and drop down.
'History:
' 02-06-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Protected Sub SortGrid(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgProvUpdate.SortCommand
mstrOrderBy = e.SortExpression
'toggle the sort order
If mstrOrderBy = ViewState("SortField") Then
If mstrOrderDir = "ASC" Then
mstrOrderDir = "DESC"
Else
mstrOrderDir = "ASC"
End If
Else
mstrOrderDir = "ASC"
End If
ViewState("SortField") = mstrOrderBy
ViewState("SortDirection") = mstrOrderDir
'requery the database, rebind the grid, and repopulate the jump to combo
Call GetAllData()
If mintNumItems > 0 Then
Call BindGrid()
Call LoadJumpToCombo()
End If
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: cmdNext_Click()
'Description: Handles the click event for the cmdNext linkbutton.
'History:
' 04-28-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNext.Click
'increment the page num by 1
ViewState("PageNumber") = mintPageNumber + 1
mintPageNumber = ViewState("PageNumber")
'if a provider was marked for updating and the user shifted gears, reset the edititemindex to -1
dgProvUpdate.EditItemIndex = -1
'rebind the grid
Call BindGrid()
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: cmdPrev_Click()
'Description: Handles the click event for the cmdPrev linkbutton.
'History:
' 04-28-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Private Sub cmdPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrev.Click
'decrement the page num by 1
ViewState("PageNumber") = mintPageNumber - 1
mintPageNumber = ViewState("PageNumber")
'if a provider was marked for updating and the user shifted gears, reset the edititemindex to -1
dgProvUpdate.EditItemIndex = -1
'rebind the grid
Call BindGrid()
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: cmdLast_Click()
'Description: Handles the click event for the cmdLast linkbutton.
'History:
' 04-28-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Private Sub cmdLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLast.Click
'we're going to the last page, so the pagenum should equal the page count
mintPageNumber = ViewState("PageCount")
ViewState("PageNumber") = mintPageNumber
'if a provider was marked for updating and the user shifted gears, reset the edititemindex to -1
dgProvUpdate.EditItemIndex = -1
'rebind the grid
Call BindGrid()
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: cmdFirst_Click()
'Description: Handles the click event for the cmdFirst linkbutton. Updates the page number and calls
' BindGrid to refresh the data.
'History:
' 04-28-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Private Sub cmdFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdFirst.Click
'set the page num = to the first page
mintPageNumber = 1
ViewState("PageNumber") = 1
'if a provider was marked for updating and the user shifted gears, reset the edititemindex to -1
dgProvUpdate.EditItemIndex = -1
'rebind the grid
Call BindGrid()
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: LoadJumpToCombo()
'Description:
'History:
' 04-28-03 JK - Created new
'-----------------------------------------------------------------------------------------------------------
Public Sub LoadJumpToCombo()
Dim intIndex As Integer, intIndex2 As Integer
cboPage.Items.Clear()
'populate the drop down
intIndex = 1
Try
Do While intIndex > 0
intIndex = mstrJumpToOption.IndexOfAny(",")
intIndex2 = mstrJumpToOptionText.IndexOfAny(",")
'parse the strings (comma-delimited) of names and values and add them
'ex. 200102031500,200102031501,200102031502
'if the intIndex < 0, i.e -1, then there is only one item. Add it, and since the value is
'-1, the loop will exit
If intIndex < 0 Then
cboPage.Items.Add(New ListItem(mstrJumpToOptionText, mstrJumpToOption))
'if the intIndex is > 0, then there are multiple items. Add the current item, and loop
'back around for more, until intIndex = -1
Else
cboPage.Items.Add(New ListItem(mstrJumpToOptionText.Substring(0, _
mstrJumpToOptionText.Length - (mstrJumpToOptionText.Length - intIndex2)), _
mstrJumpToOption.Substring(0, mstrJumpToOption.Length - (mstrJumpToOption.Length - intIndex))))
End If
'lop the item we just added to the drop down off of the end of the string
mstrJumpToOption = mstrJumpToOption.Remove(0, intIndex + 1)
mstrJumpToOptionText = mstrJumpToOptionText.Remove(0, intIndex2 + 1)
Loop
cboPage.Items(mintPageNumber - 1).Selected = True
'change the page's action to SpecChanged
hfnAction.Value = "SpecChanged"
Catch ex As Exception
Throw New ApplicationException(ex.ToString())
End Try
End Sub
'-----------------------------------------------------------------------------------------------------------
'Function Name: cboPage_SelectedIndexChanged()
'Description: Handles the SelectedIndexChanged event for the cboPage dropdownlist.
'History:
' 05-01-03 JK - Created New
'-----------------------------------------------------------------------------------------------------------
Sub cboPage_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboPage.SelectedIndexChanged
'set the pagenumber to the selected index of the jump to combo
ViewState("PageNumber") = cboPage.SelectedItem.Value
mintPageNumber = ViewState("PageNumber")
'rebind the grid
Call BindGrid()
'if a provider was marked for updating and the user shifted gears, reset the edititemindex to -1
dgProvUpdate.EditItemIndex = -1
End Sub
End Class
Hi,
it is hard to say without seeing your code. Post the relevant code.
--
Teemu Keiski
MCP, Designer/Developer
Mansoft tietotekniikka Oy
http://www.mansoft.fi
AspInsiders Member,
www.aspinsiders.com
ASP.NET Forums Moderator,
www.asp.net
AspAlliance Columnist,
www.aspalliance.com
I have a page that uses an in-place editable DataGrid that supports sorting and paging. EnableViewState is turned ON. At the top of the page are several search fields that allow the user to filter the results in the grid.
Say you filter the grid for records that have a certain condition set to "NO" (in this case a checkbox). In this scenario the search returns one result. If I then check the checkbox ("YES") and save it, I now get my message of "No records found" which is correct, because my filter is still looking for no. NOW, if I then resubmit the form, I get the following error:
System.Web.HttpException: Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request.
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Control.LoadViewStateRecursive(Object savedState)
at System.Web.UI.Page.LoadPageViewState()
at System.Web.UI.Page.ProcessRequestMain()
--- End of inner exception stack trace ---
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.Util.AspCompatApplicationStep.System.Web.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
The only way to not get this error is to reload the page completely.
What could be my problem??
Thanks in advance,
John Kirksey
