<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!--#include file="Include.inc" -->
<%
dim UserName,PassWord
UserName=replace(trim(request("UserName")),"'","") '获取用户输入的用户名和密码
PassWord=replace(trim(Request("PassWord")),"'","")
If UserName="" or PassWord="" Then '判断用户名和密码不能为空
Call MyMessage("请输入用户名和密码") '如果用户名和密码为空提示并返回
response.End()
End if
%>
<%
Dim conn,cmd,userSort,rptCall OpenConn() '建立数据库连接
Set cmd=server.CreateObject("adodb.command")
cmd.ActiveConnection=conn
cmd.CommandType=adCmdStoredProc
cmd.CommandText="pro_UserCheck"
cmd.Parameters.Append Cmd.CreateParameter("@userID",advarchar,adParamInput,50)
cmd.Parameters.Append Cmd.CreateParameter("@passWord",advarchar,adParamInput,50)
cmd.Parameters.Append Cmd.CreateParameter("@Sort",adInteger,adParamInput,4)
cmd.Parameters.Append Cmd.CreateParameter("@addr",advarchar,adParamOutPut,200)
cmd.Parameters.Append Cmd.CreateParameter("@phone",advarchar,adParamOutPut,200)
cmd.Parameters.Append Cmd.CreateParameter("@userSort",adInteger,adParamOutPut,4)
cmd.Parameters.Append Cmd.CreateParameter("@userState",adInteger,adParamOutPut,4)
cmd.Parameters.Append Cmd.CreateParameter("@rpt",adInteger,adParamOutPut,4)
cmd.Parameters("@userID").Value=UserName
cmd.Parameters("@passWord").Value=PassWord
cmd.Parameters("@Sort").Value=2
cmd.Execute
rpt=Cint(cmd.Parameters("@rpt").Value)
If rpt=0 Then
Call MyMessage("用户名密码错误或用户未被激活") '如果用户不存在或者被禁用弹出对话框提示
Response.End()
ElseIf rpt=1 Then '登陆成功
userState=Clng(cmd.Parameters("@userState").Value)
userSort=Clng(cmd.Parameters("@userSort").Value)
'If userState=2 Then
Session("KeyUserID")=UserName
Session("KeyPassWord")=PassWord
Session("KeyUserSort")=userSort
Session("KeyMySession")=MySession
Response.Redirect request.serverVariables("Http_REFERER")
'ElseIf userState=1 Then
'Session("UserID")=UserName
'Response.Redirect"Register.asp"
'Else
'Call MyMessage("用户名密码错误或者用户已被禁用") '如果用户不存在或者被禁用弹出对话框提示
'Response.End()
'End If
Else
Call MyMessage("错误")
Response.End()
End IfCall CloseConn() '关闭数据库连接
%>这是调用存储过程的ASP页面
<!--#include file="Include.inc" -->
<%
dim UserName,PassWord
UserName=replace(trim(request("UserName")),"'","") '获取用户输入的用户名和密码
PassWord=replace(trim(Request("PassWord")),"'","")
If UserName="" or PassWord="" Then '判断用户名和密码不能为空
Call MyMessage("请输入用户名和密码") '如果用户名和密码为空提示并返回
response.End()
End if
%>
<%
Dim conn,cmd,userSort,rptCall OpenConn() '建立数据库连接
Set cmd=server.CreateObject("adodb.command")
cmd.ActiveConnection=conn
cmd.CommandType=adCmdStoredProc
cmd.CommandText="pro_UserCheck"
cmd.Parameters.Append Cmd.CreateParameter("@userID",advarchar,adParamInput,50)
cmd.Parameters.Append Cmd.CreateParameter("@passWord",advarchar,adParamInput,50)
cmd.Parameters.Append Cmd.CreateParameter("@Sort",adInteger,adParamInput,4)
cmd.Parameters.Append Cmd.CreateParameter("@addr",advarchar,adParamOutPut,200)
cmd.Parameters.Append Cmd.CreateParameter("@phone",advarchar,adParamOutPut,200)
cmd.Parameters.Append Cmd.CreateParameter("@userSort",adInteger,adParamOutPut,4)
cmd.Parameters.Append Cmd.CreateParameter("@userState",adInteger,adParamOutPut,4)
cmd.Parameters.Append Cmd.CreateParameter("@rpt",adInteger,adParamOutPut,4)
cmd.Parameters("@userID").Value=UserName
cmd.Parameters("@passWord").Value=PassWord
cmd.Parameters("@Sort").Value=2
cmd.Execute
rpt=Cint(cmd.Parameters("@rpt").Value)
If rpt=0 Then
Call MyMessage("用户名密码错误或用户未被激活") '如果用户不存在或者被禁用弹出对话框提示
Response.End()
ElseIf rpt=1 Then '登陆成功
userState=Clng(cmd.Parameters("@userState").Value)
userSort=Clng(cmd.Parameters("@userSort").Value)
'If userState=2 Then
Session("KeyUserID")=UserName
Session("KeyPassWord")=PassWord
Session("KeyUserSort")=userSort
Session("KeyMySession")=MySession
Response.Redirect request.serverVariables("Http_REFERER")
'ElseIf userState=1 Then
'Session("UserID")=UserName
'Response.Redirect"Register.asp"
'Else
'Call MyMessage("用户名密码错误或者用户已被禁用") '如果用户不存在或者被禁用弹出对话框提示
'Response.End()
'End If
Else
Call MyMessage("错误")
Response.End()
End IfCall CloseConn() '关闭数据库连接
%>这是调用存储过程的ASP页面
解决方案 »
- inner join 在数据量大时的查询优化问题?
- 怎样理解Buffer hit ratio 是99%, 但Page life expectancy<200?
- 锁住表问题------------大家帮帮忙
- 数据库方向上的9种职业(与大家共勉兼散分)
- 如何仅用SQL select查询语句,将十进制整数转换成16进制整数或字符串?
- 当多个客户端程序同时对同一条记录进行修改时,大家是怎样处理这种情况?
- 数据类型的转换--在线等候,解决后马上给分
- 重新装的系统,附加sqlserver2005数据库出错,请教下高手,是怎么回事啊?
- 是否有其他更好的方式可以代替SQLMail+JOB自动发邮件?
- 如何用TCP/IP访问sql server
- 关于时间段查询问题
- 检索多个字段并分组时group by后面的字段太多导致结果不正确
Fun : 用户登陆验证.若密码加密,则由客户端实现
Author : **
CreateTime : 2005-12-23
*/CREATE PROCEDURE pro_UserCheck
@userID as varchar(50),
@passWord as varchar(50),
@sort as int,
@addr as varchar output,
@phone as int output,
@userState as int output,
@userSort as int output,
@rpt as int output
--0为登陆失败,1为登陆成功
AS
Begin
Select @userSort = UserSort,@userState=UserState,@addr=Addr,@phone=Phone
From tbl_UserInfo
Where UserID = @userID
And [Password] = @passWord
And UserState = 2 or UserState = 1 --2为通过审核并且帐号启用
--And userSort=@sort
And userSort<>3
IF @@rowcount <=0
Begin
Set @rpt=0 --0为登录失败
End
Else
Begin
IF ISNULL(@addr,'') = '' or ISNULL(@phone,'') = ''
Begin
Set @rpt=1
update tbl_userInfo set userstate=2 Where UserID = @userID End
End
End
End
GO
@userID as varchar(50),
@passWord as varchar(50),
@sort as int,
@addr as varchar output,
@phone as int output,
@userState as int output,
@userSort as int output,
@rpt as int output --0为登陆失败,1为登陆成功AS
Begin
Select
@userSort = UserSort,
@userState=UserState,
@addr=Addr,
@phone=Phone
From tbl_UserInfo
Where UserID = @userID
And [Password] = @passWord
And (UserState = 2 or UserState = 1) --#1.加上括号 --2为通过审核并且帐号启用
--And userSort=@sort
And userSort<>3 IF @@rowcount <=0 --#1.可以不用@@rowcount,不过用也没关系
Begin
Set @rpt=0 --0为登录失败
End
Else
Begin
IF (ISNULL(@addr,'') = '' or ISNULL(@phone,'') = '')
Begin
Set @rpt=0
End
Else
Begin
update tbl_userInfo set userstate=2 Where UserID = @userID
Set @rpt=1
End
End
End
GO--#3.问题应该出现在你的代码中,output参数.没有指定:ParameterDirection.Output
SqlParameter sampParm = cmd.Parameters.Append Cmd.CreateParameter("@addr",advarchar,adParamOutPut,200)
sampParm.Direction = ParameterDirection.Output;
Begin
Set @rpt=0
改为IF ISNULL(@addr,'') = '' or ISNULL(@phone,'') = ''
Begin
Set @rpt=1 时,发现能登录成功,这说明IF ISNULL(@addr,'') = '' or ISNULL(@phone,'') = '' 并没有起到判断作用。
IF ISNULL(@addr,'') = '' or ISNULL(@phone,'') = ''
这句之前print @addr+@phone看看是什么值
UserState = 2 or UserState = 1 上加上 () 是不是你要的结果呢?
Select @userSort = UserSort,@userState=UserState,@addr=Addr,@phone=Phone
From tbl_UserInfo
Where UserID = @userID
And [Password] = @passWord
And (UserState = 2 or UserState = 1) --2为通过审核并且帐号启用
--And userSort=@sort
And userSort<>3
2、你确认:
a、Addr、Phone只要有一个为空就不能登录时被失败?Select @userSort = UserSort,@userState=UserState,@addr=Addr,@phone=Phone
From tbl_UserInfo
Where UserID = @userID
And [Password] = @passWord
And (UserState = 2 or UserState = 1) --2为通过审核并且帐号启用
--And userSort=@sort
And userSort<>3
And '' <> isnull(Addr, '')
And '' <> isnull(Phone, '')
if @@rowcount <=0
begin
Set @rpt=0 --0为登录失败
end
else
begin
update tbl_userInfo set userstate=2 Where UserID = @userID
Set @rpt=1
end
IF (ISNULL(@addr,'') = '' or ISNULL(@phone,'') = '' )这样?
不行就换个思路,
if (len(@addr)>0 or len(@phone)>0)