Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 11203
Home > Forums > ASP.NET >

check if an username already exists in the database ?

Posted by Shreesh under ASP.NET on 10/21/2011 | Points: 10 | Views : 30708 | Status : [Member] | Replies : 5
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
this is the stired procedure i have used to check if an username already exists in the database or not. so far duplicate values are not stored in the database,but i need to display an output that says "this username already exists".can somebody tell me how ?


ALTER proc db_ins1(@username nvarchar(50),@dob nvarchar(max),@sex nvarchar(50),@password nvarchar(50),@email nvarchar(50))
as
if EXISTS(select 'true' from reg1 where username=@username)
begin
select 'this user already exists!!'
end
else
begin
insert into reg1 values(@username,@dob,@sex,@password,@email);
select' record added'
end



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Thiru on: 10/21/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Shreesh,

I will show the mysql stored procedure for your question: 

DELIMITER$$

CREATE PROCEDURE proc db_ins1(

IN @username nvarchar(50),

IN @psw nvarchar(50),

OUT @rslt int(16))



BEGIN

DECLARE @cnt INT DEFAULT 0;



SELECT COUNT(*) INTO @cnt FROM reg1 WHERE username=@username;

SET @rslt=@cnt;

IF @rslt=0 THEN

  INSERT INTO reg1.....//qry to insert data into table reg1;

END IF;



END $$

DELIMITER;


Note:
you have to set the OUT parameter in stored procedure to get result once SP is executed.
here i used @rslt as OUTPUT param.
if @rslt=0 ---- it will insert data in to table

As in code behind file: 
  

                c1 = dbconn

                c1.Open()

                cmd = New MySqlCommand("proc db_ins1", c1)

                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("@username", usrnm).Value = usrnm

                cmd.Parameters.Add("@psw", psw).Value = psw

                cmd.Parameters.Add(New MySqlParameter("@rslt", MySqlDbType.String))

                cmd.Parameters("@rslt").Direction = ParameterDirection.Output

                exeresult = cmd.ExecuteNonQuery()

                result = Int(cmd.Parameters("p_cnt").Value)

                If result > 0 Then

                    exeresult = "Customer name already exists"

                Else

                    exeresult = "Saved Successfully"

                End If

                c1.Close()


Hope this helps :-)

Regards,
Thiru.

Shreesh, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Shreesh on: 10/21/2011 [Member] Starter | Points: 25

Up
0
Down
thank you for your help,but i'm working in ASP.net and not VB ,since i'm new to this ,i was not able to understand some of the concepts like the line 
" cmd.Parameters.Add(New MySqlParameter("@rslt", MySqlDbType.String))"

and i'm also getting an error in the stored procedure SUCH AS "INCORRECT SYNTAX NEAR BEGIN"
and "INCORRRECT SYNTAX NEAR THEN" .


Shreesh, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Thiru on: 10/21/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Shreesh,
Hope you are in asp.net c# with backend MsSQL
but i gave code in asp.net vb with backend MySQL

try to use online code convert vb to c#
or

here:
cmd.Parameters.Add(New MySqlParameter("@rslt", MySqlDbType.String))
cmd.Parameters("@rslt").Direction = ParameterDirection.Output

There are IN, OUT, INOUT parameter directions available in stored procedure.
the above code is to define the direction: OUT
then only we can get the value from DB after the execution of stored procedure.

And regarding syntax error in stored procedure:
I used mysql stored procedure.
use that idea and create SP in MSSQL as per it syntax.

Regards,
Thiru.

Shreesh, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Taanu51 on: 10/25/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Check out this link it may solve your problem

http://www.developerscode.com/2011/02/how-to-set-check-availability-button-in.html

Tanisha Sayyad
.Net and Android Developer
INDIA(Andhra Pradesh)

Shreesh, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sksingh on: 10/25/2011 [Member] Starter | Points: 25

Up
0
Down
Hi ,
Please check below SP as well as c# code for your requrement. 

CREATE PROCEDURE  sp_checkUsers 

(

    @UserName Varchar(50),

    @IsExists INT OUTPUT

 )

AS	



If exists (select Username from TableName where username = @username)

Begin

Set @IsExists = 1

End 

Else

begin

Set @IsExists = 0

End

Return @IsExists


C# Code 


public int CheckUser(string UserName)

        {

            int _count = 0;

            SqlConnection con=new SqlConnection("");

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = con;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.CommandText = "sp_checkUsers";



            cmd.Parameters.Clear();

            cmd.Parameters.AddWithValue("@UserName", UserName);

            SqlParameter param = new SqlParameter("@IsExists",System.Data.SqlDbType.Int);

            param.Direction= System.Data.ParameterDirection.Output;

            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();

            _count = Convert.ToString(param.Value);

            return _count;



        }





If count value is greater than 0 means UserName already exists in Table.

Hope it will work fine for your requirement.

Regards,
Sunil

Regards,
Sunil

Shreesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts