|
|
Posted by pbd22 on 04/18/07 16:10
On Apr 18, 8:08 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 18 Apr 2007 06:12:44 -0700, pbd22 wrote:
>
>
>
> >I have pasted the code below (sorry if i offend anybody about
> >formatting.
> >I am not sure how to format code here... suggestions?). When I use
> >Query Analyzer and step through the code in "debug" mode, the compiler
> >jumps to the following line:
>
> >----------------------------
> >SET ROWCOUNT 0
> >----------------------------
>
> >and throws this error:
>
> >---------------------------------------------------------------------------------------------
>
> >CurrentPage TotalPages TotalRows
> >----------- ----------- -----------
> >0 0 0
>
> >Server: Msg 507, Level 16, State 2, Procedure sp_SearchRowCount, Line
> >60
> >[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
> >SET ROWCOUNT. Must be a non-null non-negative integer.
>
> >@RETURN_VALUE = -6
> >---------------------------------------------------------------------------------------------
>
> >Could somebody tell me what about the logic in my code is causing this
> >failure (I know that the value is null and that that is causing the
> >error). I have a feeling it might have to do with the following lines
> >of code that I have commented out in the WHERE clause (but, I am not
> >sure what it is doing or how to add the logic to my own code):
>
> Hi pbd22,
>
> Since those commented lines are *AFTER* the place where the error occurs
> (the debugger jumps to the incorrect line; the only line that can cause
> this error is either this one:
>
> > SET ROWCOUNT @lbound
>
> or this one:
>
> > SET ROWCOUNT @perPage
>
> I assume that it's the first, and that it's cause by some unexpected
> things going on in the calculations neede to compute @lbound.
>
> You already have added a great statement to aid in debugging; I'm gonna
> ask you to add a bit more to it - so please change this:
>
> > SELECT
>
> > CurrentPage = @pagenum,
> > TotalPages = @pages,
> > TotalRows = @rows
>
> to this:
>
> SELECT
>
> CurrentPage = @pagenum,
> PageSize = @perpage,
> TotalPages = @pages,
> TotalRows = @rows,
> UpperBoundary = @ubound,
> LowerBoundary = @lbound
>
> And then rerun the query and post back the results. We'll then have to
> take it from there.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Hi Hugo,
Thanks for your reply. I have made the changes you suggested and got
the following output:
CurrentPage PageSize TotalPages TotalRows UpperBoundary
LowerBoundary
-----------........ ----------- ...... ----------- .....
----------- ........... ------------- .......-------------
0 ........ 10 .......... 0 .....
0 ............ 0 ....... -9
Server: Msg 507, Level 16, State 2, Procedure sp_PeopleSearch, Line 63
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
SET ROWCOUNT. Must be a non-null non-negative integer.
[Back to original message]
|