Solved

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

Posted on 2012-12-21
5
337 Views
Last Modified: 2012-12-26
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
Comment
Question by:Nate_LR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38714124
What, specifically, is the problem?
0
 

Author Comment

by:Nate_LR
ID: 38714164
It only returns "No Records Found".
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38714231
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
 

Accepted Solution

by:
Nate_LR earned 0 total points
ID: 38714560
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
 

Author Closing Comment

by:Nate_LR
ID: 38720945
I figured it out on my own.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question