Problem with custom paging,

[mainship]

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