get an output value from a stored procedure using sqlDataSource

9 Feb 2006 9:13 PM

michelle

I am trying to get an output value from a stored procedure using
sqlDataSource in asp.net 2.0. But I only get a null value for the
output. Can someone please help?

The sqlDataSource:

<asp:SqlDataSource ID="DataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings: ConnectionString1 %>"
SelectCommand="UserLkp" SelectCommandType="StoredProcedure"

>

            <SelectParameters>
                  <asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Direction="InputOutput" Name="Role" Type="String" />
</SelectParameters>
        </asp:SqlDataSource>

My stored Procedure is

CREATE PROCEDURE [dbo].[UserLkp]
@Hawkid varchar(30),
@eRole varchar(50) OUTPUT
as
select @eRole=eRole from eUsers Where eUser=@Hawkid
GO

I am using selected event of dataSource1 as in the following:

Protected Sub DataSource1_Selected(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
eUserDataSource.Selected

        Dim param As System.Data.SqlClient.SqlParameter
        For Each param In e.Command.Parameters
            Response.Write(Server.HtmlEncode(param.ParameterName) &
"=")
            Response.Write(Server.HtmlEncode(param.Value) & " (")

Response.Write(Server.HtmlEncode(param.Value.GetType().ToString()) &
")<br />")
        Next

    End Sub

10 Feb 2006 12:30 AM

Bruce Barker

use sql profiler to see what parameter value is passed for @Hawkid.

-- bruce (sqlwork.com)

Show quoteHide quote

"michelle" <rong***@yahoo.com> wrote in message
news:1139519586.708828.129290@g43g2000cwa.googlegroups.com...
>I am trying to get an output value from a stored procedure using
> sqlDataSource in asp.net 2.0. But I only get a null value for the
> output. Can someone please help?
>
>
> The sqlDataSource:
>
> <asp:SqlDataSource ID="DataSource1" runat="server"
> ConnectionString="<%$ ConnectionStrings: ConnectionString1 %>"
>            SelectCommand="UserLkp" SelectCommandType="StoredProcedure"
>>
>            <SelectParameters>
>                  <asp:Parameter Name="UserID" Type="String" />
> <asp:Parameter Direction="InputOutput" Name="Role" Type="String" />
> </SelectParameters>
>        </asp:SqlDataSource>
>
> My stored Procedure is
>
> CREATE PROCEDURE [dbo].[UserLkp]
> @Hawkid varchar(30),
> @eRole varchar(50) OUTPUT
> as
> select @eRole=eRole from eUsers Where eUser=@Hawkid
> GO
>
> I am using selected event of dataSource1 as in the following:
>
> Protected Sub DataSource1_Selected(ByVal sender As Object, ByVal e As
> System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
> eUserDataSource.Selected
>
>        Dim param As System.Data.SqlClient.SqlParameter
>        For Each param In e.Command.Parameters
>            Response.Write(Server.HtmlEncode(param.ParameterName) &
> "=")
>            Response.Write(Server.HtmlEncode(param.Value) & " (")
>
>
> Response.Write(Server.HtmlEncode(param.Value.GetType().ToString()) &
> ")<br />")
>        Next
>
>    End Sub
>

10 Feb 2006 4:02 PM

michelle

Hi Bruce,

I took out the input parameter and gave it a value, it still does not
work.

CREATE PROCEDURE [dbo].[UserLkp]
@eRole varchar(50) OUTPUT
as
select @eRole=eRole from eUsers Where eUser=eee'
GO

14 Feb 2006 2:31 PM

Tapio Kulmala

Change your parameter name into "eRole" and try again.

<asp:Parameter Direction="InputOutput" Name="eRole" Type="String" />

****************************************************************
Tapio Kulmala

"Those are my principles. If you don't like them I have others."

- Groucho Marx
****************************************************************

Show quoteHide quote

>
>
> The sqlDataSource:
>
> <asp:SqlDataSource ID="DataSource1" runat="server"
> ConnectionString="<%$ ConnectionStrings: ConnectionString1 %>"
>             SelectCommand="UserLkp" SelectCommandType="StoredProcedure"
> >
>             <SelectParameters>
>                   <asp:Parameter Name="UserID" Type="String" />
> <asp:Parameter Direction="InputOutput" Name="Role" Type="String" />
> </SelectParameters>
>         </asp:SqlDataSource>
>
> My stored Procedure is
>
> CREATE PROCEDURE [dbo].[UserLkp]
> @Hawkid varchar(30),
> @eRole varchar(50) OUTPUT
> as
> select @eRole=eRole from eUsers Where eUser=@Hawkid
> GO
>

Thread options