none
SQLDataSource Return OUTPUT Parameter to text box RRS feed

  • Question

  • I have a SQLDataSource called SQLGetArea on a web form.  The SQLDataSource is tied to a stored procedure called ApGetAreaCode

     

    CREATEPROC [dbo].[ApGetAreaCode]

    @AreaID int,

    @AreaCode Varchar(2) OUTPUT

    AS

    SET NOCOUNT ON

    SELECT AreaCode

    FROM LKPFunctionalArea

    WHERE AreaID = @AreaID

     

    On the web form I have two controls, one is a dropdownlist (ddlApprover) and another is a text box (txtArea).  The selected value from the drop down list is used as the input variable.  I want to use the txtArea as the destination of the output variable.

     

    This stored procedure works correctly from either MSSqlServer Management Studio and also works when I drop a Details View on the web form.  However, the txtArea.text only displays the output parameter's DEFAULT value I gave from within the properties of the SQLDataSource.  I've tried the following code:

     

    Protected Sub myData_Selected(ByVal sender As Object, _

    ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _

    Handles SQLGetAreaCode.Selected

    txtAreaCode.Text = e.Command.Parameters("@AreaCode").Value.ToString()

    End Sub

     

    The above code only results in the output parameter's default value, as given in the properties of the SQLDataSource (SQLGetAreaCode).

     

    How do I get the @AreaCode value into the text box?

     

     

    Monday, June 18, 2007 3:15 PM

Answers

  • Problem solved!  It was my stored procedure causing the problem.  I should have set the variable, like as follows:

     

    SET @Area =

    (select area

    from table

    where area = @area

    )

     

     

    Monday, June 18, 2007 7:15 PM

All replies

  • It sounds like the parameter is not bound correctly.  If you bind as input only then the output value will not be fetched.

    You need to bind as output (I think this is SqlParameterDirection.Output).

    Monday, June 18, 2007 4:25 PM
  • Here are my parameters in ASP.net

     

    <SelectParameters>

    <asp:ControlParameter

    ControlID="txtAreaID"

    Name="AreaID"

    PropertyName="Text"

    Type="Int32" DefaultValue="" />

    <asp:Parameter

    Direction="InputOutput"

    Name="AreaCode"

    Type="String" DefaultValue="M" />

    </SelectParameters>

     
    When I change the direction of  the "AreaCode" parameter to Output, nothing shows up at all in the text box.  With the direction of "InputOutput", the default value appears.

     

    Monday, June 18, 2007 4:39 PM
  • I believe the parameter direction is okay because the details view that I put on the form - bound to this SQLDataSource - does display the correct information.

     

    Monday, June 18, 2007 4:44 PM
  • Problem solved!  It was my stored procedure causing the problem.  I should have set the variable, like as follows:

     

    SET @Area =

    (select area

    from table

    where area = @area

    )

     

     

    Monday, June 18, 2007 7:15 PM