This seems like it should be an easy thing to figure out, but I haven't found a solution to my problem. I wire up a PivotGrid to a DataTable (which I store in Session State between calls) using the NeedDataSource event handler. I have a search form on my page with 7 different parameters for the stored procedure that I call within that method.
What I am finding is that the search button works the first time (on page load), but clicking the search button doesn't cause the data to be recalled again. I would think that the rebinding of the pivot grid would be straighforward, and would cause the NeedDataSource event to fire again. Because of the size of the dataset (potentially 45,000 records) I need to store it in session between calls.
What am I missing here?
The search button only calls:
The NeedDataSource event looks like this:
What I am finding is that the search button works the first time (on page load), but clicking the search button doesn't cause the data to be recalled again. I would think that the rebinding of the pivot grid would be straighforward, and would cause the NeedDataSource event to fire again. Because of the size of the dataset (potentially 45,000 records) I need to store it in session between calls.
What am I missing here?
The search button only calls:
pvtCMStoG2.DataBind()
The NeedDataSource event looks like this:
Private Sub pvtCMStoG2_NeedDataSource(sender As Object, e As PivotGridNeedDataSourceEventArgs) Handles pvtCMStoG2.NeedDataSource
'Get an assign the data source.
pvtCMStoG2.DataSource = GetPivotGridData()
End Sub
Public Function GetPivotGridData() As DataTable
'Decalre local variables.
Dim myDataTable As New DataTable()
'Check to see if there is anything in Session for this.
If Session("ReconciliationTime") IsNot Nothing Then
'Retrieve from session.
myDataTable = CType(Session("ReconciliationTime"), DataTable)
Else
'Set up the connection.
Dim m_strConnectionString As [String] = ConfigurationManager.ConnectionStrings("SOREOConnection").ConnectionString
Dim m_objConnection As New SqlConnection(m_strConnectionString)
Dim m_objSQLCommand As New SqlCommand
Dim m_cmdParameter As SqlParameter
'Set the command properties.
With m_objSQLCommand
.Connection = m_objConnection
'Set the command timeout value to wait indefinitely.
.CommandTimeout = 0
'Set the core properties.
.CommandText = "[reconciliation].[cspCMStoG2TimesheetValidation]"
.CommandType = CommandType.StoredProcedure
End With
'Create and set the parameter values.
m_cmdParameter = New SqlParameter 'With {.ParameterName = "BeginDate", .SqlDbType = SqlDbType.DateTime}
With m_cmdParameter
.ParameterName = "@BeginDate"
.SqlDbType = SqlDbType.DateTime
'Set the value.
.Value = ctlBillingPeriodPicker.StartDate
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@EndDate"
.SqlDbType = SqlDbType.DateTime
'Set the value.
.Value = ctlBillingPeriodPicker.EndDate
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@EmployeeID"
.SqlDbType = SqlDbType.UniqueIdentifier
'Set the value.
If String.IsNullOrEmpty(ctlEmployeePicker.SelectedEmployeeID) Then
.Value = System.DBNull.Value
Else
.Value = ctlEmployeePicker.SelectedEmployeeID
End If
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@CustomerID"
.SqlDbType = SqlDbType.UniqueIdentifier
'Set the value.
If String.IsNullOrEmpty(ctlCustomerPicker.SelectedCustomerID) Then
.Value = System.DBNull.Value
Else
.Value = ctlCustomerPicker.SelectedCustomerID
End If
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@FundingAgency"
.SqlDbType = SqlDbType.VarChar
'Set the value.
If String.IsNullOrEmpty(ctlFundingOrganization.SelectedFundingOrganizationName) Then
.Value = System.DBNull.Value
Else
.Value = ctlFundingOrganization.SelectedFundingOrganizationName
End If
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@TimesheetID"
.SqlDbType = SqlDbType.Int
'Assign the value
If chkSingleTimesheetID.Checked = True Then
.Value = radTimesheetID.Value
Else
.Value = System.DBNull.Value
End If
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@IncludeLastImport"
.SqlDbType = SqlDbType.Bit
'Assign the value
.Value = chkIncludeLastImport.Checked
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Create and set the parameter values.
m_cmdParameter = New SqlParameter
With m_cmdParameter
.ParameterName = "@ErrorsOnly"
.SqlDbType = SqlDbType.Bit
'Assign the value
.Value = chkErrorsOnly.Checked
End With
'Add the parameter to the commands.
m_objSQLCommand.Parameters.Add(m_cmdParameter)
'Set up the DataAdapter
Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter
DataAdapter.SelectCommand = m_objSQLCommand
Try
'Open the connection.
m_objConnection.Open()
'Fill the data adapter.
DataAdapter.Fill(myDataTable)
Finally
'Close the connection.
m_objConnection.Close()
End Try
'Add it to the session.
Session("ReconciliationTime") = myDataTable
End If
'Return the final dataset.
Return myDataTable
End Function