Data Access Block - FillDataSet and Output Parameters

[Jonas]

Hi!

I'm using the Data Access v2 building block from MS for my data access
classes. I have a method which uses SqlHelper.FillDataSet to retrieve a
resultset from a stored procedure. Now I have added three output parameters
to return some extra info, but for some reason the values aren't returned.
If I test the stored procedure in Query Analyzer, I get the values back in
the right fashion, but when run in .NET nothing comes back.

My code looks like this:

Dim sqlParamModifyTime As SqlParameter = New
SqlParameter("@DocumentModifyTime", SqlDbType.DateTime)
Dim sqlParamIsReadLimited As SqlParameter = New
SqlParameter("@IsReadLimited", SqlDbType.Bit)
Dim sqlParamIsUpdateLimited As SqlParameter = New
SqlParameter("@IsUpdateLimited", SqlDbType.Bit)

sqlParamModifyTime.Direction = ParameterDirection.Output
sqlParamIsReadLimited.Direction = ParameterDirection.Output
sqlParamIsUpdateLimited.Direction = ParameterDirection.Output

' Create the sqlParams
Dim sqlParams As SqlParameter() = { _
New SqlParameter("@sessionPK_user",
SqlDbType.UniqueIdentifier), _
New SqlParameter("@FK_Document",
SqlDbType.UniqueIdentifier), _
sqlParamModifyTime, _
sqlParamIsReadLimited, _
sqlParamIsUpdateLimited}

' Set the values
sqlParams(0).Value = New SqlTypes.SqlGuid(SessionPK_user)
sqlParams(1).Value = New SqlTypes.SqlGuid(guidFK_Document)

Try
Dim ds As New DOC_DocumentsBE
ds.EnforceConstraints = False

SqlHelper.FillDataset(cn, "dbo.DOC_PatientsGet", ds, New
String() {"DOC_Patients"}, sqlParams)

' Get value of OUTPUT parameters
dtDocumentModifyTime = sqlParams(2).Value
blnIsReadLimited = sqlParams(3).Value
blnIsUpdateLimited = sqlParams(4).Value

Return (ds)

Finally
Trace.TraceEnd _
("cmcs", strClassName & ".Read",
SessionPK_user.ToString)
End Try

Any tips?

Brgds

Jonas

 

[WJ]

In your procedure, did you code (at the very end of the procedure):

return @DocumentModifyTime
return @IsReadLimited
return @IsUpdateLimited

John

 

[Jim Hughes]

Please show the content of the proc.

BTW, I don't think WJ's response about return @anyparamter won't work, I
believe return only returns an int.

You would need to use select @DocumentModifyTime=somecolumn in a select
statment or use set @DocumentModifyTime=somevalue before exiting the proc.

 

[Jonas]

The parameters are declared as ByRef, so they do not need to be "Return"ed.
I use the Return statement to get a dataset back to the calling method.

 

[Jonas]

Hi!

The SP works fine, I have tested it in the Query Analyzer tool from SQL
Server and it does return the output parameters correctly.
I also tried to set the direction to InputOutput and initialized the values,
but I still do not get anything back.

/Jonas

 

[WJ]

It works fine for me on my MS/SQL Developer 2000 against VS.Net 2K3. Here is
my tested procedure:

**************************************

/*
* Code this block into MS/SQL Server using SQL/Analyzer
*

--Step# 0
use Northwind
go

--Step# 1
drop procedure dbo.DOC_PatientsGet
go

--Step# 2
create procedure dbo.DOC_PatientsGet
@DocumentModifyTime DateTime out
,@IsReadLimited Bit out
,@IsUpdateLimited bit out as

set @DocumentModifyTime=getdate()
set @IsReadLimited=1 --true
set @IsUpdateLimited=0 --false

select top 3 EmployeeID
,TitleOfCourtesy as [Prefix]
,ltrim(rtrim(FirstName)+' '+rtrim(LastName))as
FullName
,Convert(varchar(10),BirthDate,101)as DOB
,Convert(varchar(10),HireDate,101)as DOH
from Employees
go

--Step# 3
--Test Section using SQL/Analyzer to ensure the Procedure is
working properly
declare @DocumentModifyTime DateTime
,@IsReadLimited Bit
,@IsUpdateLimited bit

set @DocumentModifyTime=null
set @IsReadLimited=0
set @IsUpdateLimited=0

exec dbo.DOC_PatientsGet
@DocumentModifyTime out
,@IsReadLimited out
,@IsUpdateLimited out

select @DocumentModifyTime as DT
,@IsReadLimited as OK1
,@IsUpdateLimited as OK2
go

--Step# 4
GRANT EXECUTE ON dbo.DOC_PatientsGet TO ASPNET

--Step# 5
Then finally, execute your class in VS.Net


**************************************

John

 

[Jonas]

I'm using WinXP as a development machine running against a SQL Server 2000
on Windows 2000 Server. I have the OUTPUT working in another method where I
run SqlHelper.ExecuteNonQuery, but in this method it fails when also
retrieving a dataset with SqlHelper.FillDataset.

Brgds

Jonas

 

[Jonas]

I found an article about this, and it does not mention the combination of
FillDataSet and OUTPUT parameters.

http://support.microsoft.com/kb/308049/EN-US/

Perhaps it isn't supported .... :-<

/Jonas

 

[WJ]

I'm using WinXP as a development machine running against a SQL Server 2000
on Windows 2000 Server. I have the OUTPUT working in another method where
I run SqlHelper.ExecuteNonQuery, but in this method it fails when also
retrieving a dataset with SqlHelper.FillDataset.

It does not matter. It works here for me! I got all three "output" fields
returned to my Asp.Net form using my sample Proc.

John