get an output value from a stored procedure using sqlDataSource

M

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"
<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
 
B

Bruce Barker

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

-- bruce (sqlwork.com)


michelle said:
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"
<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
 
M

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
 
T

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
****************************************************************
The sqlDataSource:

<asp:SqlDataSource ID="DataSource1" runat="server"
<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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top