With classic ADO you could attach a ReturnValue parameter to a command
containing inline parameters.
Ex:

Cmd.CommandString = "PubsInsert('G. B. Shaw', 'I lost my Rifle')"
Dim pRET As New ADODB.Parameter
pRET.Type = adInteger
pRET.Direction = adParamReturnValue
Cmd.Parameters.Append pRET
Cmd.Execute

lngNewID = pRET.Value
etc., etc,.

In this case the T-SQL procedure ends with something like RETURN @@IDENTITY.

I can't find a way to get a ReturnValue type parameter to actually return
the RETURN value with ADO.NET.

Is this no longer possible?

Tom Garth
--
Tom Garth

It's pretty easy.
Private Sub BuildCommand()
Try
cmd = New SqlCommand("ReturnAuthorCountByYearBorn", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@YearWanted", 1947)
cmd.Parameters.Add("@AuthorCount",SqlDbType.Int)
cmd.Parameters("@AuthorCount").Direction _
= ParameterDirection.Output
cmd.Parameters.Add("@ReturnValue",SqlDbType.BigInt )
cmd.Parameters("@ReturnValue").Direction _
= ParameterDirection.ReturnValue

Dim dr As SqlDataReader
cn.Open()
dr = cmd.ExecuteReader()
Dim dt As New DataTable
dt.Load(dr)
Dim intReturnValue As Integer
intReturnValue = CInt(cmd.Parameters("@ReturnValue").Value)
Catch exsql As SqlException
MessageBox.Show(exsql.ToString)
Catch ex As Exception
Debug.Assert(False, ex.ToString)
Finally
cn.Close()
End Try
End Sub

There are a couple of issues going on in the code. I assume SqlClient. This
provider supports named parameters. Not all do. Note that I have fetched all
of the rows in the resultset before trying to fetch the output parameter or
return value parameter.

This is discussed in greater detail in my book.

hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Tom Garth" <TomGarth (AT) discussions (DOT) microsoft.com> wrote

Hi Bill,

Thanks for the response. I just wasn't wordy enough with my submittal. The
sample that I sent was code to Insert a single new record to a table. I
didn't add any parameter objects to do so, The entire command was in the
CommandText. Here is a real time example.

In SQL Server (2000 or 2005)
________________________________
CREATE PROCEDURE [dbo].[Notes_Insert]
@Note text
AS

DECLARE @RETURN int, @ERROR int
SELECT @RETURN = 0, @ERROR = 0
INSERT INTO Notes
SELECT @Note
SELECT @Note_ID = @@IDENTITY, @ERROR = @@ERROR

IF @ERROR <> 0 BEGIN SET @RETURN = @ERROR END
ELSE BEGIN SET @RETURN = @Note_ID END

RETURN @RETURN
________________________________

In VB6 using ADO and OLEDB for SQL Server the code was like this.
________________________________
Dim lngNewIdentity as long
Dim Cmd as New ADODB.Command
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "Notes_Insert('Reply to Bill on Friday')"

' No parameter object yet, and none can be successfully used at this
point except
' for one of type ReturnValue like this.

Dim pRet as New ADODB.Parameter
pRET.Type = adInteger
pRET.Direction = adParamReturnValue
Cmd.Parameters.Append pRET

Cmd.ActiveConnection = cnSem

Cmd.Execute

lngNewIdentity = pRet.Value
________________________________

I have thousands of line of code written like this that work great. Now I am
using VB 2005 and the SqlClient.SqlCommand
________________________________

Dim Cmd as New SqlCommand, lngNewIdentity as long
Cmd.Connection = cnSem
Cmd.CommandType = CommandType.Text
Cmd.CommandText = "Notes_Insert 'Reply to Bill on Friday' "


Dim pRET As New SqlParameter("RetValue", SqlDbType.Int)
pRET.Direction = ParameterDirection.ReturnValue
Cmd.Parameters.Add(pRET)

Cmd.ExecuteNonQuery()

lngNewIdentity = pRet.Value
________________________________

The parameter value always comes back 0. Note too that the command type was
Text not StoredProcedure. That may be where the problem lies, but it won’t
run as CommandType.StoredProcedure. With hundreds of stored procedures to
code to, some having 20 or 30 parameters, the in-line (as I call them)
parameters save a ton of code. So, is it no longer possible to do what I
want, or have I just not explored enough options. I haven’t used the OleDB
command object to date, and that might be the only reason I haven’t conquered
this. So far I’ve been making a second trip with a scalar reader to get the
@@IDENTITY created by the new record insert.

Any insight would be really appreciated.

Regards,

Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
tgarth (AT) rlninc (DOT) com
Office: (540) 437-0553
Cell: (540) 246-5566




--
Tom Garth


"William (Bill) Vaughn" wrote:

Right. If you don't tell ADO.NET that it's a SP, it won't treat the text as
anything but raw SQL. While you can mark parameters (using named parameters
prefixed with "@"), you won't get back any OUTPUT or RETURN value parms.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Tom Garth" <TomGarth (AT) discussions (DOT) microsoft.com> wrote

Bill,

I found a solution. It would have been nice to have heard from someone
admitting the loss of this ADO Classic functionality. For the record, I did
try to do the same thing using System.Data.OleDb objects. That didn't work
either. What does work results in the following command being sent as
ExecuteReader:

"DECLARE @RETURN int; EXEC @RETURN = PM_InsertUpdate
6,'Albright','Scott','H.','Mr.','6/30/1970','Washington and Lee
University',1994,'tgarth','L070762','Albright, Scott Harrison'; SELECT
@RETURN [RETURN]"

Part of the code is (the command object already has the basic text before
this):

On Error GoTo EH
Call OpenDatabase("", "")
Cmd.CommandType = CommandType.Text
Cmd.Connection = mcnRLNShip
If GetIdentity = True Then
Dim dr As SqlDataReader
Dim s As String = Cmd.CommandText
s = "DECLARE @RETURN int; EXEC @RETURN = " & s & "; SELECT
@RETURN [RETURN]"
Cmd.CommandText = s
dr = Cmd.ExecuteReader
If dr.HasRows = True Then
dr.Read()
l = CLng(dr("RETURN").ToString)
End If
If dr.IsClosed = False Then
dr.Close()
End If
Else
l = Cmd.ExecuteNonQuery
End If

The string building requires less code than creating parameter objects for
each argument, and the traffic sent is less. It appears to be very efficient
so far.

Thanks for your help, and for all your fine books,

--
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com

tgarth (AT) rlninc (DOT) com
Office: (540) 437-0553
Cell: (540) 246-5566



"William (Bill) Vaughn" wrote: