Retrieve output parameter (identity column) from stored procedure with vb.net oledbcommand

Question

I am trying to retrieve an output parameter value (identity field) from a stored procedure in SQL Server 2008 R2.

I looked at the following stackoverflow links. I am doing the same but still facing issues: Retrieving output parameter from stored procedure with oledb command vb.net Stored procedure - return identity as output parameter or scalar

My stored procedure:

ALTER proc [dbo].[sp_Custom_InsertxRef]
       @DocumentID int,
       @RevNr int,
       @xRefDocument int,
       @xRefRevNr int,
       @xRefProjectId int,
       @RefCount int,
       @xRef int OUTPUT
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @HasFullPath bit=1;
   DECLARE @RelativePath nvarchar(300)='';
   DECLARE @RefCountEdit float=NULL;
   DECLARE @RefTimeStamp as datetime=GETDATE();
   DECLARE @xrType as int = 1;

INSERT INTO [EpiGrid].[dbo].[XRefs]
           ([DocumentID]
           ,[RevNr]
           ,[XRefDocument]
           ,[XRefProjectID]
           ,[XRefRevNr]
           ,[HasFullPath]
           ,[RelativePath]
           ,[RefCount]
           ,[RefCountEdit]
           ,[RefTimeStamp]
           ,[XrType])
     VALUES
     (
        @DocumentID,
        @RevNr,
        @xRefDocument,
        @xRefProjectId,
        @xRefRevNr,
        @HasFullPath,
        @RelativePath,
        @RefCount,
        @RefCountEdit,
        @RefTimeStamp,
        @xrType
     )
   SET @xRef=(SELECT SCOPE_IDENTITY());
   --select @xRef=(SELECT SCOPE_IDENTITY());
   return @xRef;
END

My vb.net code:

Using connection As New System.Data.OleDb.OleDbConnection(Connectionstring)
                connection.Open()
                Using command As New OleDbCommand("sp_Custom_InsertxRef", connection)
                    command.CommandType = CommandType.StoredProcedure
                    command.Parameters.Add("@DocumentID", OleDbType.Integer, 4, ParameterDirection.Input).Value = epdmParDoc.ID
                    command.Parameters.Add("@RevNr", OleDbType.Integer, 4, ParameterDirection.Input).Value = epdmParDoc.GetLocalVersionNo(parFolderId)
                    command.Parameters.Add("@xRefDocument", OleDbType.Integer, 4, ParameterDirection.Input).Value = targetReplaceDoc.ID
                    command.Parameters.Add("@xRefRevNr", OleDbType.Integer, 4, ParameterDirection.Input).Value = targetReplaceDoc.CurrentVersion
                    command.Parameters.Add("@xRefProjectId", OleDbType.Integer, 4, ParameterDirection.Input).Value = parFolderId
                    command.Parameters.Add("@RefCount", OleDbType.Integer, 4, ParameterDirection.Input).Value = count
                    'command.Parameters.Add("@xRef", OleDbType.Integer, 4, ParameterDirection.InputOutput).Value = -1
                    command.Parameters.Add("@xRef", OleDbType.Integer)
                    command.Parameters("@xRef").Direction = ParameterDirection.Output
                    command.ExecuteReader()
                    xRefId = command.Parameters("@xRef").Value
                End Using
                connection.Close()
            End Using

I tried direction as output and return value and I see the same result. The record is created in the database (no errors) but my code gets nothing for the output parameter. Any idea why?

When I debug the stored procedure in SQL Server Management Studio it creates the record and I see the return or output (tried both) with correct value.

Exec [dbo].[sp_Custom_InsertxRef]
       @DocumentID =12,
       @RevNr =1,
       @xRefDocument = 15,
       @xRefRevNr =1,
       @xRefProjectId =1,
       @RefCount =2,
       @xRef=-1;

Please advise.

1. Why use OleDB if Microsoft plans to scrap it? - use `SqlClient`. 2. Try to set size of your out parameter. — T.S., Jun 28 '14 at 02:15
tried that too. I also tried setting it to 4. I tried both as separate line for the direction definition as well as part of the paramater add line. Same results. Using VS 2008 .net 3.5 — user2965499, Jun 28 '14 at 02:48
I have not noticed but you do have "ExecuteReader"... use "ExecuteNonQuery" and you will be fine — T.S., Jun 28 '14 at 18:47

Shell · Answer 1 · 2014-06-29T04:34:31.523

First of all why are you using execute reader function? if you don't want to return any value from stored then just use ExecuteNonQuery() method instead of ExecuteReader(). If you want to read value from stored procedure then you can use ExecuteReader() but, you must have assigned DataReader object.

For example

Dim dr As OleDBDataReader = Nothing
Try    
    dr  = command.ExecuteReader()
    IF dr.HasRows Then
        While (dr.Read())
            var value = dr.getValue(0)
        End While
    End IF
    dr.Close()
Catch
    'OleDB Exception will be thrown here 
Finally
    IF dr IsNot Nothing Then
        IF dr.IsClosed = false Then dr.Close()
    End If
End Try

but, to use above method you should make some changes in your stored procedure.

Replace following line:

SET @xRef=(SELECT SCOPE_IDENTITY());
--select @xRef=(SELECT SCOPE_IDENTITY());
return @xRef;

To

SELECT SCOPE_IDENTITY();

after these output parameter will not be needed in your stored procedure.

score 1 · Answer 2 · answered Jun 28 '14 at 10:32

Using the same SP (no changes) with sqlclient instead of the oledb did the job. Probably a bug.

Using connection As New System.Data.SqlClient.SqlConnection(connectionstrng)
                connection.Open()
                Using command As New System.Data.SqlClient.SqlCommand("sp_Custom_InsertxRef", connection)
                    command.CommandType = CommandType.StoredProcedure
                    command.Parameters.Add("@DocumentID", SqlDbType.Int, 4, ParameterDirection.Input).Value = epdmParDoc.ID
                    command.Parameters.Add("@RevNr", SqlDbType.Int, 4, ParameterDirection.Input).Value = epdmParDoc.GetLocalVersionNo(parFolderId)
                    command.Parameters.Add("@xRefDocument", SqlDbType.Int, 4, ParameterDirection.Input).Value = targetReplaceDoc.ID
                    command.Parameters.Add("@xRefRevNr", SqlDbType.Int, 4, ParameterDirection.Input).Value = targetReplaceDoc.CurrentVersion
                    command.Parameters.Add("@xRefProjectId", SqlDbType.Int, 4, ParameterDirection.Input).Value = parFolderId
                    command.Parameters.Add("@RefCount", SqlDbType.Int, 4, ParameterDirection.Input).Value = count
                    'command.Parameters.Add("@xRef", OleDbType.Integer, 4, ParameterDirection.InputOutput).Value = -1
                    command.Parameters.Add("@xRef", SqlDbType.Int)
                    command.Parameters("@xRef").Direction = ParameterDirection.Output
                    command.ExecuteReader()
                    xRefId = command.Parameters("@xRef").Value
                End Using
                connection.Close()
            End Using

Retrieve output parameter (identity column) from stored procedure with vb.net oledbcommand

2 Answers2

Linked