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
(e-mail address removed)
Office: (540) 437-0553
Cell: (540) 246-5566
--
Tom Garth
William (Bill) Vaughn said:
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)
-----------------------------------------------------------------------------------------------------------------------
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
