How to return a value from stored procedure to Visual Basic.NET

Here I am going to show you how to return a value from a stored procedure written in SQL Server. As the first step we need to create a stored procedure that returns a value. In my case, I have a database called car and a table in it called tblCar. For counting the number  of row I used the following stored procedure.

If you don’t know how to create a stored procedure in SQL Server, please read this tutorial before we start.

http://tutz4u.blogspot.in/2014/03/how-to-make-stored-procedure-in-sqlserver.html

Step 1:

CREATE PROCEDURE usp_get_count

AS

BEGIN

 DECLARE @VALUE int;

 SET @VALUE=(SELECT COUNT(*) FROM tblCar);

 RETURN @VALUE;

END

GO

Please note that you must use the return keyword in order to return a specific value. If you executed the stored procedure that you created which returns a value, you will get the output with a parameter called @return_value by default. Ok now we have done the preliminary steps to return a value to Visual Basic .NET.

Step 2:

 Now open Visual Studio and create a new project. And add a button to the default form. We are going to code like, when we press that button a message box will appear showing the return value. Please check out the code listing.

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New SqlConnection

        Dim constr As String = "Data Source=.\SQLEXPRESS;Initial Catalog=car;Integrated Security=True;Pooling=False"

        Dim cmd As New SqlCommand

        Dim ReturnValue As SqlParameter

        Dim count As Integer

        con.ConnectionString = constr

        con.Open()

        cmd.Connection = con

        cmd.CommandType = CommandType.StoredProcedure

        cmd.CommandText = "usp_get_count"

        ReturnValue = cmd.Parameters.Add("Value", SqlDbType.Int)

        ReturnValue.Direction = ParameterDirection.ReturnValue

        cmd.ExecuteNonQuery()

        count = ReturnValue.Value

        MsgBox(count)

        cmd.Dispose()

        con.Close()

    End Sub

End Class

Here first we connected to the database with the con object. The main point is to create an SqlParameter Object and set the direction to Output or ReturnValue. Both works fine for me. You shoul make sure the you have used the ReturnValue sql parameter only after executing the command. Else you won’t get the value in it. In a stage we assigned like “ReturnValue = cmd.Parameters.Add("Value", SqlDbType.Int)”. You can specify anything instead of “Value” in this particular statement.Hope you enjoyed the tutorial and stay tuned for more tutorials on stored procedures.