orangutangas February 2016

Show sql procedure error_id output in VisualStudio2008

I am Facing a problem when having a procedure and want to get error_id as my VS2008 does not work.
I have a procedure that returns some output and when there is error in given data, it returns error_id (or null is everything is ok) in second sql statement:

declare @p6 int 
set @p6=NULL 
exec Rec_EdStk_EdZB_Sub1_Sp N'2',N'1207',1,5,5,@p6 output 
select @p6 

After lauching that code i get these two windows: enter image description here

So what I need is to get that second sql result in my VS2008 project. I wrote some code:

        Dim dta As New DataTable
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader
        Dim quer As String = "declare @p6 int "
        quer += " set @p6=NULL "
        quer += " exec Rec_EdStk_EdZB_Sub1_Sp N'2',N'1207',1,5,5,@p6 output "
        quer += " select @p6 "

        Try
            con.ConnectionString = "server=TRAIN;database=DB;uid=test;pwd=test"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = quer
            reader = cmd.ExecuteReader()
            con.Close()
        Catch
            MsgBox("Connection error")
        End Try

When I try to pull data out from datable I get whatever I want from first result output.
For example if I write MsgBox(dta.Rows(0)(1)) I get value 1 from second column, first row.
But how can I get value from a declared parameter @p6 (in this case (null)) which output is given in another sql results table?
*********EDIT**********
If someone will suggests to use dataset, please provide an example, as i can't get error_id value using dataset:

Answers


Zohar Peled February 2016

This is because you are using a DataReader. Output parameters are only available after the DataReader is closed.
From Microsoft support:

CAUSE
Output parameters are returned at the end of the data stream when using a DataReader object.

RESOLUTION
When using a DataReader, you must close it or read to the end of the data before the output parameters are visible.

This is the reason I prefer to work with a dataset when I need output parameters.

Post Status

Asked in February 2016
Viewed 3,782 times
Voted 7
Answered 1 times

Search




Leave an answer