Problem with custom paging, maybe in my Stored Proc?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Problem with custom paging, maybe in my Stored Proc?

  1. #1
    Join Date
    Jul 2007
    Posts
    5

    Problem with custom paging, maybe in my Stored Proc?

    I have a working example of how to do custom paging. I can get the custom paging to work, I can get it working with sorting, but when I try to get it working with sorting and filtering I run into problems.

    I want to have a gridview control that displays a few of the record's fields. I want each row sortable, and I want to be able to filter it by state. I actually have three filters, but the user can only user one at a time, and the state is the simplest. If I can get that working I think I can get the others working as well.

    My objectdatasource looks like this:

    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetInmatesSubsetByStateSorted" TypeName="WAPTableAdapters.prisonmembersTableAdapter"
    EnablePaging="True" SelectCountMethod="GetInmatesByStateRowCount" SortParameterName="sortExpression">
    <SelectParameters>
    <asp:Parameter DefaultValue="NY" Name="State" Type="String" />
    </SelectParameters>
    </asp:ObjectDataSource>


    GetInmatesSubsetByStateSorted and GetInmatesByStateRowCount are datasets built on stored procedures:

    ALTER PROCEDURE GetInmatesSubsetByStateSorted
    (
    @State nvarchar(2),
    @sortExpression nvarchar(50),
    @startRowIndex int,
    @maximumRows int
    )
    AS
    If @State IS NULL
    EXEC dbo.GetInmatesSubsetSorted @SortExpression, @startRowIndex, @maximumRows
    ELSE

    BEGIN
    -- Otherwise we want to get just those employees in the specified department
    IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ID'

    -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment
    SET @startRowIndex = @startRowIndex + 1

    -- Issue query
    DECLARE @sql nvarchar(4000)
    SET @sql = 'SELECT ID, FILE2, [LAST NAME], [FIRST NAME], [DOC NUMBER], [CORR FACILITY], ADDRESS, CITY, STATE, Country, [Zip Codes], RACE, [RELIGION 1], [EARLIEST RELEASE DATE], [DATE OF BIRTH], SIGN, [EYE COLOR], [HAIR COLOR], [SEXUAL ORIENTATION], Overseas, [SEEKING LEGAL], [DEATH ROW], LIFER, DONATIONS, PHOTO, POETRY, ARTWORK, [HOME TOWN], [MARITAL STATUS], [LATEST RELEASE DATE], [INCARCERATED SINCE], [AD TYPE], [MALE OR FEMALE], [INCARCERATED FOR], [REFERRED BY], [AD STARTED], [AD ENDS], PageViews, COMMENTS, Ad, NewAd, ReNewAd, SeekEmploy, EmploySkills, LiveOnRelease, NeedsMail, NeedsMailDate, Suspend
    FROM
    (SELECT ID, FILE2, [LAST NAME], [FIRST NAME], [DOC NUMBER], [CORR FACILITY], ADDRESS, CITY, STATE, Country, [Zip Codes], RACE, [RELIGION 1], [EARLIEST RELEASE DATE], [DATE OF BIRTH], SIGN, [EYE COLOR], [HAIR COLOR], [SEXUAL ORIENTATION], Overseas, [SEEKING LEGAL], [DEATH ROW], LIFER, DONATIONS, PHOTO, POETRY, ARTWORK, [HOME TOWN], [MARITAL STATUS], [LATEST RELEASE DATE], [INCARCERATED SINCE], [AD TYPE], [MALE OR FEMALE], [INCARCERATED FOR], [REFERRED BY], [AD STARTED], [AD ENDS], PageViews, COMMENTS, Ad, NewAd, ReNewAd, SeekEmploy, EmploySkills, LiveOnRelease, NeedsMail, NeedsMailDate, Suspend,
    ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
    FROM prisonmembers
    WHERE State = @State
    ) as InmateInfo
    WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
    ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
    + CONVERT(nvarchar(10), @maximumRows) + ') - 1'

    -- Execute the SQL query
    EXEC sp_executesql @sql
    END

    and

    ALTER PROCEDURE dbo.GetInmatesByStateRowCount
    (
    @State nvarchar(2)
    )
    AS
    IF @State IS NULL
    EXEC GetInmatesRowCount
    ELSE
    SELECT COUNT(*)
    FROM prisonmembers
    WHERE State = @State
    RETURN

    I'm getting the following error:

    Must declare the scalar variable "@State".



    The source is code that generated thee error is in a file that was generated by Visual Studio. Is my problem in one of the the stored procedures? I don't see how it can be in the Dataset, I used the Designer.

    I can get rid of the error by removing the line in blue above in the stored procedure, but then no records at all are returned. I've been struggling with this for a couple of weeks, and am no closer to a solution, despite a working example!

    Diane

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    The error would indicate that you haven't defined a parameter (in the .NET code) named "@State".
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jul 2007
    Posts
    5
    Or it could be a scope problem. I do define the variable, which is why I'm asking for help.

    Diane

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    I see @State defined in your sp but not in your code. Could you post your .NET code which includes the parameter declarations?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Replies: 0
    Last Post: 01-28-2007, 11:34 PM
  2. Replies: 2
    Last Post: 08-25-2006, 02:28 PM
  3. calling Oracle Stored proc from VB
    By Suchintya in forum VB Classic
    Replies: 1
    Last Post: 05-02-2001, 01:50 PM
  4. Oracle Stored Proc parameter problem
    By Jon Trainer in forum VB Classic
    Replies: 2
    Last Post: 04-18-2001, 03:01 PM
  5. Replies: 0
    Last Post: 03-18-2000, 03:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center