ASP.net paging with object data source won't work
I'm new to ASP.net and I simply can't figure out why the code below won't work.
<%@ Page Title="Core Search" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false"
CodeFile="CoreSearch.aspx.vb" Inherits="CoreSearch" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<form id="form1" runat="server">
<table>
<tr>
<td> Enter Search Criteria:
</td>
</tr>
<tr>
<td>
API #: <asp:TextBox ID="TextBoxAPI" runat="server"></asp:TextBox>
</td>
<td>
Permit #: <asp:TextBox ID="TextBoxPermit" runat="server"></asp:TextBox>
</td>
<td></td>
</tr>
<tr>
<td>
Well Name: <asp:TextBox ID="TextBoxName" runat="server"></asp:TextBox>
</td>
<td>
Operator: <asp:TextBox ID="TextBoxOperator" runat="server"></asp:TextBox>
</td>
<td>
Field: <asp:TextBox ID="TextBoxField" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
County: <asp:DropDownList ID="CountyDropDownList" runat="server"
DataSourceID="ObjectDataSourceCounty" DataTextField="CountyName"
DataValueField="CountyName" AppendDataBoundItems="True" EnableViewState="False">
<asp:ListItem Value="">-- Choose a county --</asp:ListItem>
</asp:DropDownList>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td>
Section: <asp:DropDownList ID="SectionDropDownList" runat="server"
DataSourceID="ObjectDataSourceSec" DataTextField="SECTION" DataValueField="SECTION" AppendDataBoundItems="True" EnableViewState="False">
<asp:ListItem Value="">-- Choose a section --</asp:ListItem>
</asp:DropDownList>
</td>
<td>
Township: <asp:DropDownList ID="TwnDropDownList" runat="server"
DataSourceID="ObjectDataSourceTwn" DataTextField="TWN" DataValueField="TWN" AppendDataBoundItems="True" EnableViewState="False">
<asp:ListItem Value="">-- Choose a township --</asp:ListItem>
</asp:DropDownList>
</td>
<td>
Range: <asp:DropDownList ID="RngDropDownList" runat="server"
DataSourceID="ObjectDataSourceRng" DataTextField="RNG" DataValueField="RNG" AppendDataBoundItems="True" EnableViewState="False">
<asp:ListItem Value="">-- Choose a range --</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td align="center" colspan="3">
<asp:Button ID="Button1"
runat="server" Text="Search" />
</td>
</tr>
</table>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1" BackColor="#DEBA84"
BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3"
CellSpacing="2" EmptyDataText="No Records Found">
<Columns>
<asp:BoundField DataField="WELL_NAME" HeaderText="Well Name" ReadOnly="true">
</asp:BoundField>
<asp:BoundField DataField="WELL_NUM" HeaderText="#" ReadOnly="true">
</asp:BoundField>
<asp:BoundField DataField="API" HeaderText="API" ReadOnly="true">
</asp:BoundField>
<asp:BoundField DataField="PERMIT_NUM" HeaderText="Permit" ReadOnly="true">
</asp:BoundField>
<asp:HyperLinkField DataNavigateUrlFields="WELL_LOCATIONID" DataNavigateUrlFormatString="CoreWellDetails.aspx?WellLocationID={0}"
Text="View Samples" >
<HeaderStyle ForeColor="#CC6600" />
<ItemStyle ForeColor="#CC6600" />
</asp:HyperLinkField>
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetCoreWellsByPage2" TypeName="CoreLibraryDB"
EnablePaging="true" StartRowIndexParameterName="startIndex" SelectCountMethod="SelectCount" MaximumRowsParameterName="maxRows">
<SelectParameters>
<asp:ControlParameter ControlID="TextBoxName" Name="wellname" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxOperator" Name="company" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxField" Name="field" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxAPI" Name="api" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxPermit" Name="permit" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="CountyDropDownList" Name="county" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TwnDropDownList" Name="twn" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="RngDropDownList" Name="rng" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="SectionDropDownList" Name="section" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSourceCounty" runat="server" SelectMethod="GetCounties" TypeName="CoreLibraryDB">
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSourceTwn" runat="server" SelectMethod="GetTownship" TypeName="CoreLibraryDB">
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSourceRng" runat="server" SelectMethod="GetRange" TypeName="CoreLibraryDB">
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSourceSec" runat="server" SelectMethod="GetSection" TypeName="CoreLibraryDB">
</asp:ObjectDataSource>
</form>
</asp:Content>
<DataObjectMethodAttribute(DataObjectMethodType.Select)>
Public Shared Function GetCoreWellsByPage2(ByVal startIndex As Integer, ByVal maxRows As Integer, ByVal wellname As String, ByVal company As String, ByVal field As String, ByVal api As String, ByVal permit As String, ByVal county As String, ByVal twn As String, ByVal rng As String, ByVal section As String) As List(Of CoreLibrary)
coreWellList = New List(Of CoreLibrary)
Dim con As New SqlConnection(GetConnectionString)
Dim sel As String =
"SELECT AGS_ID, WELL_NAME, WELL_NUM, API, PERMIT_NUM, OPERATOR_NAME, SUB_COMNAME, StatusType, WELL_LOCATIONID " &
"FROM CoreWH_wells " &
"WHERE ((WELL_NAME LIKE @Name) OR (@Name IS NULL)) " &
"AND ((OPERATOR_NAME LIKE @Company) OR (@Company IS NULL)) " &
"AND ((RES_FIELDNAME LIKE @Field) OR (@Field IS NULL)) " &
"AND ((API LIKE @API) OR (@API IS NULL)) " &
"AND ((PERMIT_NUM LIKE @Permit) OR (@Permit IS NULL)) " &
"AND ((CountyName = @County) OR (@County IS NULL)) " &
"AND ((TWN = @Township) OR (@Township IS NULL)) " &
"AND ((RNG = @Range) OR (@Range IS NULL)) " &
"AND ((SECTION = @Section) OR (@Section IS NULL))"
Dim cmd As New SqlCommand(sel, con)
If String.IsNullOrEmpty(wellname) Then
cmd.Parameters.AddWithValue("@Name", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Name", "%" & wellname & "%")
End If
If String.IsNullOrEmpty(company) Then
cmd.Parameters.AddWithValue("@Company", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Company", "%" & company & "%")
End If
If String.IsNullOrEmpty(field) Then
cmd.Parameters.AddWithValue("@Field", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Field", "%" & field & "%")
End If
If String.IsNullOrEmpty(api) Then
cmd.Parameters.AddWithValue("@API", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@API", "%" & api & "%")
End If
If String.IsNullOrEmpty(permit) Then
cmd.Parameters.AddWithValue("@Permit", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Permit", "%" & permit & "%")
End If
If String.IsNullOrEmpty(county) Then
cmd.Parameters.AddWithValue("@County", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@County", county)
End If
If String.IsNullOrEmpty(twn) Then
cmd.Parameters.AddWithValue("@Township", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Township", twn)
End If
If String.IsNullOrEmpty(rng) Then
cmd.Parameters.AddWithValue("@Range", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Range", rng)
End If
If String.IsNullOrEmpty(section) Then
cmd.Parameters.AddWithValue("@Section", DBNull.Value)
Else
cmd.Parameters.AddWithValue("@Section", section)
End If
con.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim well As CoreLibrary
Do While rdr.Read
well = New CoreLibrary
well.WellName = rdr("WELL_NAME").ToString
well.WellNum = rdr("WELL_NUM").ToString
well.API = rdr("API").ToString
well.Permit = rdr("PERMIT_NUM").ToString
well.Company = rdr("OPERATOR_NAME").ToString
well.Commodity = rdr("SUB_COMNAME").ToString
well.Status = rdr("StatusType").ToString
well.WellLocationID = CInt(rdr("WELL_LOCATIONID"))
well.AGS_ID = rdr("AGS_ID").ToString
coreWellList.Add(well)
Loop
rdr.Close()
Dim rowCount As Integer = coreWellList.Count
If startIndex + maxRows > rowCount Then
maxRows = rowCount - startIndex
End If
Dim pageList As New List(Of CoreLibrary)
Dim rowIndex As Integer
For i As Integer = 0 To maxRows - 1
rowIndex = i + startIndex
pageList.Add(coreWellList(rowIndex))
Next
Return pageList
End Function
Public Shared Function SelectCount() As Integer
Return coreWellList.Count
End Function
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you confirm the query, as written, returns records?
What if you change the query to...
" SELECT AGS_ID, WELL_NAME, WELL_NUM, API, PERMIT_NUM, OPERATOR_NAME, SUB_COMNAME, StatusType, WELL_LOCATIONID FROM CoreWH_wells "
What if you change the query to...
" SELECT AGS_ID, WELL_NAME, WELL_NUM, API, PERMIT_NUM, OPERATOR_NAME, SUB_COMNAME, StatusType, WELL_LOCATIONID FROM CoreWH_wells "
The query does return records after I simplified the code.
I figured out I needed to add AllowPaging="True" to
Then I needed to add the parameters to SelectCount
I figured out I needed to add AllowPaging="True" to
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="ProductID"
DataSourceID="ObjectDataSource1" AllowPaging="True" PageSize="8"
CellPadding="4" ForeColor="Black" GridLines="None">
Then I needed to add the parameters to SelectCount
Public Shared Function SelectCount(ByVal wellname As String, ByVal company As String, ByVal field As String, ByVal api As String, ByVal permit As String, ByVal county As String, ByVal twn As String, ByVal rng As String, ByVal section As String) As Integer
Return coreWellList.Count
End Function
Premium Content
You need an Expert Office subscription to comment.Start Free Trial