a simple registration, but never verify the username RSS

• • Reply
  

  sdnd2000

  Member

  58 Points

  111 Posts

  a simple registration, but never verify the username

  Feb 29, 2012 02:06 AM|LINK

  Hi, I created a simple registration function which include verify username method. But it never worked. I attached the .net code and storeprocedure. Please note the function is just able to register only, supposed to verify as well. 

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Data;
  using System.Data.SqlClient;
  
  namespace LoginTest
  {
      public partial class Registration : System.Web.UI.Page
      {
          protected void Page_Load(object sender, EventArgs e)
          {
          }
  
          protected void Button1_Click(object sender, EventArgs e)
          {
              string strCon = @"server=(local);database=Login;Persist Security Info=True;Integrated Security=SSPI;";
              SqlConnection conn = new SqlConnection(strCon);
              conn.Open();
              SqlCommand cmd = new SqlCommand("verify_login", conn);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add("@username", SqlDbType.VarChar, 20).Value = username.Text.Trim();
              cmd.Parameters.Add("@result", SqlDbType.Int, 10).Direction = ParameterDirection.Output;
              //cmd.ExecuteNonQuery();
              int count = Convert.ToInt32(cmd.Parameters["@result"].Value);
              if (count > 0)
              {
  
                  Response.Write("user existed");
                  conn.Close();
  
  
              }
                    
              else
              {
                   register();
                 
  
              }
            
  
                  
          }
  
             protected void register()
          {
  
             string strCon = @"server=(local);database=Login;Persist Security Info=True;Integrated Security=SSPI;";
              SqlConnection conn = new SqlConnection(strCon);
              conn.Open();
              SqlCommand cmd = new SqlCommand("dbo.usp_adduser1", conn);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add("@login", SqlDbType.VarChar, 20).Value = user_id.Text.Trim();
              cmd.Parameters.Add("@username", SqlDbType.VarChar, 20).Value = username.Text.Trim();
              cmd.Parameters.Add("@pswd", SqlDbType.VarChar, 20).Value = password.Text.Trim();
              cmd.Parameters.Add("@f_name", SqlDbType.VarChar, 25).Value = firstname.Text.Trim();
              cmd.Parameters.Add("@l_name", SqlDbType.VarChar, 35).Value = lastname.Text.Trim();
              cmd.Parameters.Add("@address", SqlDbType.VarChar, 30).Value = address.Text.Trim();
              cmd.Parameters.Add("@city", SqlDbType.VarChar, 30).Value = city.Text.Trim();
              cmd.Parameters.Add("@state", SqlDbType.Char, 2).Value = state.Text.Trim();
              cmd.Parameters.Add("@zipcode", SqlDbType.Char, 10).Value = zipcode.Text.Trim();
              int count0 = cmd.ExecuteNonQuery();
              if (count0 > 0)
              {
  
                  Response.Write("user created");
                  
  
  
              }
  
              else
              {
                  
                  Response.Write("user not created");
  
              }
              conn.Close();
             
          
          }
      }
  }

  USE [Login]
  GO
  /****** Object:  StoredProcedure [dbo].[verify_login]    Script Date: 02/28/2012 21:15:58 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:		<Author,,Name>
  -- Create date: <Create Date,,>
  -- Description:	<Description,,>
  -- =============================================
  ALTER PROCEDURE [dbo].[verify_login]	
  	-- Add the parameters for the stored procedure here
  	@username varchar=50,
  
  	@result int=10 output
  AS
  
  
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  if exists(select username from Login where username = @username )
  select @result=1
  else
  select @result=0
  
  
  
  

• • Reply
  

  me_ritz

  Star

  9337 Points

  1447 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 03:11 AM|LINK

  Modify your SP like:

  ALTER PROCEDURE [dbo].[verify_login]
          @username VARCHAR(20),
          @result INT OUTPUT
  AS
  IF EXISTS(SELECT username FROM Login WHERE username = @username )
  	SET @result=1
  ELSE
  	SET @result=0

  Please mark the post as "Answer" that helps you.
  • Marked as answer by sdnd2000 on Feb 29, 2012 04:46 PM
• • Reply
  

  Vipindas

  Contributor

  5514 Points

  810 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 03:27 AM|LINK

  Don't forget to uncomment cmd.ExecuteNonQuery();

  protected void Button1_Click(object sender, EventArgs e)
  {
  	string strCon = @"server=(local);database=Login;Persist Security Info=True;Integrated Security=SSPI;";
  	SqlConnection conn = new SqlConnection(strCon);
  	conn.Open();
  
  	SqlCommand cmd = new SqlCommand("verify_login", conn);
  	cmd.CommandType = CommandType.StoredProcedure;
  
  	cmd.Parameters.Add("@username", SqlDbType.VarChar, 20).Value = username.Text.Trim();
  	cmd.Parameters.Add("@result", SqlDbType.Int, 10).Direction = ParameterDirection.Output;
  	cmd.ExecuteNonQuery(); 

  	int count = Convert.ToInt32(cmd.Parameters["@result"].Value);
  	if (count > 0)
  	{
  		Response.Write("user existed");
  		conn.Close();
  	}
  	else
  	{
  		register();
  	}
  }
  
  protected void register()
  {
  	string strCon = @"server=(local);database=Login;Persist Security Info=True;Integrated Security=SSPI;";
  	SqlConnection conn = new SqlConnection(strCon);
  	conn.Open();
  
  	SqlCommand cmd = new SqlCommand("usp_adduser1", conn);
  	cmd.CommandType = CommandType.StoredProcedure;
  
  	cmd.Parameters.Add("@login", SqlDbType.VarChar, 20).Value = user_id.Text.Trim();
  	cmd.Parameters.Add("@username", SqlDbType.VarChar, 20).Value = username.Text.Trim();
  	cmd.Parameters.Add("@pswd", SqlDbType.VarChar, 20).Value = password.Text.Trim();
  	cmd.Parameters.Add("@f_name", SqlDbType.VarChar, 25).Value = firstname.Text.Trim();
  	cmd.Parameters.Add("@l_name", SqlDbType.VarChar, 35).Value = lastname.Text.Trim();
  	cmd.Parameters.Add("@address", SqlDbType.VarChar, 30).Value = address.Text.Trim();
  	cmd.Parameters.Add("@city", SqlDbType.VarChar, 30).Value = city.Text.Trim();
  	cmd.Parameters.Add("@state", SqlDbType.Char, 2).Value = state.Text.Trim();
  	cmd.Parameters.Add("@zipcode", SqlDbType.Char, 10).Value = zipcode.Text.Trim();
  
  	int count0 = cmd.ExecuteNonQuery();
  	if (count0 > 0)
  	{
  		Response.Write("user created");
  	}
  	else
  	{
  		Response.Write("user not created");
  	}
  	conn.Close();
  }
  
  
  ALTER PROCEDURE [dbo].[verify_login]	
  	@username varchar=50,
  	@result int output
  AS
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  
  	if exists(select username from Login where username = @username )
  		Begin
  			Set @result = 1
  		End
  	else
  		Begin
  			select @result=0
  		End
  

  Please remember to mark as answer if it helps you
  • Edited by vipindas on Feb 29, 2012 03:29 AM
  • Edited by vipindas on Feb 29, 2012 03:32 AM
  • Edited by vipindas on Feb 29, 2012 03:32 AM
  • Marked as answer by sdnd2000 on Feb 29, 2012 04:46 PM
• • Reply
  

  vish02chouha...

  Member

  303 Points

  251 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 03:46 AM|LINK

  In sql you have to use set keyword for setting any value in the declared variable or arguments(Parameters) Here you use select which doen't do that type of job

  • Marked as answer by sdnd2000 on Feb 29, 2012 04:46 PM
• • Reply
  

  sdnd2000

  Member

  58 Points

  111 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 11:31 AM|LINK

  Hi, I have changed it according to your suggestion, but it is still the same thing.

• • Reply
  

  sdnd2000

  Member

  58 Points

  111 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 11:31 AM|LINK

  Hi, I have changed it according to your suggestion, but it is still the same thing.

• • Reply
  

  Vipindas

  Contributor

  5514 Points

  810 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 04:02 PM|LINK

  You've comment out the Button1_Click event cmd.ExecuteNonQuery() statement; Bcoz of that verify_login procedure will not execute. Uncomment the Button1_Click event cmd.ExecuteNonQuery() statement and try again

  Please remember to mark as answer if it helps you
  • Edited by sdnd2000 on Feb 29, 2012 04:46 PM
  • Edited by sdnd2000 on Feb 29, 2012 11:19 PM
• • Reply
  

  sdnd2000

  Member

  58 Points

  111 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 11:20 PM|LINK

  I did it again. it is the same thing, I checked the count value, it is always 0, I do not why

• • Reply
  

  sdnd2000

  Member

  58 Points

  111 Posts

  Re: a simple registration, but never verify the username

  Feb 29, 2012 11:23 PM|LINK

  looks like this code is not right ?

   int count = Convert.ToInt32(cmd.Parameters["@result"].Value);

• • Reply
  

  Vipindas

  Contributor

  5514 Points

  810 Posts

  Re: a simple registration, but never verify the username

  Mar 01, 2012 03:34 AM|LINK

  I've checked your code and its working great. Please check your connection string and confirm the particular user is exisitng in the table or not.

  I am using Sql server 2005 Express and my connection string is

  string strCon = @"server=.\SQLEXPRESS;database=login;Trusted_Connection=True;";

  Try to set a breakpoint in Button1_Click event conn.Open() statement and chk it again

  Please remember to mark as answer if it helps you