数据库3个字段:Username,Password,[Right](布尔型)ALTER PROCEDURE [dbo].[P_VerifyUser]
(
@username nvarchar(50),
@password nvarchar(50),
@right bit out
)
AS
/*
校验用户名、密码是否正确,正确返回单一数据>0的数据表,不正确返回0数据表
*/
SELECT COUNT(*)
FROM [T_Users]
WHERE [Username]= @username AND [Password]=@password
RETURN上面那个存储过程就是验证用户名密码的,现在我想加一个out参数,把正确的用户名和密码的记录的right字段传出去
(
@username nvarchar(50),
@password nvarchar(50),
@right bit out
)
AS
/*
校验用户名、密码是否正确,正确返回单一数据>0的数据表,不正确返回0数据表
*/
SELECT COUNT(*)
FROM [T_Users]
WHERE [Username]= @username AND [Password]=@password
RETURN上面那个存储过程就是验证用户名密码的,现在我想加一个out参数,把正确的用户名和密码的记录的right字段传出去
@username nvarchar(50),
@password nvarchar(50),
@right bit out
AS
/*
校验用户名、密码是否正确,正确返回单一数据>0的数据表,不正确返回0数据表
*/
DECLARE @cnt int; EXEC sp_executesql N'SELECT @cnt = COUNT(*) FROM [T_Users]
WHERE [Username]= @username AND [Password]=@password',
N'@username nvarchar(50),@password nvarchar(50),@cnt int output',
@username,@password,@cnt output; IF @cnt > 0
SET @right = 1;
ELSE
SET @right = 0; RETURN;
GO
SET @right = 1;
ELSE
SET @right = 0;好像不对把,有些记录的right字段的值就是0呀
我以为你要得是否有记录.有记录就返回1.没有就返回0..你要得到right字段的值.那你COUNT()它干嘛呢?..ALTER PROCEDURE [dbo].[P_VerifyUser]
@username nvarchar(50),
@password nvarchar(50),
@right bit out
AS
/*
校验用户名、密码是否正确,正确返回单一数据>0的数据表,不正确返回0数据表
*/ EXEC sp_executesql N'SELECT @right = [right] FROM [T_Users]
WHERE [Username]= @username AND [Password]=@password',
N'@username nvarchar(50),@password nvarchar(50),@right bit output',
@username,@password,@right output;
RETURN;
GO
@username nvarchar(50),
@password nvarchar(50),
@right bit out
AS
/*
校验用户名、密码是否正确,正确返回单一数据>0的数据表,不正确返回0数据表
*/
DECLARE @cnt int;
SELECT @cnt = COUNT(*) FROM [T_Users]
WHERE [Username]= @username AND [Password]=@password
IF @cnt > 0
SELECT @right = right FROM [T_Users]
WHERE [Username]= @username AND [Password]=@password RETURN;
GO
string Province,string city,string addr,string post,string email,string way,ref string numId)
{
try
{
SqlParameter[] parms = new SqlParameter[16]; parms[0] = new SqlParameter("@psw", SqlDbType.NVarChar,50);
parms[0].Value = psw;
parms[1] = new SqlParameter("@question", SqlDbType.NVarChar, 50);
parms[1].Value = question;
parms[2] = new SqlParameter("@answer", SqlDbType.NVarChar, 50);
parms[2].Value = answer;
parms[3] = new SqlParameter("@name", SqlDbType.NVarChar, 50);
parms[3].Value = name;
parms[4] = new SqlParameter("@companyname", SqlDbType.NVarChar, 100);
parms[4].Value = companyname;
parms[5] = new SqlParameter("@contact", SqlDbType.NVarChar, 50);
parms[5].Value = contact;
parms[6] = new SqlParameter("@tel", SqlDbType.NVarChar, 50);
parms[6].Value = tel;
parms[7] = new SqlParameter("@phone", SqlDbType.NVarChar, 50);
parms[7].Value = phone;
parms[8] = new SqlParameter("@CompanySize", SqlDbType.NVarChar, 100);
parms[8].Value = CompanySize;
parms[9] = new SqlParameter("@Province", SqlDbType.NVarChar, 50);
parms[9].Value = Province;
parms[10] = new SqlParameter("@city", SqlDbType.NVarChar, 50);
parms[10].Value = city;
parms[11] = new SqlParameter("@addr", SqlDbType.NVarChar, 100);
parms[11].Value = addr;
parms[12] = new SqlParameter("@post", SqlDbType.NVarChar, 50);
parms[12].Value = post;
parms[13] = new SqlParameter("@email", SqlDbType.NVarChar, 50);
parms[13].Value = email;
parms[14] = new SqlParameter("@way", SqlDbType.NVarChar, 50);
parms[14].Value = way;
parms[15] = new SqlParameter("@numId", SqlDbType.VarChar, 50);
parms[15].Direction = ParameterDirection.Output;
object num = SqlHelper.SqlHelper.ExecuteNonQuery(GetConnection(connectionString), CommandType.StoredProcedure, "InsertUserInfo", parms);
numId = parms[15].Value.ToString();
if (Convert.ToInt32(num) > 0)
{
return true;
}
else
return false;
}
catch (Exception e)
{
throw e;
}
finally
{
GetConnection(connectionString).Close();
}
}/***********************************************
功能说明:插入用户信息
创建者:Jam.Fu
创建时间:2009/09/01
************************************************/
Create proc InsertUserInfo
@psw NVARCHAR(50),
@question NVARCHAR(50),
@answer NVARCHAR(50),
@name NVARCHAR(50),
@companyname NVARCHAR(100),
@contact NVARCHAR(50),
@tel NVARCHAR(50),
@phone NVARCHAR(50),
@CompanySize NVARCHAR(100),
@Province NVARCHAR(50),
@city NVARCHAR(50),
@addr NVARCHAR(100),
@post NVARCHAR(50),
@email NVARCHAR(50),
@way NVARCHAR(50),
@numId VARCHAR(50) output
AS
DECLARE @rand varchar(100)
SET @rand= RAND((DATEPART(mm, Getdate()) * 100000 )
+ (DATEPART(ss, Getdate()) * 1000 )
+ DATEPART(ms, Getdate()) )
SET @numId=dbo.CreateNumId(@rand)
INSERT INTO
dbo.AX_UserInfo
(
AX_UserPassWd,
AX_UserPwdQuestion,
AX_UserAnswer,
AX_UserTname,
AX_CompanyName,
AX_ContactUser,
AX_UserTel,
AX_Mobel,
AX_CompanySize,
AX_Province,
AX_City,
AX_UserAddr,
AX_UserCode,
AX_UserEmail,
AX_Way,
AX_NumID
)
VALUES
(
@psw,
@question,
@answer,
@name,
@companyname,
@contact,
@tel,
@phone,
@CompanySize,
@Province,
@city,
@addr,
@post,
@email,
@way,
@numId
)