get an output value from a stored procedure using sqlDataSource

[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:SqlDataSou rce ID="DataSource1 " runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs: ConnectionStrin g1 %>"
SelectCommand=" UserLkp" SelectCommandTy pe="StoredProce dure"

<SelectParamete rs>
<asp:Paramete r Name="UserID" Type="String" />
<asp:Paramete r Direction="Inpu tOutput" Name="Role" Type="String" />
</SelectParameter s>
</asp:SqlDataSour ce>

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_Sel ected(ByVal sender As Object, ByVal e As
System.Web.UI.W ebControls.SqlD ataSourceStatus EventArgs) Handles
eUserDataSource .Selected

Dim param As System.Data.Sql Client.SqlParam eter
For Each param In e.Command.Param eters
Response.Write( Server.HtmlEnco de(param.Parame terName) &
"=")
Response.Write( Server.HtmlEnco de(param.Value) & " (")
Response.Write( Server.HtmlEnco de(param.Value. GetType().ToStr ing()) &
")<br />")
Next

End Sub

[Bruce Barker]

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

-- bruce (sqlwork.com)
"michelle" <ro*****@yahoo. com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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:SqlDataSou rce ID="DataSource1 " runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs: ConnectionStrin g1 %>"
SelectCommand=" UserLkp" SelectCommandTy pe="StoredProce dure"

<SelectParamete rs>
<asp:Paramete r Name="UserID" Type="String" />
<asp:Paramete r Direction="Inpu tOutput" Name="Role" Type="String" />
</SelectParameter s>
</asp:SqlDataSour ce>

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_Sel ected(ByVal sender As Object, ByVal e As
System.Web.UI.W ebControls.SqlD ataSourceStatus EventArgs) Handles
eUserDataSource .Selected

Dim param As System.Data.Sql Client.SqlParam eter
For Each param In e.Command.Param eters
Response.Write( Server.HtmlEnco de(param.Parame terName) &
"=")
Response.Write( Server.HtmlEnco de(param.Value) & " (")
Response.Write( Server.HtmlEnco de(param.Value. GetType().ToStr ing()) &
")<br />")
Next

End Sub

[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

[Tapio Kulmala]

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

<asp:Paramete r Direction="Inpu tOutput" Name="eRole" Type="String" />
*************** *************** *************** *************** ****
Tapio Kulmala

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

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

The sqlDataSource:

<asp:SqlDataSou rce ID="DataSource1 " runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs: ConnectionStrin g1 %>"
SelectCommand=" UserLkp" SelectCommandTy pe="StoredProce dure"

<SelectParamete rs>
<asp:Paramete r Name="UserID" Type="String" />
<asp:Paramete r Direction="Inpu tOutput" Name="Role" Type="String" />
</SelectParameter s>
</asp:SqlDataSour ce>

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