troubleshooting Question

ASP.net paging with object data source won't work

Avatar of Nate_LR
Nate_LR asked on
ASP.NET
5 Comments1 Solution386 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Nate_LR

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros