473,324 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

fetch data from output parameter of a store procedure

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 getProductCommScale @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=TISComm, @BrokerCommRate=BrokerComm, @Fee=Fee
from tis_productCommScale where ProductName='Imed'
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 getProductCommScale 'Vusa',@TISCommRate, @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(ConfigurationSettings.AppSettings(Gl obal.CfgKeyConnString))
Dim myCommand As SqlCommand = New
SqlCommand("GetProductCommScale", sqlConnection)

myCommand.CommandType = CommandType.StoredProcedure
Dim pProduct As SqlParameter = New SqlParameter("@Product",
SqlDbType.VarChar, 30)
pProduct.Value = product
myCommand.Parameters.Add(pProduct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@TISCommRate", SqlDbType.Decimal)
pTisCommRate.Precision = 5
pTisCommRate.Scale = 2
pTisCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pTisCommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@BrokerCommRate", SqlDbType.Decimal)
pBrokerCommRate.Precision = 5
pBrokerCommRate.Scale = 2
pBrokerCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pBrokerCommRate)

Dim pFee As SqlParameter = New SqlParameter("@Fee",
SqlDbType.Decimal)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pFee)

sqlConnection.Open()
Dim reader As SqlDataReader = myCommand.ExecuteReader()

Dim TisCommRate As Decimal = Convert.ToDecimal(pTisCommRate.Value)

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

--
Betty
Aug 29 '06 #1
6 2701
Sorry, I use the wrong one: exec getProductCommScale 'Vusa',@TISCommRate,
@BrokerCommRate, @Fee
Acutally I mean exec getProductCommScale 'Imed,@TISCommRate,
@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 getProductCommScale @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=TISComm, @BrokerCommRate=BrokerComm, @Fee=Fee
from tis_productCommScale where ProductName='Imed'
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 getProductCommScale 'Vusa',@TISCommRate, @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(ConfigurationSettings.AppSettings(Gl obal.CfgKeyConnString))
Dim myCommand As SqlCommand = New
SqlCommand("GetProductCommScale", sqlConnection)

myCommand.CommandType = CommandType.StoredProcedure
Dim pProduct As SqlParameter = New SqlParameter("@Product",
SqlDbType.VarChar, 30)
pProduct.Value = product
myCommand.Parameters.Add(pProduct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@TISCommRate", SqlDbType.Decimal)
pTisCommRate.Precision = 5
pTisCommRate.Scale = 2
pTisCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pTisCommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@BrokerCommRate", SqlDbType.Decimal)
pBrokerCommRate.Precision = 5
pBrokerCommRate.Scale = 2
pBrokerCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pBrokerCommRate)

Dim pFee As SqlParameter = New SqlParameter("@Fee",
SqlDbType.Decimal)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pFee)

sqlConnection.Open()
Dim reader As SqlDataReader = myCommand.ExecuteReader()

Dim TisCommRate As Decimal = Convert.ToDecimal(pTisCommRate.Value)

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

--
Betty
Aug 29 '06 #2
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 getProductCommScale 'Vusa',@TISCommRate,
@BrokerCommRate, @Fee
Acutally I mean exec getProductCommScale 'Imed,@TISCommRate,
@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 getProductCommScale @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=TISComm, @BrokerCommRate=BrokerComm, @Fee=Fee
from tis_productCommScale where ProductName='Imed'
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 getProductCommScale 'Vusa',@TISCommRate, @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(ConfigurationSettings.AppSettings(Gl obal.CfgKeyConnString))
Dim myCommand As SqlCommand = New
SqlCommand("GetProductCommScale", sqlConnection)

myCommand.CommandType = CommandType.StoredProcedure
Dim pProduct As SqlParameter = New SqlParameter("@Product",
SqlDbType.VarChar, 30)
pProduct.Value = product
myCommand.Parameters.Add(pProduct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@TISCommRate", SqlDbType.Decimal)
pTisCommRate.Precision = 5
pTisCommRate.Scale = 2
pTisCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pTisCommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@BrokerCommRate", SqlDbType.Decimal)
pBrokerCommRate.Precision = 5
pBrokerCommRate.Scale = 2
pBrokerCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pBrokerCommRate)

Dim pFee As SqlParameter = New SqlParameter("@Fee",
SqlDbType.Decimal)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pFee)

