Output Parameter Help!

[Ben Smith]

First off, I think there is a bug in the Data ApplicationBlocks when it comes to Output parameters. Even when I specify the direction the SQLHelper.ExecuteReader method still treats the parameter as an input parameter. But on to my questions...

I am having trouble returning a values back from a SQLCommand object using an output parameter. Where as the Application Blocks problem was creating the wrong command text, the SQLCommand is generating the right text but is not capturing the returned parameter value. Any ideas?

See my code below:

Dim pTotalChange As New SqlParameter("@intTotalChanges", SqlDbType.Int)
pTotalChange.Direction = ParameterDirection.Output

'SQLReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "uspGetChangeCount", pTotalChange)

conn.Open()
SQLCommand.Connection = conn
SQLCommand.CommandText = "uspGetChangeCount"
SQLCommand.CommandType = CommandType.StoredProcedure
SQLCommand.Parameters.Add(pTotalChange)
SQLReader = SQLCommand.ExecuteReader(CommandBehavior.CloseConnection)



Here is the generated SQL as seen through SQL Profiler:

declare @P1 int
set @P1=0
exec uspGetChangeCount @intTotalChanges = @P1 output
select @P1

 

[William \(Bill\) Vaughn]

Ok, let me start by saying that I don't use the application blocks. I see them as training wheels for a tricycle--they simply aren't needed and they tend to get in my way. In this case I simply setup the output parameters by hand. Nothing really mysterious here. The trick to getting to the OUTPUT paramters is to finish fetching the DataReader rowset. Fill does this for you. Once the rowset is populated, the OUTPUT parameters appear next in the TDS stream.

I'll be discussing this and other techniques in my ADO.NET (1.1 and 2.0) workshop in September (VSLive Orlando) and in Chicago in October.

hth
Dim WithEvents cn As SqlConnection

Dim cmd As SqlCommand

Dim da As SqlDataAdapter

Dim ds As New DataSet

Dim dr As SqlDataReader



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

cn = New SqlConnection("Data Source=betav7;integrated security=sspi;initial catalog=biblio")

cn.Open()

cmd = New SqlCommand("TestAllOutput", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

..Parameters.Add("@Return_Value", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

..Parameters.Add("@P1", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output

..Parameters.Add("@P2", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output

..Parameters.Add("@P3", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output

End With

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Do ' Process each resultset

ListBox1.Items.Add("Resultset")

Do While dr.Read

ListBox1.Items.Add("Data:" & dr.GetValue(0).ToString)

Loop

Loop While Not dr.IsClosed And dr.NextResult = True

ListBox1.Items.Add("Return Value: " & cmd.Parameters(0).ToString)

ListBox1.Items.Add("Output 1: " & cmd.Parameters(1).Value.ToString)

ListBox1.Items.Add("Output 2: " & cmd.Parameters(2).Value.ToString)

ListBox1.Items.Add("Output 3: " & cmd.Parameters(3).Value.ToString)

Catch exS As SqlException

MsgBox("SQLException:" & exS.Message & " Severity:" & exS.Class)

Catch ex As Exception

MsgBox("Infomessage:" & ex.ToString)

Finally

dr.Close()

End Try

End Sub

_____________________

Profiler Trace:

declare @P1 varchar(20)
set @P1='1947'
declare @P2 varchar(20)
set @P2='Betty Rubble'
declare @P3 varchar(20)
set @P3='16153'
exec TestAllOutput @P1 = @P1 output, @P2 = @P2 output, @P3 = @P3 output
select @P1, @P2, @P3




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

First off, I think there is a bug in the Data ApplicationBlocks when it comes to Output parameters. Even when I specify the direction the SQLHelper.ExecuteReader method still treats the parameter as an input parameter. But on to my questions...

I am having trouble returning a values back from a SQLCommand object using an output parameter. Where as the Application Blocks problem was creating the wrong command text, the SQLCommand is generating the right text but is not capturing the returned parameter value. Any ideas?

See my code below:

Dim pTotalChange As New SqlParameter("@intTotalChanges", SqlDbType.Int)
pTotalChange.Direction = ParameterDirection.Output

'SQLReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "uspGetChangeCount", pTotalChange)

conn.Open()
SQLCommand.Connection = conn
SQLCommand.CommandText = "uspGetChangeCount"
SQLCommand.CommandType = CommandType.StoredProcedure
SQLCommand.Parameters.Add(pTotalChange)
SQLReader = SQLCommand.ExecuteReader(CommandBehavior.CloseConnection)



Here is the generated SQL as seen through SQL Profiler:

declare @P1 int
set @P1=0
exec uspGetChangeCount @intTotalChanges = @P1 output
select @P1