DataGrid, DataAdapter, SP Parameters?

[jgoodman00]

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 MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[DaZZleD]

post the code where you add the parameters

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[jgoodman00]

I added the parameters through the IDE, so the code is part of the windows designer generated code...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[DaZZleD]

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

so is it working from ide or not?

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[jgoodman00]

Using the ServerExplorer in the IDE I can execute the sp as I expect it to work.

But I cannot configure the dataadapter because of the above errors...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures