Datagrid, Paging and Viewstate Disabled

[tsp1lrk]

hello!

I'm in a catch 22 here- I found some good information to reduce the size of the datagrid by disabling viewstate- no prob. When I use paging and searching together, I have problems. If I do a search on page 1 for something, it's fine. If I go to say page '6' and search again, I get that infamous: Invalid CurrentPageIndex value. It must be >= 0 and < the PageCount.

Sooo... I took out the if not postback and it worked, then I lost the paging functionality. It would go from 1-10, fine; then 11-20... when I click the ... it goes back to 1-10, so each method is overwriting the other and I've been searching for an answer to no avail. Nothing seems to show a solution for paging with viewstate disabled. Any ideas? HELP! Here's my code-behind:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Viewstate("pageIndex") = 0
End If
DataGrid1.DataBind()
BindGrid()
End Sub

Private Sub BindGrid()
Dim cnn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet

cnn = New SqlConnection(ConfigurationSettings.AppSettings().Item("ConnectionString"))
da = New SqlDataAdapter("select Appropriation, PAR, ProjectContact, ProjectName , 'ViewInfo.aspx?APPID=' +Appropriation AS URL from tblAppropriation", cnn)
da.Fill(ds, "tblAppropriation")
DataGrid1.DataSource = ds.Tables("tblAppropriation").DefaultView
DataGrid1.CurrentPageIndex = CInt(Viewstate("pageIndex"))
DataGrid1.DataSource = ds
DataGrid1.DataMember = "tblAppropriation"
DataGrid1.DataBind()
End Sub

Sub Datagrid1_PageIndexChanged(ByVal s As Object, ByVal e As DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
Viewstate("pageIndex") = e.NewPageIndex
BindGrid()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim cnn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet
cnn = New SqlConnection(ConfigurationSettings.AppSettings().Item("ConnectionString"))
da = New SqlDataAdapter("GetAppropInfo1", cnn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.
da.SelectCommand.Parameters.Add(New SqlParameter("@SearchVal", SqlDbType.VarChar, 100))

'Assign the search value to the parameter.
da.SelectCommand.Parameters("@SearchVal").Value = Trim(txtPO.Text)

'Create and add an output parameter to Parameters collection.
da.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", SqlDbType.Int, 4))

'Set the direction for the parameter. This parameter returns the Rows returned.
da.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

ds = New DataSet 'Create a new DataSet to hold the records.
da.Fill(ds, "tblAppropriation") 'Fill the DataSet with the rows returned.

'Get the number of rows returned, and then assign it to the Label control.
'lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
Label2.Text = da.SelectCommand.Parameters(1).Value & " Rows Found!"

'Set the data source for the DataGrid as the DataSet that holds the rows.
DataGrid1.DataSource = ds.Tables("tblAppropriation").DefaultView
DataGrid1.DataBind()

'Bind the DataSet to the DataGrid.
'NOTE: If you do not call this method, the DataGrid is not displayed!

da.Dispose() 'Dispose of the DataAdapter.
cnn.Close() 'Close the connection.
End Sub

Private Sub btnLogout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
FormsAuthentication.SignOut()
Response.Redirect("login.aspx")
End Sub
End Class

Thanks for your continued assistance! Much appreciated!
Lisa
UPS

 

[adamroof]

i think your page is overkill. Will this work?

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
		ViewState("search") = ""
		BindGrid()
	End If
End Sub

Private Sub BindGrid()
	Dim cnn As SqlConnection
        Dim da As SqlDataAdapter
        Dim ds As New DataSet

        cnn = New SqlConnection(ConfigurationSettings.AppSettings().Item("ConnectionString"))
	If ViewState("search") <> "" Then
	        da = New SqlDataAdapter("GetAppropInfo1", cnn)
        	da.SelectCommand.CommandType = CommandType.StoredProcedure
	        da.SelectCommand.Parameters.Add(New SqlParameter("@SearchVal", SqlDbType.VarChar, 100))
        	da.SelectCommand.Parameters("@SearchVal").Value = ViewState("search")
	        da.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", SqlDbType.Int, 4))
        	da.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

	        ds = New DataSet 
        	da.Fill(ds, "tblAppropriation") 'Fill the DataSet with the rows returned.

	        DataGrid1.DataSource = ds.Tables("tblAppropriation").DefaultView
			If ds.Tables(0).Rows.Count < 1 Then
        			Label2.text = "Could Not Load Datagrid."
        			Exit Sub
			Else
				Label2.Text = da.SelectCommand.Parameters(1).Value & " Rows Found!"
        		End If
        	DataGrid1.DataBind()

	        da.Dispose() 'Dispose of the DataAdapter.
        	cnn.Close() 'Close the connection.
	Else
        	da = New SqlDataAdapter("select Appropriation, PAR, ProjectContact, ProjectName , 'ViewInfo.aspx?APPID=' +Appropriation AS URL from tblAppropriation", cnn)
        	da.Fill(ds, "tblAppropriation")
        	DataGrid1.DataSource = ds.Tables("tblAppropriation").DefaultView
        	DataGrid1.DataSource = ds
	        	If ds.Tables(0).Rows.Count < 1 Then
        			Label2.text = "Could Not Load Datagrid."
        		Exit Sub
        		End If
        	DataGrid1.DataMember = "tblAppropriation"
        	DataGrid1.DataBind()
	End If
	ViewState("search") = ""
End Sub

    Sub Datagrid1_PageIndexChanged(ByVal s As Object, ByVal e As DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        BindGrid()
    End Sub
    
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
	ViewState("search") = Trim(txtPO.Text)
	BindGrid()
    End Sub

If trim gives you error, do Imports Microsoft.VisualBasic

 

[adamroof]

oh yeah,
turn EnableViewState back on...

<aspataGrid id=dataGrid1 runat=server EnableViewState=True>

or

DataGrid1.EnableViewState = True

or omit the line all together as True is the default for datagrids.