100分求解如下程序调用存储过程
前台登陆LOGIN.ASPX
用户名:txtUserName
密码:txtUserPass
数据库中结构:
uid ===表自增字段
UserName ===用户名 ===varchar(50)
UserPass ===密码 ===varchar(50)
Flag ===是否通过(默认是0,也就是被锁定的用户,不能登陆,1是正常,可以登陆) ==int类型
存储过程如下(网上找来的,语法检查通过)
-1－用户不存在，-２－密码错误，-３－此帐号被锁
CREATE PROCEDURE tp_login
@txtUserName varchar(50),
@txtUserPass varchar(50),
@out varchar(50)
AS
declare @key int
set @key=0/* 0－此帐号被锁*/
if not exists(select * from UserList where UserName=@txtUserName)
begin
set @out=-1
return @out
end
else
begin
if not exists(select * from UserList where UserName=@txtUserName and UserPass=@txtUserPass)
begin
set @out=-2
return @out
end
else
begin
if exists(select * from UserList where UserName=@txtUserName and UserPass=@txtUserPass and Flag=@key)
begin
set @out=-3
return @out
end
else
select @out=uid from UserList where UserName=@txtUserName and UserPass=@txtUserPass
return @out
end
end
GO
LOGIN.ASP.CS文件中,如何调用该存储过程?
我是这样写的,但写到那里写不下去了,不会写了,望大哥们帮忙
string txtUserName = Request.Form[ "txtUserName "].ToString().Trim();
string txtUserPass = Request.Form[ "txtUserPass "].ToString().Trim();
SqlConnection conn=GlassDB.createConnection();
conn.Open();
SqlCommand cmd=new SqlCommand( "tp_login ",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter( "@txtUserName ",txtUserName));
cmd.Parameters.Add(new SqlParameter( "@txtUserPass ",txtUserPass));
cmd.ExecuteNonQuery();
.......这里怎么返回给用户登陆的信息??上面写的对还是不对啊?
[解决办法]
cmd.Parameters.Add(new SqlParameter( "@txtUserPass ",txtUserPass));
SqlParameter parter = new SqlParameter( "@pageCount ",SqlDbType.VarChar,50);
parter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parter);
cmd.ExecuteNonQuery();
string outID = parter.Value;
[解决办法]
string ReturnValue = " ";
cmd.Parameters.Add(new SqlParameter( "@out ",ReturnValue,ParameterDirection.Output));
[解决办法]
在查询分析器里面执行
declare @tmp varchar(50)
exec @tmp=tp_login 参数1，参数2，参数3
你就明白了
或者直接设置output参数
[解决办法]
给你看个例子:http://www.5d.cn/Tutorial/webdevelop/.net/200412/1960.html
[解决办法]
[解决办法]
up~~~~~~~~~~~~
[解决办法]
前面没有测试,有些笔误,现在下班了,仔细看了一下:
方案1:
存储过程:
-------------
ALTER PROCEDURE tp_login
@txtUserName varchar(50),
@txtUserPass varchar(50),
@out varchar(50) output
AS
declare @key int
set @key=0/* 0－此帐号被锁*/
if not exists(select * from UserList where UserName=@txtUserName)
set @out=-1
else
begin
if not exists(select * from UserList where UserName=@txtUserName and UserPass=@txtUserPass)
set @out=-2
else
begin
if exists(select * from UserList where UserName=@txtUserName and UserPass=@txtUserPass and Flag=@key)
set @out=-3
else
select @out=uid from UserList where UserName=@txtUserName and UserPass=@txtUserPass
end
end
----------------------------------
页面代码:
string txtUserName = Request.Form[ "txtUserName "].ToString().Trim();
string txtUserPass = Request.Form[ "txtUserPass "].ToString().Trim();
SqlConnection conn=GlassDB.createConnection();
conn.Open();
SqlCommand cmd=new SqlCommand( "tp_login ",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter( "@txtUserName ",txtUserName));
cmd.Parameters.Add(new SqlParameter( "@txtUserPass ",txtUserPass));
//输出参数
cmd.Parameters.Add(new SqlParameter( "@out ",SqlDbType.Int,4));
cmd.Parameters[ "@out "].Direction=ParameterDirection.Output;
cmd.ExecuteNonQuery();
//获取输出参数的值
int result =(int)cmd.Parameters[ "@out "].Value;
conn.Close();
...
方案2:
存储过程:
------------------
ALTER PROCEDURE tp_login
@txtUserName varchar(50),
@txtUserPass varchar(50)
AS
declare @key int
set @key=0/* 0－此帐号被锁*/
declare @out varchar(50)
if not exists(select * from UserList where UserName=@txtUserName)
begin
set @out=-1
end
else
begin
if not exists(select * from UserList where UserName=@txtUserName and UserPass=@txtUserPass)
begin
set @out=-2
end
else
begin
if exists(select * from UserList where UserName=@txtUserName and UserPass=@txtUserPass and Flag=@key)
begin
set @out=-3
end
else
select @out=uid from UserList where UserName=@txtUserName and UserPass=@txtUserPass
end
end
return @out
---------------------
页面代码:
string txtUserName = Request.Form[ "txtUserName "].ToString().Trim();
string txtUserPass = Request.Form[ "txtUserPass "].ToString().Trim();
SqlConnection conn=GlassDB.createConnection();
conn.Open();
SqlCommand cmd=new SqlCommand( "tp_login ",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter( "@txtUserName ",txtUserName));
cmd.Parameters.Add(new SqlParameter( "@txtUserPass ",txtUserPass));
// 返回值
cmd.Parameters.Add(new SqlParameter( "Returnvalue ",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null ));
cmd.ExecuteNonQuery();
//获取返回值
int result =(int)cmd.Parameters[ "Returnvalue "].Value;
conn.Close();
...
