Link to home
Start Free TrialLog in
Avatar of Nate_LR
Nate_LR

asked on

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>

Open in new window



<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

Open in new window

Avatar of Paul MacDonald
Paul MacDonald
Flag of United States of America image

What, specifically, is the problem?
Avatar of Nate_LR
Nate_LR

ASKER

It only returns "No Records Found".
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 "
ASKER CERTIFIED SOLUTION
Avatar of Nate_LR
Nate_LR

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nate_LR

ASKER

I figured it out on my own.