sqlConnection.Open()
Dim reader As SqlDataReader = myCommand.ExecuteReader()

Dim TisCommRate As Decimal = Convert.ToDecimal(pTisCommRate.Value)

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

--
Betty
Aug 29 '06 #3
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 getProductCommScale 'Vusa',@TISCommRate,
@BrokerCommRate, @Fee
Acutally I mean exec getProductCommScale 'Imed,@TISCommRate,
@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 getProductCommScale @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=TISComm, @BrokerCommRate=BrokerComm, @Fee=Fee
from tis_productCommScale where ProductName='Imed'
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 getProductCommScale 'Vusa',@TISCommRate, @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(ConfigurationSettings.AppSettings(Gl obal.CfgKeyConnString))
Dim myCommand As SqlCommand = New
SqlCommand("GetProductCommScale", sqlConnection)

myCommand.CommandType = CommandType.StoredProcedure
Dim pProduct As SqlParameter = New SqlParameter("@Product",
SqlDbType.VarChar, 30)
pProduct.Value = product
myCommand.Parameters.Add(pProduct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@TISCommRate", SqlDbType.Decimal)
pTisCommRate.Precision = 5
pTisCommRate.Scale = 2
pTisCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pTisCommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@BrokerCommRate", SqlDbType.Decimal)
pBrokerCommRate.Precision = 5
pBrokerCommRate.Scale = 2
pBrokerCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pBrokerCommRate)

Dim pFee As SqlParameter = New SqlParameter("@Fee",
SqlDbType.Decimal)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pFee)

sqlConnection.Open()
Dim reader As SqlDataReader = myCommand.ExecuteReader()

Dim TisCommRate As Decimal = Convert.ToDecimal(pTisCommRate.Value)

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

--
Betty
Aug 29 '06 #4
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.NextResult to go through the previous result and query the
parameters. e.g:

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

Dim reader As SqlDataReader
reader = comm.ExecuteReader()

'get the first result

reader.NextResult()

For Each pm As SqlParameter In comm.Parameters

If pm.Direction = ParameterDirection.Output Then
Response.Write("<br/>" & pm.ParameterName & ": " & 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.ExecuteReader()

...........

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.

Aug 30 '06 #5
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.NextResult to go through the previous result and query the
parameters. e.g:

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

Dim reader As SqlDataReader
reader = comm.ExecuteReader()

'get the first result

reader.NextResult()

For Each pm As SqlParameter In comm.Parameters

If pm.Direction = ParameterDirection.Output Then
Response.Write("<br/>" & pm.ParameterName & ": " & 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.ExecuteReader()

...........

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.

Aug 30 '06 #6
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.

Aug 31 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Jarrod Morrison | last post by:
Hi All Im generally a vb programmer and am used to referencing multiple records returned from a query performed on an sql database and im trying to move some functions of my software into sql...
0
by: sedefo | last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i was researching how i can write a database independent data access layer. In my company we already use Data Access...
2
by: serge | last post by:
My project is to automate testing of Stored Procedures of type SELECT (at least for now). I want to create a table where each stored procedure's input parameter values are entered and in another...
6
by: Rod Snyder | last post by:
I'm trying to set up an asp.net (vb.net) that will allow a user to insert/update a bio/profile. I wanted to create a SQL Server 2000 table that includes their contact info and bio info. However,...
1
by: Agnes | last post by:
I try to set an output parameter to get the result from store procedure, However,Do I still need to loop the reader to get result ?? Thanks a lot cmdKey.Parameters("@result").Direction =...
4
by: Mr Not So Know It All | last post by:
im new to SQL Server and ASP.Net. Here's my problem. I have this SQL Server stored procedure with an input parameter and output parameter CREATE PROCEDURE . @in_rc varchar(8) @out_eList...
11
by: c676228 | last post by:
Hi everyone, I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store...
1
by: sheenaa | last post by:
Hello Members, I m creating my application forms in ASP.Net 2005 C# using the backend SQL Server 2005. What i have used on forms :: ? On my first form i have used some...
4
amitpatel66
by: amitpatel66 | last post by:
In real time applications, there is always a requirement to generate the data selected from database in XML format. using Oracle, generating XML Structured data becomes easier using the inbuilt...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.