i was wondering if anyone can help me with this issue i have, i am trying to log users in by checking in the customer table for username and password and check it against customer type table to determine the type of customer they are eg service, trade and
retail, below is what i have and i get this error which i dont know how to go about, any help will be appreciated, thanks in advance
STORED PROCEDURE
ALTER PROCEDURE dbo.StoredProcedure2
(
@Username VarChar(50),
@Password varChar(50),
@OutRes varChar OUTPUT
)
AS
set @OutRes = (select a.customertype from customertype a, customer b where a.customertypeid = b.customertypeid and b.Username = @Username and b.Password = @Password)
if(@OutRes = 1)
begin
set @OutRes = 1--Login is Correct
end
else
begin
set @OutRes = 0 --Bad Login
end
In your stored procedure, change the datatype of your @OutRes parameter to int, not varchar. Also verify that the datatype of your customertype.customertype column is integer. Or did you mean to use the customertypeid column for @OutRes?
I have just changed it to int and i have also verified in the customertype table and customer type is not integer so i have changed the stored procedure to CustomerTypeID which is an int and it does not give me the error now but the login click doesnt do
anything.
my new stored procedure is below
ALTER PROCEDURE dbo.StoredProcedure2
(
@Username VarChar(50),
@Password varChar(50),
@OutRes INT OUTPUT
)
AS
set @OutRes = (select a.customertypeID from customertype a, customer b where a.customertypeid = b.customertypeid and b.Username = @Username and b.Password = @Password)
if(@OutRes = 1)
begin
set @OutRes = 1--Login is Correct
end
else
begin
set @OutRes = 0 --Bad Login
end
ALTER PROCEDURE dbo.StoredProcedure2
(
@Username VarChar(50),
@Password varChar(50),
@OutRes varChar(max) OUTPUT
)
AS
begin
set @OutRes = COALESCE( (select a.customertype
from customertype a, customer b
where a.customertypeid = b.customertypeid and
b.Username = @Username and
b.Password = @Password) , 'Error');
end
I have solved that error by changing the @OutRes type in the membership class, i no longer get any error but its not doing anything when i click login button and im beginning to wonder if my login code is right, iv copied it below, thanks
switch (customertype)
{
case "Trade":
ErrorMessage.Text = " I am Trade......";
break;
case "Service":
ErrorMessage.Text = " I am Service......";
break;
case "Retail":
ErrorMessage.Text = " I am Retail......";
break;
default:
ErrorMessage.Text = "Username or Password Invalid!!!";
break;
}
ALTER PROCEDURE dbo.StoredProcedure2
(
@Username VarChar(50),
@Password varChar(50),
@CustType varchar(50),
@OutRes varChar(50) OUTPUT
)
AS
set @CustType = (select a.customertype from customertype a, customer b where a.customertypeid = b.customertypeid and b.Username = @Username and b.Password = @Password)
if(@CustType = null)
set @OutRes = 'Invalid' --Bad Login
else
set @OutRes = @CustType --Login is Correct
when login is clicked
protected void Login_Click(object sender, EventArgs e)
{
Membership member = new Membership();
member.Username = Username.Text;
member.Password = Password.Text;
string customertype = member.LoginUser();
switch (customertype)
{
case "Trade":
ErrorMessage.Text = " I am Trade......";
break;
case "Service":
ErrorMessage.Text = " I am Service......";
break;
case "Retail":
ErrorMessage.Text = " I am Retail......";
break;
default:
ErrorMessage.Text = "Username or Password Invalid!!!";
break;
}
"Procedure or function 'StoredProcedure2' expects parameter '@CustType', which was not supplied."
I have ran the query in the stored procedure which returns a customer type e.g retail, trade or service based on the username and password i enter, it returns the customer type the user belongs to so i dont understand why i get the above error, please help,
THANKS
taolut
Member
1 Points
31 Posts
Conversion failed when converting the varchar value 'S' to data type int.
Apr 11, 2012 07:55 PM|LINK
Hi
i was wondering if anyone can help me with this issue i have, i am trying to log users in by checking in the customer table for username and password and check it against customer type table to determine the type of customer they are eg service, trade and retail, below is what i have and i get this error which i dont know how to go about, any help will be appreciated, thanks in advance
STORED PROCEDURE
ALTER PROCEDURE dbo.StoredProcedure2
(
@Username VarChar(50),
@Password varChar(50),
@OutRes varChar OUTPUT
)
AS
set @OutRes = (select a.customertype from customertype a, customer b where a.customertypeid = b.customertypeid and b.Username = @Username and b.Password = @Password)
if(@OutRes = 1)
begin
set @OutRes = 1--Login is Correct
end
else
begin
set @OutRes = 0 --Bad Login
end
LOGIN.ASPX.CS
protected void Login_Click(object sender, EventArgs e)
{
Membership member = new Membership();
member.Username = Username.Text;
member.Password = Password.Text;
string customertype = member.LoginUser();
switch (customertype)
{
case "Trade":
ErrorMessage.Text = " I am Trade......";
break;
case "Service":
ErrorMessage.Text = " I am Service......";
break;
case "Retail":
ErrorMessage.Text = " I am Retail......";
break;
default:
ErrorMessage.Text = "Username or Password Invalid!!!";
break;
}
}
MEMBERSHIP CLASS
public string LoginUser()
{
string customerType ="";
DbConnection dbconnect = new DbConnection();
SqlConnection connect = dbconnect.OpenConenction();
SqlCommand SelectQuery = new SqlCommand();
SelectQuery.CommandType = CommandType.StoredProcedure;
SelectQuery.CommandText = "[dbo].[StoredProcedure2]";
SelectQuery.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = Username;
SelectQuery.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = Password;
SelectQuery.Parameters.Add("@OutRes", SqlDbType.Int, 4);
SelectQuery.Parameters["@OutRes"].Direction = ParameterDirection.Output;
SelectQuery.Connection = connect;
SelectQuery.ExecuteNonQuery();
SqlDataReader reader = SelectQuery.ExecuteReader(CommandBehavior.SingleResult);
if (reader.HasRows) {
while (reader.Read()) {
customerType = reader["@OutRes"].ToString();
// customerType = reader["customerType"].ToString();
}
}
dbconnect.CloseConnection();
return customerType;
}
}
}
ANY HELP WILL BE APPRECIATED, THANKS
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 11, 2012 08:02 PM|LINK
In your stored procedure, change the datatype of your @OutRes parameter to int, not varchar. Also verify that the datatype of your customertype.customertype column is integer. Or did you mean to use the customertypeid column for @OutRes?
taolut
Member
1 Points
31 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 11, 2012 08:11 PM|LINK
Thanks
I have just changed it to int and i have also verified in the customertype table and customer type is not integer so i have changed the stored procedure to CustomerTypeID which is an int and it does not give me the error now but the login click doesnt do anything.
my new stored procedure is below
ALTER PROCEDURE dbo.StoredProcedure2
(
@Username VarChar(50),
@Password varChar(50),
@OutRes INT OUTPUT
)
AS
set @OutRes = (select a.customertypeID from customertype a, customer b where a.customertypeid = b.customertypeid and b.Username = @Username and b.Password = @Password)
if(@OutRes = 1)
begin
set @OutRes = 1--Login is Correct
end
else
begin
set @OutRes = 0 --Bad Login
end
imobsuz
Participant
1278 Points
195 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 11, 2012 09:00 PM|LINK
Hi,
Try:
ALTER PROCEDURE dbo.StoredProcedure2 ( @Username VarChar(50), @Password varChar(50), @OutRes varChar(max) OUTPUT ) AS begin set @OutRes = COALESCE( (select a.customertype from customertype a, customer b where a.customertypeid = b.customertypeid and b.Username = @Username and b.Password = @Password) , 'Error'); endI hope this helps.
taolut
Member
1 Points
31 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 11, 2012 09:13 PM|LINK
Thanks
i have just done that and this is the new error i get
""Error converting data type varchar(max) to int""
I ran the query for the stored procedure and it returns the customer type eg Service, trade or retail from the customer type table
Thanks
taolut
Member
1 Points
31 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 11, 2012 09:18 PM|LINK
I have solved that error by changing the @OutRes type in the membership class, i no longer get any error but its not doing anything when i click login button and im beginning to wonder if my login code is right, iv copied it below, thanks
protected void Login_Click(object sender, EventArgs e)
{
Membership member = new Membership();
member.Username = Username.Text;
member.Password = Password.Text;
string customertype = member.LoginUser();
switch (customertype)
{
case "Trade":
ErrorMessage.Text = " I am Trade......";
break;
case "Service":
ErrorMessage.Text = " I am Service......";
break;
case "Retail":
ErrorMessage.Text = " I am Retail......";
break;
// default:
// ErrorMessage.Text = "Username or Password Invalid!!!";
// break;
}
im suspecting the SWITCH might bot be right, im quite new to ASP.NET so im sorry if im asking questions that are obvious to the eyes
Thanks
imobsuz
Participant
1278 Points
195 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 12, 2012 12:01 AM|LINK
You tried with the default section?
switch (customertype) { case "Trade": ErrorMessage.Text = " I am Trade......"; break; case "Service": ErrorMessage.Text = " I am Service......"; break; case "Retail": ErrorMessage.Text = " I am Retail......"; break; default: ErrorMessage.Text = "Username or Password Invalid!!!"; break; }bugwee
Member
354 Points
72 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 12, 2012 01:27 AM|LINK
Your stored Procedure
when login is clicked
protected void Login_Click(object sender, EventArgs e) { Membership member = new Membership(); member.Username = Username.Text; member.Password = Password.Text; string customertype = member.LoginUser(); switch (customertype) { case "Trade": ErrorMessage.Text = " I am Trade......"; break; case "Service": ErrorMessage.Text = " I am Service......"; break; case "Retail": ErrorMessage.Text = " I am Retail......"; break; default: ErrorMessage.Text = "Username or Password Invalid!!!"; break; }taolut
Member
1 Points
31 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 12, 2012 08:51 AM|LINK
Hi
I have made the changes and i get the error below
"Procedure or function 'StoredProcedure2' expects parameter '@CustType', which was not supplied."
I have ran the query in the stored procedure which returns a customer type e.g retail, trade or service based on the username and password i enter, it returns the customer type the user belongs to so i dont understand why i get the above error, please help, THANKS
taolut
Member
1 Points
31 Posts
Re: Conversion failed when converting the varchar value 'S' to data type int.
Apr 12, 2012 08:53 AM|LINK
When i include the default section, it just runs past the cases through to the default to output " Username or Password Invalid"