How to get a return value from a stored procedure in VB.NET

I have a stored procedure in SQL Server for generating transaction numbers.

Can anyone help me with how to call the Stored Procedure from VB.NET and how will i get the value that is returned from the proced开发者_如何学Goure into the front end.

Regards, George

I think you want something like this:

Public Sub Foo()

Using sql As New SqlClient.SqlConnection("YourConnection")
  sql.Open()
  Using cmd As New SqlClient.SqlCommand("YourSPName", sql)
    cmd.CommandType = CommandType.StoredProcedure
    Dim myReturnValue As String = cmd.ExecuteScalar
  End Using
End Using
End Sub

Where myReturnValue will be what ever your output param in SQL is.

What kind of value is it you're returning? Will that value in turn result in another database action?

It might be best to return data instead of a single value.

For example if you were verifying the username and password for a potential login, instead of returning a simple true or false you would return the users information. No information returned means failed login.

This method has the advantage of minimising database requests, something which will have a serious effect if it is a common action.

Personally I've never needed to return a single value.