I am having issues with the databind method of the gridview control I could use some help on. Some background... I have a search interface with a variety of textboxes (First Name, Last Name, Year, Gender etc.) The user will enter some data and press the search button. At which time I am dynamically generating the SQL for the datasource and binding it to the gridview control.
Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
' Check Program Year
If ddYear.SelectedValue = 0 Then
lblStatus.Text = "Please select a program year!"
Exit Sub
Else
lblStatus.Text = ""
End If
ds1.SelectCommand = "SELECT [StudentId], [ProgramYear], [LastName], [Middle], [FirstName], [HighSchoolCode], [Sex], [DateOfBirth] FROM [Student] WHERE [ProgramYear] = " & ddYear.SelectedValue & ""
Dim useCase As Integer = 0
If tbFname.Text <> "" Then useCase = 1
If tbLname.Text <> "" Then useCase = 2
If tbFname.Text <> "" And tbLname.Text <> "" Then useCase = 3
If tbFname.Text <> "" And tbM.Text <> "" Then useCase = 4
If tbID.Text <> "" Then useCase = 5
If tbHSCode.Text <> "" Then useCase = 6
If tbLname.Text <> "" And ddGender.SelectedValue <> "0" Then useCase = 7
If tbHSCode.Text <> "" And ddGender.SelectedValue <> "0" Then useCase = 8
Select Case useCase
Case 1 'First Name
ds1.SelectCommand += " and ([FirstName] LIKE '%' + '" & tbFname.Text & "' + '%')"
Case 2 'Last Name
ds1.SelectCommand += " and ([LastName] LIKE '%' + '" & tbLname.Text & "' + '%')"
Case 3 'First and Last Name
ds1.SelectCommand += " and ([FirstName] LIKE '%' + '" & tbFname.Text & "' + '%') and ([LastName] LIKE '%' + '" & tbLname.Text & "' + '%')"
Case 4 'First and Middle
ds1.SelectCommand += " and ([FirstName] LIKE '%' + '" & tbFname.Text & "' + '%') and ([Middle] = '" & tbM.Text & "')"
Case 5 'Student ID
ds1.SelectCommand += " and ([StudentId] = '" & tbID.Text & "')"
Case 6 'HS Code
ds1.SelectCommand += " and ([HighSchoolCode] = '" & tbHSCode.Text & "')"
Case 7 'Last Name and Sex
ds1.SelectCommand += " and ([Sex] = '" & ddGender.SelectedValue & "' and ([LastName] LIKE '%' + '" & tbLname.Text & "' + '%'))"
Case 8 'HS Code and Sex
ds1.SelectCommand += " and ([HighSchoolCode] = '" & tbHSCode.Text & "' and [Sex] = '" & ddGender.SelectedValue & "')"
Case Else
ds1.SelectCommand += " order by [LastName] desc"
End Select
ds1.DataBind()
GridView1.DataBind()
End Sub
This works well however when I enable paging and apply the OnPageIndexChanging method as below I run into trouble.
Protected Sub gridview1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
gridview1.PageIndex = e.NewPageIndex
GridView1.DataSource = ds1
gridview1.databind()
End Sub
What happens is that I click on the paging footer of the gridview to change pages and the gridview does not apply the new binding until I press the submit button again. I am not sure why this is happening any advice is appreciated.
Gridview:
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" DataKeyNames="StudentId" DataSourceID="ds1" AllowPaging="True" PageSize="15">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>.
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Student Id" InsertVisible="False" SortExpression="StudentId">
<ItemTemplate>
<a href="searchDetail.aspx?StudentID=<%# eval("StudentId") %>" >
<asp:Label ID="xyz" runat="server" Text='<%# Bind("StudentId") %>' /></a>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" />
<asp:BoundField DataField="Middle" HeaderText="Middle" SortExpression="Middle" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" />
<asp:BoundField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
<asp:BoundField DataField="HighSchoolCode" HeaderText="HS Code" SortExpression="HighSchoolCode" />
<asp:BoundField DataField="ProgramYear" HeaderText="Program Year" SortExpression="ProgramYear" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
DS1
<asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:NMERITEXString %>"></asp:SqlDataSource>
