Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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

0
Nate_LR
Asked:
Nate_LR
Advertise Here
  • 3
  • 2
1 Solution
 
Paul MacDonaldDirector, Information SystemsCommented:
What, specifically, is the problem?
0
 
Nate_LRAuthor Commented:
It only returns "No Records Found".
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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 "
0
 
Nate_LRAuthor Commented:
The query does return records after I simplified the code.

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">

Open in new window




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

Open in new window

0
 
Nate_LRAuthor Commented:
I figured it out on my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
  • 3
  • 2
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now