如何在asp.net设计页面中传递store procedure参数 [英] How to pass the store procedure parameter in asp.net design page
问题描述
我的商店程序如下
My store procedure as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OH_Room_Allocation_SMS]
@session VARCHAR (2), @date VARCHAR (15), @type int
AS
BEGIN
DECLARE @stud_name AS VARCHAR (100),
@Mob_num AS VARCHAR (15),
@Course AS VARCHAR (50),
@Batch_id AS VARCHAR (20),
@RoomNo AS INT,
@Sess AS VARCHAR (10),
@RoomFloor AS VARCHAR (15),
@CrsTime varchar(10),
@PreviousRoomNo AS INT;
CREATE TABLE #TempTable
(
stud_name VARCHAR (100),Mob_num VARCHAR (15),Course VARCHAR (50),
Batch_id VARCHAR (50),RoomNo VARCHAR (20),Sess VARCHAR (10),RoomFloor VARCHAR (15),Timings varchar(10)
);
BEGIN TRANSACTION;
DECLARE Rooms CURSOR
FOR SELECT bthid,
minor_code,
RoomNo,
Sess
FROM TB_Room_Allocation_SMS
WHERE active <> 'D'
AND Sess = @session
AND Dateofcrs = @date
ORDER BY RoomNo;
OPEN Rooms;
FETCH NEXT FROM Rooms INTO @Batch_id, @Course, @RoomNo, @Sess;
WHILE @@Fetch_status = 0
BEGIN
SET @PreviousRoomNo = 0;
SELECT @PreviousRoomNo = roomno
FROM TB_Room_Allocation_SMS
WHERE active <> 'D'
AND Sess = @session
AND bthid = @Batch_id
AND Dateofcrs < @date
ORDER BY DateOfCrs;
PRINT @PreviousRoomNo;
select @CrsTime = cbm_batch_start_time from co_batch_master where cbm_active <> 'D' and cbm_batch_id = @Batch_id
IF @RoomNo <> @PreviousRoomNo
BEGIN
DECLARE Studdetails CURSOR
FOR SELECT s.stud_name,
CASE rtrim(isnull(s.stud_mobile, ''))
WHEN '' THEN rtrim(s.stud_telephone) ELSE rtrim(s.stud_mobile)
END AS MobileNo
FROM course_registration AS cr, batch_course_registration AS bcr, student AS s
WHERE cr.stud_id = s.stud_id
AND bcr.cr_bill_no = cr.cr_bill_no
AND cr.cr_active = 'A'
AND s.stud_active <> 'D'
AND bcr.bcr_batch_id = @Batch_id;
set @RoomFloor = ''
if @RoomNo = '11'
set @RoomFloor = '1stFloor'
if @RoomNo = '12'
set @RoomFloor = '1stFloor'
if @RoomNo = '13'
set @RoomFloor = '1stFloor'
if @RoomNo = '14'
set @RoomFloor = '1stFloor'
if @RoomNo = '21'
set @RoomFloor = '2ndFloor'
if @RoomNo = '22'
set @RoomFloor = '2ndFloor'
if @RoomNo = '23'
set @RoomFloor = '2ndFloor'
if @RoomNo = '24'
set @RoomFloor = '2ndFloor'
if @RoomNo = '31'
set @RoomFloor = '3rdFloor'
if @RoomNo = '32'
set @RoomFloor = '3rdFloor'
if @RoomNo = '33'
set @RoomFloor = '3rdFloor'
if @RoomNo = '34'
set @RoomFloor = '3rdFloor'
if @RoomNo = '41'
set @RoomFloor = '4thFloor'
if @RoomNo = '42'
set @RoomFloor = '4thFloor'
--if @RoomNo = 'Conference'
--Set @RoomFloor = 'Ground Floor'
if (@Course = 'REO' or @Course = 'REO_C' or @Course = 'RM' or @Course = 'REO-O' or @Course = 'REO-O_C')
begin
set @CrsTime = '08:30'
end
OPEN Studdetails;
FETCH NEXT FROM Studdetails INTO @stud_name, @Mob_num;
WHILE @@Fetch_status = 0
BEGIN
if (len(ltrim(rtrim(@Mob_num))) > 9) and @Mob_num <> '' and @Mob_num <> 'NULL'
begin
if (@Mob_num <> '9380244904')
begin
INSERT INTO #TempTable VALUES (@stud_name, @Mob_num, @Course, @Batch_id, @RoomNo, @Sess, @RoomFloor,@CrsTime);
end
end
FETCH NEXT FROM Studdetails INTO @stud_name, @Mob_num;
END
CLOSE Studdetails;
DEALLOCATE Studdetails;
END
FETCH NEXT FROM Rooms INTO @Batch_id, @Course, @RoomNo, @Sess;
END
CLOSE Rooms;
DEALLOCATE Rooms;
COMMIT TRANSACTION;
if @type = 0
begin
SELECT *
FROM #TempTable;
end
else
begin
select distinct convert(varchar(12),cbm_batch_start_dt,106) as Crs_Date,b.cmn_minor_code,a.roomno,b.cbm_batch_start_time as Timings from #TempTable a,co_batch_master b where a.Batch_id =b.cbm_batch_id
end
END
当我执行商店程序如下
在以下商店程序中我传递参数session，date， 0
OH_Room_Allocation_SMS'AM'，'11-13-2012'，0
when i execute the store procedue as follows
in below store procedure i pass the parameter session,date,0
OH_Room_Allocation_SMS 'AM','11-13-2012',0
R . VIJAYA 9626447077 REO B10755 12 AM 1stFloor 08:30
R.NARAYA 9150399363 REO B10755 12 AM 1stFloor 08:30
in下面的商店程序我通过参数会话，日期，1
OH_Room_Allocation_SMS'AM'，'11-13-2012'，1
in below store procedure i pass the parameter session,date,1
OH_Room_Allocation_SMS 'AM','11-13-2012',1
12 Nov 2012 REO 12 7.15
17 Sep 2012 PH1 42 7.15
$ b运行模式下的$ b如下所示
运行模式中的
我有一个名为send sms的按钮当我点击发送网格视图将在该网格视图中打开我希望输出如下（storeprocedure参数会话，日期，1）
in run mode as follows
in runmode i have one button called send sms when i click the sendsms gridview will open in that gridview i want output as follows (storeprocedure parameter session,date,1)
12 Nov 2012 REO 12 7.15
17 Sep 2012 PH1 42 7.15
在该gridview我有一个名为ok的按钮。当我点击要执行的确定按钮存储过程功能时。
发送按钮代码如下
in that gridview i have one button called ok. when i click the ok button store procedure function to be executed.
sendsms button code as follows
protected void sendsms_Click(object sender, EventArgs e)
{
string date = Convert.ToDateTime(currentDate.SelectedDateValue).ToString("MM-dd-yyyy");
SMSDetails(ddlSession.SelectedItem.Text, date);
lblUpdate.Text = "SMS Send Successfully";
gvshowcourse.Visible = true;
}
我怎么能在asp.net上做什么？
问候，
narasiman P.
for that how can i do in asp.net?
regards,
narasiman P.
推荐答案
看看这里：
如何使用Visual Basic .NET在ASP.NET中调用SQL Server存储过程 [ ^ ] - 这是VB.NET，但想法是一样的。
有关详细信息，请访问以下链接：如何：执行返回行的存储过程 [ ^ ]
Have a look here:
How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET[^] - it's VB.NET, but the idea is the same.
For further information, follow this link: How to: Execute a Stored Procedure that Returns Rows[^]
这篇关于如何在asp.net设计页面中传递store procedure参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
