SQLClient output parameter returns DBNull

Question

Its a ASP.net application in VS2008, connecting to SQL 2005 database.

No errors calling the Stored procedure, db update is successful but the OUTPUT param returns DBnull all the time. Below the vb code:

Dim ConnectString As String = "", connect As New Data.SqlClient.SqlConnection
ConnectString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
connect.ConnectionString = ConnectString
Dim cmd As New SqlCommand("saveAccess", connect)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@Name", "SampleName"))
Dim outparam As SqlParameter = New SqlParameter("@returnValue", SqlDbType.Int)
outparam.Direction = ParameterDirection.Output
cmd.Parameters.Add(outparam)
connect.Open()
cmd.ExecuteNonQuery()
If IsDBNull(cmd.Parameters("@returnValue").Value Then
    Response.Write("Why does it always returns DBNull")
Else : Response.Write(cmd.Parameters("@returnValue").Value.ToString())
End If
connect.Close()

Here is the SQL code

ALTER PROCEDURE [dbo].[saveAccess] 
(@Name NVARCHAR(20), @returnValue INT OUTPUT )
AS
BEGIN
INSERT INTO Access ([Name]) VALUES (@Name);

SELECT @returnValue = @@ROWCOUNT;
END

Not sure what is the silly mistake that I am doing. Any input helps.

Thanks

you need to close the connection before you can read the output parameter being set in your SPROC. Output parameters are set in the variables once the connection is closed. — Ahmed ilyas, Jun 11 '14 at 18:39
Solution (as said silly myself): missed the @ symbol in front of the returnValue variable. I typed up the code in this posting correctly but I had it without the @ in the SP. 'code'wrong: SELECT returnValue = @@ROWCOUNT; 'code'correct: SELECT @returnValue = @@ROWCOUNT; — user836107, Jun 11 '14 at 19:03

score 1 · Answer 1 · answered Jun 11 '14 at 18:38

1

Instead of SELECT, try using SET to set the value of the output parameter

SET @returnValue = @@ROWCOUNT;

answered Jun 11 '14 at 18:38

Sachin

40,216
7
90
102

Thanks Sachin, that helped me put the missing @ symbol in front of the returnValue variable which I was missing. It works now. SET or SELECT works the same way. – user836107 Jun 11 '14 at 19:10

score 1 · Answer 2 · answered Jun 12 '14 at 12:45

Solution (as said silly myself): missed the @ symbol in front of the returnValue variable. I typed up the code in this posting correctly but I had it without the @ in the SP.

wrong: SELECT returnValue = @@ROWCOUNT;

correct: SELECT @returnValue = @@ROWCOUNT;

Thanks

SQLClient output parameter returns DBNull

2 Answers2