I want to call a stored procedure ( sql server 2005 ) passing a control parameter and the procedure returns a value. I am using a sql data source control. the problem is the stored procedure has two select statements.
one returns complete data from the table employee using name of an employee in the where clause
that we pass using control parameter and display it in a gridview.names of employee you can select from a drop down list.the other select statement
calculates da of employee and returns it using a return parameter.
the program works if only first second statement(da calculation) is used
this is some logical error i think.
please help me.
following is the code
the database name is test and has procedure called getda.
scripts are as follows
CREATE procedure [dbo].[getda]
@name varchar(50)
as
begin
declare @da int
select @da= ((salary * 10) /100) from employee
where name = @name
select * from employee where name = @name
return @da
end
-------------------------------------------------------------------
this is the code for employee table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[employee](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[salary] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------------------------
code on aspx.cs page
protected void getda(object sender, SqlDataSourceStatusEventArgs e)
{
string parameter;
// Label3.Text = e.Command.Parameters[0].ParameterName;
// Label4.Text = e.Command.Parameters[1].ParameterName;
TextBox2.Text = e.Command.Parameters[0].Value.ToString();
TextBox3.Text = e.Command.Parameters[1].Value .ToString();
foreach (SqlParameter param in e.Command.Parameters)
{
parameter = param.Value.ToString();
parameter = parameter.Trim();
if (parameter.Equals("0") || parameter.Equals("select one"))
{
}
else
{
TextBox1.Text = param.Value.ToString();
}
}
}
--------------------------------------------------------------
code on aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="width: 100%">
<tr>
<td style="width:25%">
<asp:SqlDataSource ID="sql1" runat="server"
ConnectionString="<%$ConnectionStrings:connection %>"
SelectCommandType="Text"
SelectCommand="select * from employee"
ProviderName="System.Data.SqlClient"
>
</asp:SqlDataSource>
</td>
<td style="width: 25%">
<asp:DropDownList ID="DropDownList1" runat="server"
AppendDataBoundItems="true"
AutoPostBack="true"
DataSourceID="sql1"
DataTextField="name"
DataValueField="id"
>
<asp:ListItem Selected="False" > select one </asp:ListItem>
</asp:DropDownList></td>
<td style="width: 25%">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></td>
<td style="width: 25%">
</td>
</tr>
<tr>
<td style="width: 25%">
</td>
<td style="width: 25%">
<asp:SqlDataSource ID="sql2" runat="server"
ConnectionString="<%$ConnectionStrings:connection %>"
DataSourceMode="DataReader"
SelectCommandType="StoredProcedure"
SelectCommand="getda" OnSelected="getda"
>
<SelectParameters>
<asp:ControlParameter DefaultValue="selected" ControlID="dropdownlist1"
Name="name"
PropertyName="SelectedItem.Text"
/>
<asp:Parameter Direction="ReturnValue" Name="da" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</td>
<td style="width: 25%">
</td>
<td style="width: 25%">
<asp:GridView ID="GridView1" runat="server" DataSourceId="sql2">
</asp:GridView>
</td>
</tr>
<tr>
<td style="width: 25%">
<asp:Label ID="Label1" runat="server" Text="passed parameter"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td>
<td style="width: 25%">
<asp:Label ID="Label2" runat="server" Text="return parameter"></asp:Label>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox></td>
<td style="width: 25%">
</td>
<td style="width: 25%">
</td>
</tr>
<tr>
<td style="width: 25%">
</td>
<td style="width: 25%">
</td>
<td style="width: 25%">
</td>
<td style="width: 25%">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
how to pass a control parameter and process a return parameter in sqldatasource
