Assigning ID to variable with parameter OUTPUT

Question

This INSERT query works perfectly fine inside of VS20012, SQL Server 2008 R2. But when I try to execute it from VB code behind I get DBnull Conversion error on the line trying to assign the value to the variable after the execute.

The insert is not successful either, but also does not cause the Try Catch to error. Even after connection is closed. Very weird behavior.

sqSql = "INSERT INTO tblLogging (vein, num, descripBaseFileName, " & _
        " csvBaseFileName, baseActive, diagActive) " & _
        " OUTPUT INSERTED.keyID" & _
        " VALUES (@vein, @num, @cnfFil, @csvFil, 'True', 'False')"

command3.Parameters.AddWithValue("@vein", vein1)
command3.Parameters.AddWithValue("@num", veinNum)
command3.Parameters.AddWithValue("@cnfFil", cnfFil)
command3.Parameters.AddWithValue("@csvFil", csvFil)

command3.Parameters.Add("@keyID", SqlDbType.Int)
command3.Parameters("@keyID").Direction = ParameterDirection.Output

Try
    command3.CommandText = sqSql

    oCnn.Open()
    command3.ExecuteScalar()
    oCnn.Close()

ERRORS OUT HERE:

    **Dim retkeyID As Integer = command3.Parameters("@keyID").Value**

Catch ex As Exception
    Throw ex

End Try

Any ideas on what I am doing wrong would be greatly appreciated.

Answer 1

In your context (a single insert with a single return value) you don't need an output parameter to get back the value of the OUTPUT clause.
You could simply convert the return value of the ExecuteScalar

Dim result = Convert.ToInt32(command3.ExecuteScalar())

If you really want to use the @keyID output parameter then you need to set it before exiting from the query.

sqSql = "INSERT INTO tblLogging (vein, num, descripBaseFileName, " & _
        " csvBaseFileName, baseActive, diagActive) " & _
        " VALUES (@vein,@num,@cnfFil,@csvFil,'True', 'False'); " & _
        "SELECT @KeyID = SCOPE_IDENTITY(); "

(Of course this assumes that KeyID is an IDENTITY column and is not calculated by some kind of trigger in the database)