[2005] Stored Procedure and VB.Net scenario?

[blakemckenna]

I have the following Stored Procedure that basically Inserts a record based on the PK (3 parameter fields make up the PK). If the record already exists, it will Error out but continue on selecting the record that already exists and returns a single value (rowID).

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[prcInsertBatchHeader]
	(
        @terminalNumber		varchar(80),
	@supplier		varchar(60),
	@batchDateTime		varchar(22)
	)
AS
SET NOCOUNT ON

         DECLARE @NewRowID	bigint

	INSERT INTO tblBatchHeader
		(
		terminalNumber,
		supplier,
		batchDateTime
		)
	VALUES
		(
		@terminalNumber,
		@supplier,
		@batchDateTime
		)

	SELECT	rowID 
	  FROM	tblBatchHeader
	 WHERE  (terminalNumber = @terminalNumber)	AND
		(supplier = @supplier)	                        AND
		(batchDateTime = @batchDateTime)

In my VB.Net code I need to return the rowID and I'm not sure how to do this. Here is the VB Code:

    Private Sub WriteHeaderRecord(ByVal strTerminal As String, _
                                  ByVal strSupplier As String, _
                                  ByVal strBatchDateTime As String)
        Try
            Dim cmdSelect As New SqlCommand("prcInsertBatchHeader", cn)
            cmdSelect.CommandType = CommandType.StoredProcedure
            cmdSelect.Transaction = trans

            cmdSelect.Parameters.Add(New SqlParameter("@terminalNumber", SqlDbType.VarChar, 80))
            cmdSelect.Parameters.Add(New SqlParameter("@supplier", SqlDbType.VarChar, 60))
            cmdSelect.Parameters.Add(New SqlParameter("@batchDateTime", SqlDbType.VarChar, 22))
            cmdSelect.Parameters.Add(New SqlParameter("@retVal", SqlDbType.Int, 0))

            cmdSelect.Parameters("@retVal").Direction = ParameterDirection.ReturnValue

            cmdSelect.Parameters("@terminalNumber").Value = strTerminal
            cmdSelect.Parameters("@supplier").Value = strSupplier
            cmdSelect.Parameters("@batchDateTime").Value = strBatchDateTime

            cmdSelect.ExecuteNonQuery()

            I need to get the value of the rowID here!!!

        Catch ex As Exception
            strProcedure = "WriteHeaderRecord()"
            msg = ex.Message
            WriteToErrorFile(msg, strFileName, strProcedure)
        End Try
    End Sub

Thanks,

[techgnome]

Two ways ... 1) an output parameter... then jsut assign the value you selected to the outoput parameter. 2) Don't use ExecureNonQuery, but actually execute with DataSet or a reader,and get the value that way.

-tg

[blakemckenna]

I'm not sure how to set it up in the SP or the VB Code!

[techgnome]

Scratch that... I see you already have the VB code for a return parameter... good.. the fix is even simpler...
in the sp, do this:

declare a variable of type in .. say @NewRowID... then change the select to put the RowID into the variable
SELECT @NewRowID = RowID .....

then after that....add the return
RETURN @NewRowID

After the ExecuteNonQuery, your return parameter will have the value in it
cmdSelect.Parameters("@retVal").Value

easy peasy.

-tg

[blakemckenna]

Tech,

For some reason it's still not working. Here is the revised code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[prcInsertBatchHeader]
	(
        @terminalNumber			varchar(80),
	@supplier			varchar(60),
	@batchDateTime			varchar(22),
	@rowID				int
	)
AS
SET NOCOUNT ON


	INSERT INTO tblBatchHeader
		(
		terminalNumber,
		supplier,
		batchDateTime
		)
	VALUES
		(
		@terminalNumber,
		@supplier,
		@batchDateTime
		)

	SELECT	@rowID = rowID 
	  FROM	tblBatchHeader
	 WHERE  (terminalNumber = @terminalNumber)	AND
		(supplier = @supplier)			AND
		(batchDateTime = @batchDateTime)

	RETURN @rowID

    Private Sub WriteHeaderRecord(ByVal strTerminal As String, ByVal strSupplier As String, ByVal strBatchDateTime As String)
        Try
            Dim cmdInsert As New SqlCommand("prcInsertBatchHeader", cn)

            cmdInsert.Transaction = trans
            cmdInsert.CommandType = CommandType.StoredProcedure

            cmdInsert.Parameters.Add(New SqlParameter("@terminalNumber", SqlDbType.VarChar, 80))
            cmdInsert.Parameters.Add(New SqlParameter("@supplier", SqlDbType.VarChar, 60))
            cmdInsert.Parameters.Add(New SqlParameter("@batchDateTime", SqlDbType.VarChar, 22))
            cmdInsert.Parameters.Add(New SqlParameter("@rowID", SqlDbType.Int, 0))

            cmdInsert.Parameters("@terminalNumber").Value = strTerminal
            cmdInsert.Parameters("@supplier").Value = strSupplier
            cmdInsert.Parameters("@batchDateTime").Value = strBatchDateTime
            cmdInsert.Parameters("@rowID").Value = 0

            cmdInsert.ExecuteNonQuery()

            intRowID = cmdInsert.Parameters("@rowID").Value

            If intRowID = 0 Then
                Err.Raise(5)
            End If

        Catch sqlEX As SqlException
            strProcedure = "WriteHeaderRecord()"
            msg = sqlEX.Message
            WriteToErrorFile(msg, strFileName, strProcedure)

        Catch ex As Exception
            strProcedure = "WriteHeaderRecord()"
            msg = ex.Message
            WriteToErrorFile(msg, strFileName, strProcedure)
        End Try
    End Sub

[techgnome]

you're missing this line:
cmdSelect.Parameters("@retVal").Direction = ParameterDirection.ReturnValue

You had it in there originally...that's what's going to tell ado.net where to put the RETURN value from.

IF it still doesn't work... try adding that parameter first. I usually do that out of habbit (standards here) so I don't know if it makes a difference or not. but the Direction shuld be set right after adding it, before adding the next.

-tg