469,358 Members | 1,639 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,358 developers. It's quick & easy.

get an output value from a stored procedure using sqlDataSource

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.SqlDataSourceStatusEvent Args) Handles
eUserDataSource.Selected

Dim param As System.Data.SqlClient.SqlParameter
For Each param In e.Command.Parameters
Response.Write(Server.HtmlEncode(param.ParameterNa me) &
"=")
Response.Write(Server.HtmlEncode(param.Value) & " (")
Response.Write(Server.HtmlEncode(param.Value.GetTy pe().ToString()) &
")<br />")
Next

End Sub

Feb 9 '06 #1
3 11106
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.googlegr oups.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.SqlDataSourceStatusEvent Args) Handles
eUserDataSource.Selected

Dim param As System.Data.SqlClient.SqlParameter
For Each param In e.Command.Parameters
Response.Write(Server.HtmlEncode(param.ParameterNa me) &
"=")
Response.Write(Server.HtmlEncode(param.Value) & " (")
Response.Write(Server.HtmlEncode(param.Value.GetTy pe().ToString()) &
")<br />")
Next

End Sub

Feb 10 '06 #2
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

Feb 10 '06 #3

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
************************************************** **************


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


Feb 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Steve Holden | last post: by
4 posts views Thread by laurenq uantrell | last post: by
4 posts views Thread by =?Utf-8?B?QmFidU1hbg==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.