I have a stored procedure running in SQL Server 2000 which returns a value
when I run it in SQL Server Management Studio 2008 (SMS). I have also tested
it in SQL Server 2000 Query Analyzer with the same result. However, when I
run it with VB.Net 2010 I get "nothing" as the value of the return parameter:
cmd.Parameters("@ReturnCount").Value.
How do I get a value back? The parameter I am testing on is @ReturnCount.
I will need it to work for the return code also - @ReturnCode.
I have searched the web and found quite a few examples and tried them, but
they do not work with this code.
(I have simplified the procedure for testing. I am using dynamic SQL
because the SELECT in the actual procedure uses a parameter on the FROM
clause. I use sp_executesql in order to get a return value from the
dynamic SQL.) -
/*
-
Test procedure:
-
-
declare @ReturnCount int
-
declare @ReturnCode int
-
exec dbo.spSampGroupPRptgS 'mltpsdb02',
-
'prodreporting',
-
'''100000015''',
-
@ReturnCode output
-
*/
-
-
if exists (select *
-
from dbo.sysobjects
-
where id = object_id(N'dbo.spSampGroupPRptgS') and
-
OBJECTPROPERTY(id, N'IsProcedure') = 1)
-
drop procedure dbo.spSampGroupPRptgS
-
GO
-
-
CREATE PROCEDURE dbo.spSampGroupPRptgS
-
( @PRptgDBServer varchar(30),
-
@PRptgDatabase varchar(30),
-
@GroupParentIDs varchar(4000),
-
@ReturnCode int output
-
)
-
AS
-
SELECT @ReturnCode = 0
-
DECLARE @SQL nvarchar(4000)
-
DECLARE @ReturnCount int
-
-
SELECT @SQL=
-
N'SELECT @ReturnCount = Count(*)
-
FROM GroupTestData
-
WHERE Parent_Group_Id in ('+@GroupParentIDs+')
-
'
-
-
DECLARE @params nvarchar(500)
-
-
SET @params =
-
N'@PRptgDBServer nvarchar(30),
-
@PRptgDatabase nvarchar(30),
-
@GroupParentIDs nvarchar(4000),
-
@ReturnCount int output'
-
-
EXEC sp_executesql @SQL,
-
@params,
-
@PRptgDBServer = @PRptgDBServer,
-
@PRptgDatabase = @PRptgDatabase,
-
@GroupParentIDs = @GroupParentIDs,
-
@ReturnCount = @ReturnCount OUTPUT
-
-
SELECT @ReturnCount
-
-
GO
-
-
----------------------------------------------------------
-
-
Public Function GetGroupParents(ByRef GroupParentsArg(,) As String) As Boolean
-
-
Dim cmd As New SqlCommand
-
Dim drGroupParents As SqlDataReader
-
Dim cnString As String = gConnectionString
-
Dim cn As New SqlConnection(cnString)
-
Dim MaxRowCount As Integer
-
Dim RowCount As Integer
-
-
GetGroupParents = True
-
-
Try
-
cmd.CommandTimeout = 1200
-
cmd.CommandText = "dbo.spSampGroupPRptgS"
-
cmd.CommandType = CommandType.StoredProcedure
-
-
cmd.Parameters.Add("@PRptgDBServer", SqlDbType.VarChar, 30).Value = gPRptgDBServer
-
cmd.Parameters.Add("@PRptgDatabase", SqlDbType.VarChar, 30).Value = gPRptgDatabase
-
cmd.Parameters.Add("@GroupParentIDs", SqlDbType.VarChar, 4000).Value = gGroupParentIDs
-
-
cmd.Parameters.Add("@ReturnCount", SqlDbType.Int)
-
cmd.Parameters("@ReturnCount").Direction = ParameterDirection.ReturnValue
-
cmd.Parameters.Add("@ReturnCode", SqlDbType.Int)
-
cmd.Parameters("@ReturnCode").Direction = ParameterDirection.Output
-
-
' Call spSampGroupPRptgS to get Parent Group Name, Product ID's, and Group ID's
-
cmd.Connection = cn
-
cmd.CommandType = CommandType.StoredProcedure
-
cn.Open()
-
drGroupParents = cmd.ExecuteReader
-
-
If drGroupParents.HasRows Then
-
-
MaxRowCount = CInt(cmd.Parameters("@ReturnCount").Value)
-
MessageBox.Show("MaxRowCount: " & MaxRowCount, Application.ProductName)
-
-
ReDim GroupParentsArg(MaxRowCount, 4)
-
-
RowCount = 0
-
While drGroupParents.Read()
-
GroupParentsArg(RowCount, 0) = Trim(drGroupParents.Item("ParentGroupId").ToString)
-
GroupParentsArg(RowCount, 1) = Trim(drGroupParents.Item("ParentGroupName").ToString)
-
GroupParentsArg(RowCount, 2) = Trim(drGroupParents.Item("ProductId").ToString)
-
GroupParentsArg(RowCount, 3) = Trim(IIf(Not IsDBNull(drGroupParents.Item("GroupId")), _
-
drGroupParents.Item("GroupId"), "").ToString)
-
RowCount += 1
-
End While
-
Else
-
If Not gSilentMode Then
-
MessageBox.Show("No Group ProdReporting Data Found", _
-
Application.ProductName & " No Group ProdReporting Data Found")
-
End If
-
InformationHandler("No Group ProdReporting Data Found", "")
-
GetGroupParents = False
-
Exit Function
-
End If
-
-
Catch ex As Exception
-
ErrorHandler("DataAccessModule.GetGroupParents", "", ex)
-
GetGroupParents = False
-
End Try
-
-
End Function
-
-
1 2798
If your SP is returning the result in query analyzer window and not in your front-end, then the problem is in your front-end. Go to .Net forum and post the portion where you access the returned result set of your SP.
Good Luck!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ¤ Alias |
last post by:
I have a function named
getID3info (lvwDiscInfo.SelectedItem).
What is the difference between
getID3info (lvwDiscInfo.SelectedItem)
and
Call getID3info(lvwDiscInfo.SelectedItem) ?
|
by: Luis |
last post by:
I'm using a SQL Server 2000 stored procedure similar to the one below
to upload data to a database. This data is collected from the user on
a number of asp pages and stored in session variables...
|
by: Don Leverton |
last post by:
Hi Folks,
I'm still in the process of rewriting my Parts Inventory application, and
still using good old Access97 to do it.
I'm attempting to modify Allen Browne's code from:...
|
by: maniac |
last post by:
Hey guys, I'm new here, just a simple question.
I'm learning to Program in C, and I was recommended a book called,
"Mastering C Pointers", just asking if any of you have read it,
and if it's...
|
by: Julio Allegue |
last post by:
I am getting the wrong Count(*) on vb.net using the ExecuteScalar . It
returns all the rows. It doesn't seem to look at the WHERE clause. At
the same time, I am getting the correct count on "SQL...
|
by: royaltiger |
last post by:
I am trying to copy the inventory database in Building Access Applications
by John L Viescas but when i try to run the database i get an error in the
orders form when i click on the allocate...
|
by: Cao Yi |
last post by:
Hi,
here's a fract of codes, and what's the line "scanf("%lf%*",
&cvi)" doing?
=============================
do {
printf("\nCoefficient: ");
scanf("%lf%*", &cvi);
getchar();
} while (cvi <=...
|
by: cfriedalek |
last post by:
I'm writing a python script using win32com to call a 3rd party program
via its VBS based API.
In VBS a query to get some plot data goes like this:
Plot.QueryBegin datacode, Nothing
What is...
|
by: Larry Bud |
last post by:
Using XML serializer, if an XML field is missing, then the value in
the object is Nothing.
Is there a slick way of converting a "Nothing" to an empty string
without doing is
if x is nothing...
|
by: raylopez99 |
last post by:
I ran afoul of this Compiler error CS1612 recently, when trying to
modify a Point, which I had made have a property. It's pointless to
do this (initially it will compile, but you'll run into...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |