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.

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Nov 03 '14 at 21:22

Steve · Accepted Answer · 2014-11-03T21:23:27.470

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)

edited Nov 03 '14 at 21:23

answered Nov 03 '14 at 20:59

Steve

213,761
22
232
286

I know this should work, but I keep getting zero back. Tested the connection by dropping a Gridview on the form and using the smart tag to configure a query, allows editing and updates and deletes, but this query will not insert a record at this time. – htm11h Nov 03 '14 at 21:53
You need to debug. Do you get something back from ExecuteScalar or do you get zero?. In any case I will try to resolve step by step. First I would just use the insert without any OUTPUT looking if the record has been added or not. Then readd the code to get back the OUTPUT or the SELECT IDENTITY – Steve Nov 03 '14 at 22:02
As a side note baseActive and diagActive are bit columns? – Steve Nov 03 '14 at 22:04
I get Zero back. I used your first solution, just returning the result of the ExecuteScalar, this did not use the OUTPUT clause. I also removed the bit fields though I doubt they were an issue. – htm11h Nov 04 '14 at 13:15
If the record doesn't exists then there is something really wrong in the query text. Try removing the OUTPUT part. Are the parameters passed of the same type of the columns? (Albeit you should get an exception in this case) – Steve Nov 04 '14 at 13:31
Well, surprisingly I think there is something wrong with the SQL DB, the records were inserted, but I did not get back the value of the keyID. for some reason my table was not refreshing yesterday when the insert was performed. When I went to look at the table it was empty. Today the test records are there. Not sure whey that is happening. I recently had an issue changing a column within VS12. It would not save the change. Had to use SSMS. Very weird. I am going to accept your answer because I know it should be working. – htm11h Nov 04 '14 at 15:26
Please check your connectionstring. Look at this answer.http://stackoverflow.com/questions/17147249/why-dont-changes-to-database-save/17147460#17147460 – Steve Nov 04 '14 at 15:51

Assigning ID to variable with parameter OUTPUT

1 Answers1