Conversion failed when converting the varchar value 'S' to data type int. RSS

• • Reply
  

  bugwee

  Member

  354 Points

  72 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 09:13 AM|LINK

  modify the stored procedure, it should be

  ALTER PROCEDURE dbo.StoredProcedure2
  (
  @Username VarChar(50),
  @Password varChar(50),
  @OutRes varChar(50) OUTPUT
  )
  AS
  Declare @CustType varchar(50)
  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

  
  
  

  Mark as Answer the posts that helped you.
• • Reply
  

  taolut

  Member

  1 Points

  31 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 09:24 AM|LINK

  I still cant get this to work, i t just runs through to the default

  i just changed the stored procedure and now it just runs through to the default to show invalid login or password, i have copied below all the codes i have.... Can someone please help me take a look at if is there is something i am not seeing please

  STORED PROCEDURE

  ALTER PROCEDURE dbo.StoredProcedure2
  (
  @Username VarChar(50),
  @Password varChar(50),
  @OutRes varChar(50) OUTPUT
  )
  AS
  Declare @CustType varchar(50)
  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

  LOGIN CLICK

  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;
  }

  }

  THE LOGINUSER() IN MEMBERSHIP CLASS

  public partial class Membership
  {
  public String Username { get; set; }
  public String Password { get; set; }

  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.VarChar, 50);
  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();
  }
  }
  dbconnect.CloseConnection();
  return customerType;
  }

  THANKS A LOT

  • Edited by taolut on Apr 12, 2012 02:35 PM
• • Reply
  

  bugwee

  Member

  354 Points

  72 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 09:26 AM|LINK

  Please mark as answer if that solves your problem.

  Mark as Answer the posts that helped you.
• • Reply
  

  taolut

  Member

  1 Points

  31 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 09:35 AM|LINK

  Hi

  its still not solved unfortunately as it just runs through to the default to show invalid login or username, dont know what i am doing wrong thats y i copied all the code i have on

  Thanks

• • Reply
  

  taolut

  Member

  1 Points

  31 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 02:36 PM|LINK

  I still cant get this to work, i t just runs through to the default

  i just changed the stored procedure and now it just runs through to the default to show invalid login or password, i have copied below all the codes i have.... Can someone please help me take a look at if is there is something i am not seeing please

  STORED PROCEDURE

  ALTER PROCEDURE dbo.StoredProcedure2
  (
  @Username VarChar(50),
  @Password varChar(50),
  @OutRes varChar(50) OUTPUT
  )
  AS
  Declare @CustType varchar(50)
  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

  LOGIN CLICK

  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;
  }

  }

  THE LOGINUSER() IN MEMBERSHIP CLASS

  public partial class Membership
  {
  public String Username { get; set; }
  public String Password { get; set; }

  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.VarChar, 50);
  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();
  }
  }
  dbconnect.CloseConnection();
  return customerType;
  }

  THANKS A LOT

• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 02:49 PM|LINK

  Try this way to check if the return is expected:

  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 = customertype;
          break;
  }

   

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
• • Reply
  

  taolut

  Member

  1 Points

  31 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 03:17 PM|LINK

  Hi

  II have just done that and now it does nothing when i click login which means its not getting any return i suppose, i have looked through the code several times and still cannot see what the problem is :(

• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 05:48 PM|LINK

  Keep the switch and try to change the if statement in the procedure (StoredProcedure2):

  if (@CustType is null)

   

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
• • Reply
  

  taolut

  Member

  1 Points

  31 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 12, 2012 06:17 PM|LINK

  Thanks for the reply, i have just done that now and it still runs through to the default, invalid username and password 

• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: Conversion failed when converting the varchar value 'S' to data type int.

  Apr 13, 2012 12:26 AM|LINK

  Try to keep the switch below for tests:

  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 = "(" + customertype + ")";
          break;
  }

  Then try the following version of the procedure: 

  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/' + @Username + '/' + @Password);
  end
  

  
  
  

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos