Getting an Output Parameter from a SqlDataSource

[John Bailo]

This is a my solution to getting an Output parameter from a SqlDataSource.

I have seen a few scant articles but none of them take it all the way to
a solution. Hopefully this will help some poor soul.

Situation: I want to do a lookup using a stored procedure for each
value in a Row within a GridView.

I use a lookup function in my code behind, evaluating the necessary
bound fields. The problem is the SqlDataSource representing the stored
procedure was returning an empty string after running the .Select() method.

<asp:Label
ID="FieldValue"
runat="server" Text='<%#
lookupValueName((string)DataBinder.Eval(Container,"DataItem.FieldName"),(string)DataBinder.Eval(Container,"DataItem.FieldValue"))
%>'>
</asp:Label>


However, after some research, I found that I did see the Output
parameter in the Selecting event, but only in the
SqlDataSourceStatusEventArgs object. Finally, it occured to me, I
would simply set the Output parameter for the SqlDataSource in the
Selecting event handler.

protected void SQLDataSource5_Selected(
object sender,
SqlDataSourceStatusEventArgs e)
{
SqlDataSource5.SelectParameters["ValueName"].DefaultValue =
e.Command.Parameters["@ValueName"].Value.ToString();
}

I guess it was built this way to allow the developer to handle datatype
conversions in the Selected event handler and parameter setup in the
Selecting event handler.

Then when I was back in my lookup function, I could access the value of
the output paramter.

string valuename =
SqlDataSource5.SelectParameters["ValueName"].DefaultValue;

An alterative may have been to add the data source to the GridRow
ItemTemplate...however, since I was using an asp:label, I didn't see a
method to bind the output parameter to the label.

 

[RobinS]

I'm not sure this will help you. You have a lot of words, and it's late
where I live. Here's an example of how to use output parameters to get data
from SQLServer. This runs against Northwind.


How to get a parameter back:

Dim SQLString = "SELECT @UnitPrice = UnitPrice, " & _
" @UnitsInStock = UnitsInStock, " & _
"FROM Products WHERE ProductName = @ProductName"

Dim pUnitPrice, pInStock, pProductName As SqlParameter
pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)
pUnitPrice.Direction = ParameterDirection.Output
pInStock = cmd.Parameters.Add("@UnitsInStock", SqlDbType.NVarChar, 20)
pInStock.Direction = ParameterDirection.Output
pProductName = cmd.Parameters.Add("ProductName", SqlDbType.NvarChar, 40)
pProductName.Value = "Chai"

cmd.ExecuteNonQuery()
if pUnitPrice.Value Is DBNull.Value Then 'none were found
Console.WriteLine("No product found named {0}", pProductName.Value)
Else
Console.WriteLine("Unit price: {0}", pUnitPrice.Value)
Console.WriteLine("In Stock: {0}", pInStock.Value)
End If

Robin S.
--------------------------------

This is a my solution to getting an Output parameter from a
SqlDataSource.

I have seen a few scant articles but none of them take it all the way to
a solution. Hopefully this will help some poor soul.

Situation: I want to do a lookup using a stored procedure for each value
in a Row within a GridView.

I use a lookup function in my code behind, evaluating the necessary bound
fields. The problem is the SqlDataSource representing the stored
procedure was returning an empty string after running the .Select()
method.

<asp:Label
ID="FieldValue"
runat="server" Text='<%#
lookupValueName((string)DataBinder.Eval(Container,"DataItem.FieldName"),(string)DataBinder.Eval(Container,"DataItem.FieldValue"))
%>'>
</asp:Label>


However, after some research, I found that I did see the Output parameter
in the Selecting event, but only in the SqlDataSourceStatusEventArgs
object. Finally, it occured to me, I would simply set the Output
parameter for the SqlDataSource in the Selecting event handler.

protected void SQLDataSource5_Selected(
object sender,
SqlDataSourceStatusEventArgs e)
{
SqlDataSource5.SelectParameters["ValueName"].DefaultValue =
e.Command.Parameters["@ValueName"].Value.ToString();
}

I guess it was built this way to allow the developer to handle datatype
conversions in the Selected event handler and parameter setup in the
Selecting event handler.

Then when I was back in my lookup function, I could access the value of
the output paramter.

string valuename =
SqlDataSource5.SelectParameters["ValueName"].DefaultValue;

An alterative may have been to add the data source to the GridRow
ItemTemplate...however, since I was using an asp:label, I didn't see a
method to bind the output parameter to the label.