系统报错:为过程或函数proc_UserLogin指定了过多的参数。
我用得数据库是SQL 2005,存储过程是这样写的,编译通过的
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go/*--创建存储过程--*/
ALTER procedure [dbo].[proc_UserLogin]
@username varchar(20) output,
@userpassword varchar(20) output
as
select * from Users where LoginId=@username and LoginPwd=@userpassword
godeclare @name varchar(20),@password varchar(20)
exec proc_UserLogin @name output,@password output以前用的SQL 2000也是这样写的,在程序里调用是通过的。
我用得数据库是SQL 2005,存储过程是这样写的,编译通过的
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go/*--创建存储过程--*/
ALTER procedure [dbo].[proc_UserLogin]
@username varchar(20) output,
@userpassword varchar(20) output
as
select * from Users where LoginId=@username and LoginPwd=@userpassword
godeclare @name varchar(20),@password varchar(20)
exec proc_UserLogin @name output,@password output以前用的SQL 2000也是这样写的,在程序里调用是通过的。
ALTER procedure [dbo].[proc_UserLogin]
@username varchar(20) output,
@userpassword varchar(20) output
as
declare 条件 [条件类型],条件 [条件类型]
set 条件=赋值
set 条件=赋值
declare @username varchar(20),@userpassword varchar(20)
select @username = LoginId,@userpassword = LoginPwd from Users
where LoginId=条件 and LoginPwd=条件
go
declare @name varchar(20),@password varchar(20)
exec proc_UserLogin @name output,@password output
{
new SqlParameter("@username",u.Loginid),
new SqlParameter("@userpassword",u.Loginpwd)
};
ALTER procedure [dbo].[proc_UserLogin]
@username varchar(20) output,
@userpassword varchar(20) output
as
select * from Users where LoginId=@username and LoginPwd=@userpassword
go declare @name varchar(20),@password varchar(20)
exec proc_UserLogin @name output,@password output为何参数是output?
把output参数删了,然后改一下。
ALTER procedure [dbo].[proc_UserLogin]
@username varchar(20),
@userpassword varchar(20)
as
select * from Users where LoginId=@username and LoginPwd=@userpassword
go
@username varchar(20) ,
@userpassword varchar(20),
as
select * from Users
where LoginId=@username and LoginPwd=@userpassword
go
declare @name varchar(20),@password varchar(20)
exec proc_UserLogin @name ,@password
public class DataAccess
{
private SqlConnection objSqlConnection;
private SqlCommand objSqlCommand;
public DataAccess()
{
//连接数据库
try
{ objSqlConnection = new SqlConnection("server=.;uid=sa;pwd=sasa;database=MyBookShop;");
objSqlCommand = new SqlCommand("", objSqlConnection);
}
catch(Exception e)
{
string str = e.Message;
}
} //用户登录
public SqlDataReader Login(string procName, SqlParameter[] paras)
{
SqlDataReader objSqlDataReader = null;
try
{
objSqlConnection.Open();
objSqlCommand.CommandText = procName;
objSqlCommand.CommandType = CommandType.StoredProcedure;
if (paras != null)
{
objSqlCommand.Parameters.AddRange(paras);
}
objSqlDataReader = objSqlCommand.ExecuteReader();
}
catch (Exception e)
{
string str = e.Message;
}
return objSqlDataReader;
} }
//这是另外的一个类
public class UserService
{
private static DataAccess da = new DataAccess();
public UserService()
{ } public static bool Users_Login(Users u)
{
try
{
SqlParameter[] objSqlParameter = new SqlParameter[]
{
new SqlParameter("@username",u.Loginid),
new SqlParameter("@userpassword",u.Loginpwd)
};
SqlDataReader objSqlDataReader = da.Login("proc_UserLogin", objSqlParameter);
if (objSqlDataReader.Read())
{
return true;
} }
catch (Exception e)
{
string str = e.Message;
}
finally
{
da.GetClose();
}
return false;
}
查询语句:
select * from Users where LoginId=@username and LoginPwd=@userpassword
就可以返回一个table,为什么不直接从这个table中去取用户名和密码呢?
@username varchar(20) ,
@userpassword varchar(20),
as
--想明白为什么select 1或者select userid而不是select *
select 1 from Users
where LoginId=@username and LoginPwd=@userpassword
go
new SqlParameter("@username",SqlDbType.varchar,20),
new SqlParameter("@userpassword",SqlDbType.varchar,20)
我在登录页面测试了一下,第一次是成功的,然后都登录不了,用断点调试就报上面这个错。我的主要代码:
public static User GetUserByLoginId(string loginId)
{
int userRoleId;
int userStateId;
try
{ string sql = "select * from Users where LoginId=@LoginId";
using (SqlDataReader objSqlDataReader = da.GetReader(sql, new SqlParameter("@LoginId",loginId)))
{
if (objSqlDataReader != null)
if (objSqlDataReader.Read())
{
User user = new User();
user.Id = (int)objSqlDataReader["Id"];
user.Loginid = objSqlDataReader["LoginId"].ToString();
user.Loginpwd = objSqlDataReader["LoginPwd"].ToString();
user.Name = objSqlDataReader["Name"].ToString();
user.Address = objSqlDataReader["Address"].ToString();
user.Phone = objSqlDataReader["Phone"].ToString();
user.Mail = objSqlDataReader["Mail"].ToString();
userRoleId = (int)objSqlDataReader["UserRoleId"];
userStateId = (int)objSqlDataReader["UserStateId"];
objSqlDataReader.Close();
//user.Userstateid = UserStateService.GetUserStateById(userStateId);
//user.Userroleid = UserRoleService.GetUserRoleById(userRoleId);
return user;
}
}
}
catch (Exception e)
{
string str = e.Message;
}
finally
{
da.GetClose();
}
return null;
}