Bug of SqlClient?! Problem with output parameter/SQL Server 2000!

[Guest]

Here is the simple test stored procedure:
<code>
CREATE PROCEDURE pr_test
(
@xParam1 nvarchar(200) = null output
)
as
--set @xParam1 = newid()
select result = @xParam1
set @xParam1 = newid()
</code>

If I run the following test SQL script in Query Analyzer, I get expected
result:
declare @x nvarchar(500)

<code>
set @x = '1234'
exec pr_test @x output
select result = @x
</code>

<output>
result


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1234

(1 row(s) affected)

after



----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
92CBE528-6097-48C6-A0C4-C152425D180A

(1 row(s) affected)
</output>

However, in Visual Studio .NET 2002/2003, the following code does not get
expected result:
<code>
DataSet ds = new DataSet();
this.sqlDataAdapter1.SelectCommand.Parameters["@xParam1"].Value = "1234";
this.sqlDataAdapter1.Fill(ds);
this.dataGrid1.DataSource = ds.Tables[0];
this.Text =
this.sqlDataAdapter1.SelectCommand.Parameters["@xParam1"].Value.ToString();
</code>

The output paramter @xParam1 has been setup correctly. The datagrid always
shows the value of 'result' column as 'null'.

Do I have misunderstanding of ADO.NET/SqlClient or this is simply a bug?

 

[W.G. Ryan eMVP]

When you sey that the parameter has been setup correctly, you've explicity
set its direction to output is that correct?

 

[otabekhm]

data set returned sqlDataAdapter1 must be read fully and
sqlDataAdapter1 must be closed before you can extract output or return
value. this is a rule. Look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp
for details

Otabek

 

[Guest]

When you sey that the parameter has been setup correctly, you've explicity
set its direction to output is that correct?

Yes, sqlDataAdapter1 is created using the wizard. The code is correct.

 

[Guest]

Thanks for reply. But no, that's not the case.

I use sqlDataAdapter1.Fill, which has been always working for me and others.

The return value is correct, but the value of the only column of the
returned dataset is always 'null', which is not correct (should be "1234").