Search code examples
sql-servervb.netstored-proceduresreturn

Visual Basic 2019: call SQL Server stored procedure and store return value

I'm currently migrating a VB6 app to VB.NET and I'm having trouble capturing a returned value from a stored procedure.

This procedure returns a count (@count int output). This works fine in VB6 so I am confident that the stored procedure is OK and my modern VB 2019 code is the problem.

This code executes the procedure but errors trying to extract the returned integer. I assume that I don't need a DataTable to capture the returned value but I can't figure out what adjustment I need to make. I tried to QuickWatch the variables (using VS 2019) but I don't see anything being populated by the stored procedure at any point in the chain.

' Create & Call SQL Server stored procedure to obtain the count
Dim respondent_count_Command = New SqlCommand
respondent_count_Command.Connection = conn
respondent_count_Command.CommandType = CommandType.StoredProcedure

' Declare output parameter
Dim param_count = New SqlParameter()
param_count.ParameterName = "@Count"
param_count.SqlDbType = SqlDbType.Int
param_count.Direction = ParameterDirection.Output
respondent_count_Command.Parameters.Add(param_count)

' Execute the procedure
respondent_count_Command.CommandText = "[target_db].dbo.[p_XPR_ExcelExporting_Ansbooks_Count]"
Dim respondent_count_dt = New DataTable
respondent_count_dt.Load(respondent_count_Command.ExecuteReader())
conn.Close()

' Store the returned value in a dataRow. This value is expected to be an INTEGER
Dim ques_number_rows() As DataRow = respondent_count_dt.Select()

AnsBookCount = ques_number_rows(0)(0)

Error:

System.IndexOutOfRangeException: 'Index was outside the bounds of the array'

Solution

  • Assuming the stored procedure has a single output parameter and returns no resultsets. Just run

    Dim respondent_count_Command = New SqlCommand
respondent_count_Command.Connection = conn
respondent_count_Command.CommandType = CommandType.StoredProcedure

' Declare output parameter
Dim param_count = New SqlParameter()
param_count.ParameterName = "@Count"
param_count.SqlDbType = SqlDbType.Int
param_count.Direction = ParameterDirection.Output
respondent_count_Command.Parameters.Add(param_count)

' Execute the procedure
respondent_count_Command.CommandText = "[target_db].dbo.[p_XPR_ExcelExporting_Ansbooks_Count]"

respondent_count_Command.ExecuteNonQuery()

AnsBookCount = param_count.Value