DataGrid, DataAdapter, SP Parameters?

[James]

I have an sp with paramaters like this:
ALTER PROCEDURE dbo.SomeProc
(
@SortBy varchar(50) = NULL
@ShowComplete bit = 0
@StaffID int = NULL
)
AS
DECLARE @SQL varchar(2500)
SET @SQL =
'SELECT StaffID, Status FROM SomeTable '

IF @StaffID IS NOT NULL
SET @SQL = @SQL + ' WHERE StaffID = ' + @StaffID
ELSE
SET @SQL = @SQL + ' WHERE SQLLogin = SUSER_SNAME() '
IF @ShowComplete <> 1
SET @SQL = @SQL + ' AND StatusID NOT IN(3,4,5) '
IF @SortBy IS NOT NULL
SET @SQL = @SQL + ' ORDER BY ' + @SortBy

--PRINT @SQL
EXEC (@SQL)

I am trying to create a dataadapter (daTasks) bound to this sp, & then bind
my datagrid (dgdTasks) to it.

This works perfectly, until I include the parameters in the sp. The
parameters all have default values, but when I add these parameters to the
parameters collection of daTasks, I get a host of errors, such as 'Failed to
get schema for this stored procedure' & 'Input string was not in correct
format.'

For information, I can execute the sp from the IDE with or without
parameters, without a problem.

Any suggestions as to how I can solve this?


--
Cheers,

James Goodman

[Michael Tkachev]

Re: DataGrid, DataAdapter, SP Parameters?

Hi,

Don't use sqlstring. you have a lot of errors there.
The bellow you can find my solution, but you have to make a sort in the C#
or VB.

if @showcomlete = 1
begin
SELECT StaffID, Status FROM SomeTable
where StaffID = isnull(@StaffID , StaffID )
and SQLLogin =
case
when @StaffID is null then SUSER_SNAME()
else SQLLogin
end
end
else
begin
SELECT StaffID, Status FROM SomeTable
where StaffID = isnull(@StaffID , StaffID )
and SQLLogin =
case
when @StaffID is null then SUSER_SNAME()
else SQLLogin
end
end

bye

"James" <jamesATnorto n-associates.co.u kREMOVE> wrote in message
news:eo9T7PErEH A.1712@tk2msftn gp13.phx.gbl...[color=blue]
> I have an sp with paramaters like this:
> ALTER PROCEDURE dbo.SomeProc
> (
> @SortBy varchar(50) = NULL
> @ShowComplete bit = 0
> @StaffID int = NULL
> )
> AS
> DECLARE @SQL varchar(2500)
> SET @SQL =
> 'SELECT StaffID, Status FROM SomeTable '
>
> IF @StaffID IS NOT NULL
> SET @SQL = @SQL + ' WHERE StaffID = ' + @StaffID
> ELSE
> SET @SQL = @SQL + ' WHERE SQLLogin = SUSER_SNAME() '
> IF @ShowComplete <> 1
> SET @SQL = @SQL + ' AND StatusID NOT IN(3,4,5) '
> IF @SortBy IS NOT NULL
> SET @SQL = @SQL + ' ORDER BY ' + @SortBy
>
> --PRINT @SQL
> EXEC (@SQL)
>
> I am trying to create a dataadapter (daTasks) bound to this sp, & then[/color]
bind[color=blue]
> my datagrid (dgdTasks) to it.
>
> This works perfectly, until I include the parameters in the sp. The
> parameters all have default values, but when I add these parameters to the
> parameters collection of daTasks, I get a host of errors, such as 'Failed[/color]
to[color=blue]
> get schema for this stored procedure' & 'Input string was not in correct
> format.'
>
> For information, I can execute the sp from the IDE with or without
> parameters, without a problem.
>
> Any suggestions as to how I can solve this?
>
>
> --
> Cheers,
>
> James Goodman
>
>[/color]