Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 30th, 2008, 01:57 PM
Authorized User
 
Join Date: Aug 2008
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Wink how to pass a control parameter and process a return parameter in sqldatasource

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>
&nbsp;
</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>

Last edited by jayshankar; December 30th, 2008 at 01:59 PM..
 
Old January 1st, 2009, 10:18 AM
Authorized User
 
Join Date: Aug 2008
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i am waiting for the reply.Plz reply guys . i am really confused.





Similar Threads
Thread Thread Starter Forum Replies Last Post
pass parameter naeem_ul_hussan Classic ASP Basics 2 August 30th, 2007 11:18 AM
SqlDataSource Input parameter sg225551 ASP.NET 2.0 Basics 0 January 16th, 2007 10:54 AM
sqldatasource parameter and databinding help binici ASP.NET 2.0 Basics 1 December 5th, 2006 05:23 PM
How to return an output parameter dyaneshwaran SQL Language 0 January 12th, 2006 05:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.