This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
| =?iso-8859-1?Q?Andr=E9_Colbi=F6rnsen?= |
Is it totally unecessary to declare the stored proc?
Cmd.CommandType =3D CommandType.StoredProcedure
And is it also unecessary to declare that you want a return value?
Dim pVal As SqlParameter =3D cmd.Parameters.Add("Returnvalue",
SqlDbType.Int)
pVal.Direction =3D ParameterDirection.ReturnValue
Cmd.ExecuteScalar()
int =3D cmd.Parameters("ReturnValue").Value
Am I barking up the wrong tree in assuming this?
Regards/Halsningar
Andre Colbiornsen
--------------------------------------
Sonnenburg Communications
Bergsgatan 3,
SE-211 54 Malm=F6
Sweden
Tel.: +46-(0)40-97 78 80
Fax.: +46-(0)40-97 78 80
Mob.: +46-(0)708-97 78 79
Mail: Click here to reveal e-mail address
Web.: www.sonnenburg.se
----------------------------------------
B2B Web Solutions - Specializing in .Net
----------------------------------------
-----Ursprungligt meddelande-----
Fr=E5n: William (Bill) Vaughn [mailto:Click here to reveal e-mail address]=20
Skickat: den 26 juli 2002 01:36
Till: ngfx-sqlclient
=C4mne: [ngfx-sqlclient] RE: sp return value
stored procedure:
CREATE PROCEDURE JustReturnValue
AS
RETURN 1111
Option Strict On
Option Explicit On
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As SqlConnection
Dim cmd As SqlCommand
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
cn =3D New SqlConnection("data source=3D.;integrated
security=3Dsspi;initial catalog=3Dbiblio")
cn.Open()
cmd =3D New SqlCommand("JustReturnValue", cn)
Dim int As Integer
int =3D CInt(cmd.ExecuteScalar)
Debug.WriteLine(int.ToString)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
cn.Close()
End Sub
End Class
William (Bill) Vaughn
Author, trainer, mentor
Microsoft Regional Director -- Pacific Northwest
Beta V Corporation
Redmond, Washington USA
www.betav.com
(425) 556-9205 (v/f)
-----Original Message-----
From: Peter Brunone [mailto:Click here to reveal e-mail address]
Sent: Thursday, July 25, 2002 4:20 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: sp return value
Can we see the SP and .NET code? It sounds like something's not
right; I've done a ton of RETURN @@IDENTITY SP's that successfully get
picked up with ExecuteScalar().
-Peter
-----Original Message-----
From: William (Bill) Vaughn [mailto:Click here to reveal e-mail address]
I just tried this with an stored procedure that has
RETURN 1111
And ExecuteScalar returned 0.
William (Bill) Vaughn
-----Original Message-----
From: Russ McClelland [mailto:Click here to reveal e-mail address]
Right, if all your after is the value returned from your SP such as:
RETURN @@IDENTITY
The ExecuteScalar() is the easiest mechanism to use. You don't have to
create extra parameters or access them after the call. ExecuteScalar()
will provide the value for you and you simply cast it into what you
expect (decimal, int, etc.)
| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID=20
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT
|
|
| |
| |
| William \(Bill\) Vaughn (VIP) |
No, my example shows that the ExecuteScalar does NOT work to return
either the ReturnValue or Output parameters.
Yes, you do need to define the parameters and retrieve them post
execution (via ExecuteNonQuery if there is no rowset in the resultset.
Sorry for the confusion.
William (Bill) Vaughn
Author, trainer, mentor
Microsoft Regional Director -- Pacific Northwest
Beta V Corporation
Redmond, Washington USA
www.betav.com
(425) 556-9205 (v/f)
-----Original Message-----
From: André Colbiörnsen [mailto:Click here to reveal e-mail address]
Sent: Thursday, July 25, 2002 4:45 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] SV: RE: sp return value
Is it totally unecessary to declare the stored proc?
Cmd.CommandType = CommandType.StoredProcedure
And is it also unecessary to declare that you want a return value?
Dim pVal As SqlParameter = cmd.Parameters.Add("Returnvalue",
SqlDbType.Int)
pVal.Direction = ParameterDirection.ReturnValue
Cmd.ExecuteScalar()
int = cmd.Parameters("ReturnValue").Value
Am I barking up the wrong tree in assuming this?
Regards/Halsningar
Andre Colbiornsen
--------------------------------------
Sonnenburg Communications
Bergsgatan 3,
SE-211 54 Malmö
Sweden
Tel.: +46-(0)40-97 78 80
Fax.: +46-(0)40-97 78 80
Mob.: +46-(0)708-97 78 79
Mail: Click here to reveal e-mail address
Web.: www.sonnenburg.se
----------------------------------------
B2B Web Solutions - Specializing in .Net
----------------------------------------
-----Ursprungligt meddelande-----
Från: William (Bill) Vaughn [mailto:Click here to reveal e-mail address]
Skickat: den 26 juli 2002 01:36
Till: ngfx-sqlclient
Ämne: [ngfx-sqlclient] RE: sp return value
stored procedure:
CREATE PROCEDURE JustReturnValue
AS
RETURN 1111
Option Strict On
Option Explicit On
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As SqlConnection
Dim cmd As SqlCommand
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
cn = New SqlConnection("data source=.;integrated
security=sspi;initial catalog=biblio")
cn.Open()
cmd = New SqlCommand("JustReturnValue", cn)
Dim int As Integer
int = CInt(cmd.ExecuteScalar)
Debug.WriteLine(int.ToString)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
cn.Close()
End Sub
End Class
William (Bill) Vaughn
Author, trainer, mentor
Microsoft Regional Director -- Pacific Northwest
Beta V Corporation
Redmond, Washington USA
www.betav.com
(425) 556-9205 (v/f)
-----Original Message-----
From: Peter Brunone [mailto:Click here to reveal e-mail address]
Sent: Thursday, July 25, 2002 4:20 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: sp return value
Can we see the SP and .NET code? It sounds like something's not
right; I've done a ton of RETURN @@IDENTITY SP's that successfully get
picked up with ExecuteScalar().
-Peter
-----Original Message-----
From: William (Bill) Vaughn [mailto:Click here to reveal e-mail address]
I just tried this with an stored procedure that has
RETURN 1111
And ExecuteScalar returned 0.
William (Bill) Vaughn
-----Original Message-----
From: Russ McClelland [mailto:Click here to reveal e-mail address]
Right, if all your after is the value returned from your SP such as:
RETURN @@IDENTITY
The ExecuteScalar() is the easiest mechanism to use. You don't have to
create extra parameters or access them after the call. ExecuteScalar()
will provide the value for you and you simply cast it into what you
expect (decimal, int, etc.)
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT
|
|
| |
|
|
|
|
|
