fetch data from output parameter of a store procedure

[c676228]

Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like this:
ALTER proc getProductCommS cale @product As varchar(30), @TISCommRate As
Decimal(5,2) OUTPUT,
@BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As Decimal(5,2) OUTPUT
As
if RTRIM(@product) ='Imed'
Select @TISCommRate=TI SComm, @BrokerCommRate =BrokerComm, @Fee=Fee
from tis_productComm Scale where ProductName='Im ed'
select @TISCommRate , @BrokerCommRate , @Fee
Return
an dthen I tested in the database with the following code:
Declare @TISCommRate As Decimal(5,2)
Declare @BrokerCommRate As Decimal(5,2)
Declare @Fee As Decimal(5,2)
exec getProductCommS cale 'Vusa',@TISComm Rate, @BrokerCommRate , @Fee
Then I got the following data:
..35 .20 5.00

But it seems not working in asp.net(1.1), here is part of my .net coding:
Dim sqlConnection As SqlConnection = New
SqlConnection(C onfigurationSet tings.AppSettin gs(Global.CfgKe yConnString))
Dim myCommand As SqlCommand = New
SqlCommand("Get ProductCommScal e", sqlConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure
Dim pProduct As SqlParameter = New SqlParameter("@ Product",
SqlDbType.VarCh ar, 30)
pProduct.Value = product
myCommand.Param eters.Add(pProd uct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@ TISCommRate", SqlDbType.Decim al)
pTisCommRate.Pr ecision = 5
pTisCommRate.Sc ale = 2
pTisCommRate.Di rection = ParameterDirect ion.Output
myCommand.Param eters.Add(pTisC ommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@ BrokerCommRate" , SqlDbType.Decim al)
pBrokerCommRate .Precision = 5
pBrokerCommRate .Scale = 2
pBrokerCommRate .Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pBrok erCommRate)

Dim pFee As SqlParameter = New SqlParameter("@ Fee",
SqlDbType.Decim al)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pFee)

sqlConnection.O pen()
Dim reader As SqlDataReader = myCommand.Execu teReader()

Dim TisCommRate As Decimal = Convert.ToDecim al(pTisCommRate .Value)

But somehow I always get nothing from pTisCommRate.Va lue, same to the other
parameters. What's going on? I am just learning to get data from output
parameters, did I miss anything?

--
Betty

[c676228]

Sorry, I use the wrong one: exec getProductCommS cale 'Vusa',@TISComm Rate,
@BrokerCommRate , @Fee
Acutally I mean exec getProductCommS cale 'Imed,@TISCommR ate,
@BrokerCommRate , @Fee

--
Betty
"c676228" wrote:

Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like this:
ALTER proc getProductCommS cale @product As varchar(30), @TISCommRate As
Decimal(5,2) OUTPUT,
@BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As Decimal(5,2) OUTPUT
As
if RTRIM(@product) ='Imed'
Select @TISCommRate=TI SComm, @BrokerCommRate =BrokerComm, @Fee=Fee
from tis_productComm Scale where ProductName='Im ed'
select @TISCommRate , @BrokerCommRate , @Fee
Return
an dthen I tested in the database with the following code:
Declare @TISCommRate As Decimal(5,2)
Declare @BrokerCommRate As Decimal(5,2)
Declare @Fee As Decimal(5,2)
exec getProductCommS cale 'Vusa',@TISComm Rate, @BrokerCommRate , @Fee
Then I got the following data:
.35 .20 5.00

But it seems not working in asp.net(1.1), here is part of my .net coding:
Dim sqlConnection As SqlConnection = New
SqlConnection(C onfigurationSet tings.AppSettin gs(Global.CfgKe yConnString))
Dim myCommand As SqlCommand = New
SqlCommand("Get ProductCommScal e", sqlConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure
Dim pProduct As SqlParameter = New SqlParameter("@ Product",
SqlDbType.VarCh ar, 30)
pProduct.Value = product
myCommand.Param eters.Add(pProd uct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@ TISCommRate", SqlDbType.Decim al)
pTisCommRate.Pr ecision = 5
pTisCommRate.Sc ale = 2
pTisCommRate.Di rection = ParameterDirect ion.Output
myCommand.Param eters.Add(pTisC ommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@ BrokerCommRate" , SqlDbType.Decim al)
pBrokerCommRate .Precision = 5
pBrokerCommRate .Scale = 2
pBrokerCommRate .Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pBrok erCommRate)

Dim pFee As SqlParameter = New SqlParameter("@ Fee",
SqlDbType.Decim al)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pFee)

sqlConnection.O pen()
Dim reader As SqlDataReader = myCommand.Execu teReader()

Dim TisCommRate As Decimal = Convert.ToDecim al(pTisCommRate .Value)

But somehow I always get nothing from pTisCommRate.Va lue, same to the other
parameters. What's going on? I am just learning to get data from output
parameters, did I miss anything?

--
Betty

[c676228]

Hi everyone,
I got it. It is because I added one more line code in the Store Procedure
which is

select ,@TISCommRate, @BrokerCommRate , @Fee
It is used for displaying data after the execution of the store procedure.
After I removed the line of code, the data in the my asp.net fine now.
Can you tell me why?

--
Betty
"c676228" wrote:

Sorry, I use the wrong one: exec getProductCommS cale 'Vusa',@TISComm Rate,
@BrokerCommRate , @Fee
Acutally I mean exec getProductCommS cale 'Imed,@TISCommR ate,
@BrokerCommRate , @Fee

--
Betty
"c676228" wrote:

Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like this:
ALTER proc getProductCommS cale @product As varchar(30), @TISCommRate As
Decimal(5,2) OUTPUT,
@BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As Decimal(5,2) OUTPUT
As
if RTRIM(@product) ='Imed'
Select @TISCommRate=TI SComm, @BrokerCommRate =BrokerComm, @Fee=Fee
from tis_productComm Scale where ProductName='Im ed'
select @TISCommRate , @BrokerCommRate , @Fee
Return
an dthen I tested in the database with the following code:
Declare @TISCommRate As Decimal(5,2)
Declare @BrokerCommRate As Decimal(5,2)
Declare @Fee As Decimal(5,2)
exec getProductCommS cale 'Vusa',@TISComm Rate, @BrokerCommRate , @Fee
Then I got the following data:
.35 .20 5.00

But it seems not working in asp.net(1.1), here is part of my .net coding:
Dim sqlConnection As SqlConnection = New
SqlConnection(C onfigurationSet tings.AppSettin gs(Global.CfgKe yConnString))
Dim myCommand As SqlCommand = New
SqlCommand("Get ProductCommScal e", sqlConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure
Dim pProduct As SqlParameter = New SqlParameter("@ Product",
SqlDbType.VarCh ar, 30)
pProduct.Value = product
myCommand.Param eters.Add(pProd uct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@ TISCommRate", SqlDbType.Decim al)
pTisCommRate.Pr ecision = 5
pTisCommRate.Sc ale = 2
pTisCommRate.Di rection = ParameterDirect ion.Output
myCommand.Param eters.Add(pTisC ommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@ BrokerCommRate" , SqlDbType.Decim al)
pBrokerCommRate .Precision = 5
pBrokerCommRate .Scale = 2
pBrokerCommRate .Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pBrok erCommRate)

Dim pFee As SqlParameter = New SqlParameter("@ Fee",
SqlDbType.Decim al)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pFee)

sqlConnection.O pen()
Dim reader As SqlDataReader = myCommand.Execu teReader()

Dim TisCommRate As Decimal = Convert.ToDecim al(pTisCommRate .Value)

But somehow I always get nothing from pTisCommRate.Va lue, same to the other
parameters. What's going on? I am just learning to get data from output
parameters, did I miss anything?

--
Betty

[c676228]

I got it, it is because of the extra line of code in the store procedure
select @TISCommRate, @BrokerCommRate , @Fee
for displaying execution result from the store procedure. After I removed
this line of code, it is fine in asp.net.
Can you tell me why?
--
Betty
"c676228" wrote:

Sorry, I use the wrong one: exec getProductCommS cale 'Vusa',@TISComm Rate,
@BrokerCommRate , @Fee
Acutally I mean exec getProductCommS cale 'Imed,@TISCommR ate,
@BrokerCommRate , @Fee

--
Betty
"c676228" wrote:

Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like this:
ALTER proc getProductCommS cale @product As varchar(30), @TISCommRate As
Decimal(5,2) OUTPUT,
@BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As Decimal(5,2) OUTPUT
As
if RTRIM(@product) ='Imed'
Select @TISCommRate=TI SComm, @BrokerCommRate =BrokerComm, @Fee=Fee
from tis_productComm Scale where ProductName='Im ed'
select @TISCommRate , @BrokerCommRate , @Fee
Return
an dthen I tested in the database with the following code:
Declare @TISCommRate As Decimal(5,2)
Declare @BrokerCommRate As Decimal(5,2)
Declare @Fee As Decimal(5,2)
exec getProductCommS cale 'Vusa',@TISComm Rate, @BrokerCommRate , @Fee
Then I got the following data:
.35 .20 5.00

But it seems not working in asp.net(1.1), here is part of my .net coding:
Dim sqlConnection As SqlConnection = New
SqlConnection(C onfigurationSet tings.AppSettin gs(Global.CfgKe yConnString))
Dim myCommand As SqlCommand = New
SqlCommand("Get ProductCommScal e", sqlConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure
Dim pProduct As SqlParameter = New SqlParameter("@ Product",
SqlDbType.VarCh ar, 30)
pProduct.Value = product
myCommand.Param eters.Add(pProd uct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@ TISCommRate", SqlDbType.Decim al)
pTisCommRate.Pr ecision = 5
pTisCommRate.Sc ale = 2
pTisCommRate.Di rection = ParameterDirect ion.Output
myCommand.Param eters.Add(pTisC ommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@ BrokerCommRate" , SqlDbType.Decim al)
pBrokerCommRate .Precision = 5
pBrokerCommRate .Scale = 2
pBrokerCommRate .Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pBrok erCommRate)

Dim pFee As SqlParameter = New SqlParameter("@ Fee",
SqlDbType.Decim al)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirect ion.Output
myCommand.Param eters.Add(pFee)

sqlConnection.O pen()
Dim reader As SqlDataReader = myCommand.Execu teReader()

Dim TisCommRate As Decimal = Convert.ToDecim al(pTisCommRate .Value)

But somehow I always get nothing from pTisCommRate.Va lue, same to the other
parameters. What's going on? I am just learning to get data from output
parameters, did I miss anything?

--
Betty

[Steven Cheng[MSFT]]

Hi Betty,

As for the store procedure output parameter behavior you encountered, it is
due to the following reason:

When you add the addition statement below in your SP:

select ,@TISCommRate, @BrokerCommRate , @Fee

after you executed the stored precedure, it will return three results:

1. three columes resulted by the above statement, you can change the
statement to

"select ,@TISCommRate as AA , @BrokerCommRate as BB , @Fee as CC" to
distinct with your output paramters

2. the output parameters defined in your SP( @TISCommRate,
@BrokerCommRate , @Fee)

3. the return value of your store procedure.

You can verify this by executing the SP in SQL Server management studio or
query analyzer.

When you execute the SP through .NET SqlCommand object, the returned
DataReader will also contains the three results, and the #1 one is in ahead
of the output parameters, therefore, you need to use the
DataReader.Next Result to go through the previous result and query the
parameters. e.g:

=============== ====
............... .

Dim reader As SqlDataReader
reader = comm.ExecuteRea der()

'get the first result

reader.NextResu lt()

For Each pm As SqlParameter In comm.Parameters

If pm.Direction = ParameterDirect ion.Output Then
Response.Write( "<br/>" & pm.ParameterNam e & ": " & pm.Value)
End If
Next

reader.Close()
conn.Close()
=============== =============== ===

another means to get the output parameter is access the Parameters
collection after you have closed the datareader, at that time, those output
parameter has been filled with the proper return values. e.g.

=============== ====
............... .

Dim reader As SqlDataReader
reader = comm.ExecuteRea der()

...........

reader.Close()

' access the output parameters here

=============== =============== ===

Hope this helps clarify it. If you have anything unclear, please feel free
to let me know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

=============== =============== =============== =====

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.

=============== =============== =============== =====

This posting is provided "AS IS" with no warranties, and confers no rights.

[c676228]

Steve,
thank you for the very clear clarification.
Nice to meet you here again.
--
Betty
"Steven Cheng[MSFT]" wrote:

Hi Betty,

As for the store procedure output parameter behavior you encountered, it is
due to the following reason:

When you add the addition statement below in your SP:

select ,@TISCommRate, @BrokerCommRate , @Fee

after you executed the stored precedure, it will return three results:

1. three columes resulted by the above statement, you can change the
statement to

"select ,@TISCommRate as AA , @BrokerCommRate as BB , @Fee as CC" to
distinct with your output paramters

2. the output parameters defined in your SP( @TISCommRate,
@BrokerCommRate , @Fee)

3. the return value of your store procedure.

You can verify this by executing the SP in SQL Server management studio or
query analyzer.

When you execute the SP through .NET SqlCommand object, the returned
DataReader will also contains the three results, and the #1 one is in ahead
of the output parameters, therefore, you need to use the
DataReader.Next Result to go through the previous result and query the
parameters. e.g:

=============== ====
............... .

Dim reader As SqlDataReader
reader = comm.ExecuteRea der()

'get the first result

reader.NextResu lt()

For Each pm As SqlParameter In comm.Parameters

If pm.Direction = ParameterDirect ion.Output Then
Response.Write( "<br/>" & pm.ParameterNam e & ": " & pm.Value)
End If
Next

reader.Close()
conn.Close()
=============== =============== ===

another means to get the output parameter is access the Parameters
collection after you have closed the datareader, at that time, those output
parameter has been filled with the proper return values. e.g.

=============== ====
............... .

Dim reader As SqlDataReader
reader = comm.ExecuteRea der()

...........

reader.Close()

' access the output parameters here

=============== =============== ===

Hope this helps clarify it. If you have anything unclear, please feel free
to let me know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

=============== =============== =============== =====

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.

=============== =============== =============== =====

This posting is provided "AS IS" with no warranties, and confers no rights.

[Steven Cheng[MSFT]]

Me too :-)

Have a nice day!

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.