How to see if username exist in database ? [Resolved]

Posted by Akiii under C# on 6/13/2011 | Points: 10 | Views : 12585 | Status : [Member] | Replies : 7
Hi,

I have a username and password fields in my aspx page....
now when the user enters the username, i want to check if the username already exists in the database or not via stored procedure ...?

Can anyone tell me how to do this...??
Any help is appreciated..

Thanks and Regards
Akiii




Responses

Posted by: Sksingh on: 6/13/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Akiii,

You can use output parameter in SQL to return a value if username already exists.

Procedure


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


How to call it from code behind


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

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

Posted by: Akiii on: 6/13/2011 [Member] Bronze | Points: 25

Up
0
Down
hi sunil...thanks for ur reply...

i tried to execute your stored procedure but its giving me "Incorrect syntax near the keyword 'return'" error.....
Could you please tell me why it is giving me ?

Regards
Akiii

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

Posted by: Sksingh on: 6/14/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Akiii,

Now check it above procedure , in syntax one begin was missing after else block.

Regards,
Sunil

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

Posted by: Akiii on: 6/14/2011 [Member] Bronze | Points: 25

Up
0
Down
yes sunil.....it should be like this:-
CREATE PROCEDURE  sp_checkUsers 

(
@UserName nVarchar(max),
@IsExists INT OUTPUT
)

AS

If exists (select user_nametb from usertable where user_nametb = @UserName)

Begin
Set @IsExists = 1
return @IsExists
End

Else

Begin
Set @IsExists = 0
return @IsExists
End


Your code is working fine......
Could you please recommend me some sites where i can study stored procedures.....i am not good at it ??

Thanks for ur help sunil....
I mark yours as an answer....

Regards
Akiii


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

Posted by: Sksingh on: 6/14/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Akiii,

Please check below link for basic proc

http://msdn.microsoft.com/en-us/library/ms190669.aspx

top of that you can write your own logic, even u can use try, catch block and validate your business logic over here.

Regards,
Sunil

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

Posted by: Akiii on: 6/14/2011 [Member] Bronze | Points: 25

Up
0
Down
Thanks Sunil for your esteemed help....

Enjoy coding....

Regards
Akiii

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

Posted by: Abhinaya on: 2/3/2013 [Member] Starter | Points: 25

Up
0
Down
what should be specified at @username? please help me.....

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

Login to post response