execute stored procedure using sqldatasource and get return value in vb.net

Question

How can I execute a stored procedure using sqldatasource and get the return value in vb.net.

Thanks,

Terri

Answer 1

You need to use a SqlConnection with a SqlCommand, like this:

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("StoredProcedureName", connection)) {
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("SomeParam", someValue);

    object result = command.ExecuteScalar();
}

Answer 2

If you already have the SP returning a value then you have to grab the value in the corresponding event for the data source. AKA - Inserted, Selected, etc...

Here's a couple links illustrating the point.

http://fredrik.nsquared2.com/viewpost.aspx?PostID=162

http://www.velocityreviews.com/forums/t86158-re-how-to-retrieve-an-output-parameter-using-sqldatasource-control.html