check if an username already exists in the database ?

Posted by Shreesh under ASP.NET on 10/21/2011 | Points: 10 | Views : 23726 | Status : [Member] | Replies : 5
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




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