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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

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

0	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

0	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

0	what should be specified at @username? please help me..... Akiii, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts