控件都在呀! 对了,@username varchar(20), @password varchar(100) AS DECLARE @user_id int set @user_id = (Select user_id From [fang].[dbo].[user] Where user_user=@username and user_pwd=@password) if @user_id =null return -1 else return 1 GO 怎么改成SQL语句呀!
SELECT user_id FROM [fang].[dbo].[user] WHERE user_user='"& TextBox1.Text &"' and '"& TextBox2.Text &"' 其中TextBox1和TextBox2是你声明的两个控件,要和你数据库中的字段对应
ExecuteScalar()方法用于获取sql语句执行后返回的数据集的第一行的第一列的值,而不能获取通过return在存储过程中返回的值,所以得改一下你的存储过程:create procedure ValidateLogin ( @username varchar(20), @password varchar(100) ) AS if exists(select 1 from [fang].[dbo].[user] where user_user=@username and user_pwd=@password) select 0 else select 1 GO
对不起,存储过程返回值写反了,改成这样:create procedure ValidateLogin ( @username varchar(20), @password varchar(100) ) AS if exists(select 1 from [fang].[dbo].[user] where user_user=@username and user_pwd=@password) select 1 else select 0 GO然后在你的代码中用ExecuteScalar()方法获取返回的值,如果是1则用户认证通过,否则用户不存在或密码密码
webdiyer(陕北吴旗娃) 说的对,我的意思也是这样,建议事实
create procedure ValidateLogin ( @username varchar(20), @password varchar(100) ) AS if exists(select 1 from [fang].[dbo].[user]//1是干什么用的??? exists什么意思 ? where user_user=@username and user_pwd=@password) select 0 //没看懂 else select 1 //没看懂 GO
要返回user_id字段可以这样:create procedure ValidateLogin ( @username varchar(20), @password varchar(100) ) AS declare @user_id int select @user_id=user_id from [fang].[dbo].[user] where user_user=@username and user_pwd=@password select isnull(@user_id,-1) as user_id GO执行ExecuteScalar()方法后,如果返回的结果是-1表示这个用户不存在,否则返回的就是这个用户的user_id。
object o = myComm.ExecuteScalar();
id(object !=null)
xxxxxxx
i=(Int32)myComm.ExecuteScalar();//未强对像引用到实例!
现在的问题是在用户名和密码全对的情况下,也出现这种错误!!!!
对了,@username varchar(20),
@password varchar(100)
AS
DECLARE @user_id int
set @user_id = (Select user_id From [fang].[dbo].[user] Where user_user=@username and user_pwd=@password)
if @user_id =null
return -1
else
return 1
GO
怎么改成SQL语句呀!
SELECT user_id FROM [fang].[dbo].[user] WHERE user_user='"& TextBox1.Text &"' and '"& TextBox2.Text &"'
其中TextBox1和TextBox2是你声明的两个控件,要和你数据库中的字段对应
i=(int)myComm.ExecuteScalar();//
改成:
i=(int)myComm.ExecuteNonQuery();//就可以了。
ExecuteScalar用来返回查询的第一行的第一列,而你的存储过程返回是一个值。
(
@username varchar(20),
@password varchar(100)
)
AS
if exists(select 1 from [fang].[dbo].[user]
where user_user=@username and user_pwd=@password)
select 0
else
select 1
GO
(
@username varchar(20),
@password varchar(100)
)
AS
if exists(select 1 from [fang].[dbo].[user]
where user_user=@username and user_pwd=@password)
select 1
else
select 0
GO然后在你的代码中用ExecuteScalar()方法获取返回的值,如果是1则用户认证通过,否则用户不存在或密码密码
(
@username varchar(20),
@password varchar(100)
)
AS
if exists(select 1 from [fang].[dbo].[user]//1是干什么用的??? exists什么意思 ?
where user_user=@username and user_pwd=@password)
select 0 //没看懂
else
select 1 //没看懂
GO
string CNstr;
int i;
CNstr="data source=localhost;initial catalog=fang;password=;persist security info=True;user id=sa;packet size=4096";
sqlconn=new SqlConnection(CNstr);
sqlconn.Open();
SqlCommand myComm=new SqlCommand( "ValidateLogin",sqlconn);
myComm.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar,50));
myComm.Parameters["@username"].Value = TextBox1.Text.ToString();
myComm.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar,100));
myComm.Parameters ["@password"].Value = TextBox2.Text.ToString();
myComm.CommandType=CommandType.StoredProcedure;
i=(Int32)myComm.ExecuteNonQuery();
if(i==-1)
{
Label1.Text="失败";
}
else
{
Label1.Text="成功";
}
sqlconn.Close();
myComm.Dispose();
存储过程用吴旗娃的
create procedure ValidateLogin
(
@username varchar(20),
@password varchar(100)
)
AS
if exists(select 1 from [fang].[dbo].[user]
where user_user=@username and user_pwd=@password)
select 1
else
select 0
GO
现在成了无论密码或不对全是失败了!!!我晕ing!!
begin
select count(*) into UserResult from jason_user
where user_id = UserId and user_pwd = UserPwd;
end JASON_LOGIN;
string CNstr;
int i;
CNstr="data source=localhost;initial catalog=fang;password=;persist security info=True;user id=sa;packet size=4096";
sqlconn=new SqlConnection(CNstr);
sqlconn.Open();
SqlCommand myComm=new SqlCommand( "ValidateLogin",sqlconn);
myComm.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar,50));
myComm.Parameters["@username"].Value = TextBox1.Text.ToString();
myComm.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar,100));
myComm.Parameters ["@password"].Value = TextBox2.Text.ToString();
myComm.CommandType=CommandType.StoredProcedure;
i=(Int32)myComm.ExecuteNonQuery();改成//i=(Int32)myComm.ExecuteScalar();就好了!!
if(i==-1)
{
Label1.Text="失败";
}
else
{
Label1.Text="成功";
}
sqlconn.Close();
myComm.Dispose();
存储过程用吴旗娃的
谢谢你!
请问如果成功的话我要返回user_id 呢??
返回的应该是一个值才对,可能无法返回单独的值把,可以Response.Write(i)出来看一看,要是SQL语句可以返回你想要的值,储存过程我还真不知道,请教webdiyer(陕北吴旗娃) :)
(
@username varchar(20),
@password varchar(100)
)
AS
declare @user_id int
select @user_id=user_id from [fang].[dbo].[user]
where user_user=@username and user_pwd=@password
select isnull(@user_id,-1) as user_id
GO执行ExecuteScalar()方法后,如果返回的结果是-1表示这个用户不存在,否则返回的就是这个用户的user_id。