Hi Guys,
Im attempting to get an output value from my stored procedure. Im
pulling my hair out at this one. any help appreciated.
my code is as follows:
Dim cnstring As String
Dim iConn As New SqlClient.SqlConnection(strRegistry)
Dim iDataAdapter As New SqlClient.SqlDataAdapter
Dim iDataSet As New DataSet
Dim err As String
Dim cmd As New SqlCommand
Try
iConn.Open()
cmd.Connection = iConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = SQLStr
Dim myParam1 As SqlParameter =
cmd.Parameters.Add("@Threshhold", SqlDbType.VarChar, 50)
myParam1.Direction = ParameterDirection.Input
myParam1.Value = String.Empty
Dim myParam2 As SqlParameter =
cmd.Parameters.Add("@Return", SqlDbType.VarChar, 50)
myParam2.Direction = ParameterDirection.Output
iDataAdapter = New SqlClient.SqlDataAdapter
iDataAdapter = New SqlDataAdapter(cmd)
cmd.ExecuteNonQuery()
iDataAdapter.Fill(iDataSet, "tblRoleReturned")
Dim s As String = cmd.Parameters("@Return").Value
SQLDSReturn = iDataSet
**********STORED PROCEDURE***************************************** ****
CREATE PROCEDURE OADsp_SelectUnassignedReferrals
@Return varchar(50) output,
@Threshhold int
as
Set @Threshhold = 60
SELECT * FROM tbl_Referrals
WHERE (AssignedStaffID IS NULL AND TimeAssigned IS NULL)
OR (ReferralStaffID IS NULL AND
DateDiff(SECOND,(TimeAssigned),(GETDATE())) @Threshhold)
IF @@ERROR>0
BEGIN
RETURN 99
END
ELSE
BEGIN
RETURN 1
END
GO 1 3235
You're not trying to get the output parameter, prior to running through your
result set generated by the select statement, are you?
"csgraham74" wrote:
Hi Guys,
Im attempting to get an output value from my stored procedure. Im
pulling my hair out at this one. any help appreciated.
my code is as follows:
Dim cnstring As String
Dim iConn As New SqlClient.SqlConnection(strRegistry)
Dim iDataAdapter As New SqlClient.SqlDataAdapter
Dim iDataSet As New DataSet
Dim err As String
Dim cmd As New SqlCommand
Try
iConn.Open()
cmd.Connection = iConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = SQLStr
Dim myParam1 As SqlParameter =
cmd.Parameters.Add("@Threshhold", SqlDbType.VarChar, 50)
myParam1.Direction = ParameterDirection.Input
myParam1.Value = String.Empty
Dim myParam2 As SqlParameter =
cmd.Parameters.Add("@Return", SqlDbType.VarChar, 50)
myParam2.Direction = ParameterDirection.Output
iDataAdapter = New SqlClient.SqlDataAdapter
iDataAdapter = New SqlDataAdapter(cmd)
cmd.ExecuteNonQuery()
iDataAdapter.Fill(iDataSet, "tblRoleReturned")
Dim s As String = cmd.Parameters("@Return").Value
SQLDSReturn = iDataSet
**********STORED PROCEDURE***************************************** ****
CREATE PROCEDURE OADsp_SelectUnassignedReferrals
@Return varchar(50) output,
@Threshhold int
as
Set @Threshhold = 60
SELECT * FROM tbl_Referrals
WHERE (AssignedStaffID IS NULL AND TimeAssigned IS NULL)
OR (ReferralStaffID IS NULL AND
DateDiff(SECOND,(TimeAssigned),(GETDATE())) @Threshhold)
IF @@ERROR>0
BEGIN
RETURN 99
END
ELSE
BEGIN
RETURN 1
END
GO
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Steve Holden |
last post by:
Has anyone, with any driver whatsoever, managed to retrieve output
parameters from a SQL Server stored procedure? I've just been rather
embarrassed to find out it's not as easy as it might seem,...
|
by: Begoña |
last post by:
in my java application I've made a call to this stored procedure
CREATE procedure pruebaICM
@pANI varchar(20),
@pTABLA varchar(20),
@pInsert varchar(500),
@pUpdate varchar(1000),
@pFLAG...
|
by: Steven |
last post by:
I'm calling a stored procedure which has an output parameter of type int.
Once the stored procedure is executed, I want to check the value of the
parameter in case it is null. However, when the a...
|
by: laurenq uantrell |
last post by:
I need to get the value of an output parameter back into my VBA
function calling a stored procedure.
I'm using the following construction to append a new record in a SQL
Server table:
...
|
by: Yusuf INCEKARA |
last post by:
I have a stored procedure :
CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY...
|
by: Christopher Weaver |
last post by:
I'm having trouble accessing the value of an output parameter of a stored
procedure. The SP looks like this:
SET TERM ^ ;
CREATE PROCEDURE SP_NEW_TASK
RETURNS (
"uidTask" INTEGER)
AS
begin
|
by: MS |
last post by:
Here's my simple stored procedure:
ALTER PROCEDURE GetMemberIDByEmail
@Email EmailAddress,
@ID int OUTPUT
AS
SELECT @ID = ID FROM tbl_Member WHERE Email=@Email
RETURN
|
by: Peter |
last post by:
Hi, there
I have created an stored procedure using the DDL below for my MS Access
Database and no error occurs. Also it can create an stored procedure if I
changed the parameter from "" to...
|
by: Tifer |
last post by:
Hello,
I'm still new to the whole .Net thing and I'm having a problem with
something that should be so simple -- executing a query and returning
an output parameter. It's a standard "Add"...
|
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: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: 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,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |