Check User Name or Email Availability Using ASP.NET, Ajax and jQuery

Datetime:2016-08-22 22:06:22          Topic: ASP.NET  jQuery  Ajax           Share

Introduction : In this article I have explained how to check username or email availability from sql server database table using Asp.Net, jQuery and Ajax.

In previous articles i explained  Jquery ajax json example in asp.net to insert data into sql server database withoutpostback and  Ajax autocompleteextender control example in asp.net using web service and  Jquery ui autocomplete textbox with database in asp.net and  Create registration form and send confirmation email to new registered users in asp.net and  Jquery to show image preview after validating image size and type before upload in asp.net

Description : While working on Asp.Net page we usually need to create a registration page to register users so that they can login to the website. On registration page there may be the fields like username, password, date of birth etc. 

Username or email must me unique for each user. So developer has to validate them as soon as they are entered in respective textboxes so that user came to know whether the username or email entered is available or not. 

There are many ways to implement this check. Here I am going to use jQuery to make ajax calls to the server to check whether username/email is available or already assigned to other user.

Implementation : Let’s create a test page (default.aspx) for demonstration purpose.

First of all create a table using the following script

CREATE TABLE tbUsers

(

UserId    INT IDENTITY ( 1 , 1 ) NOT NULL PRIMARY KEY ,

UserName  VARCHAR ( 50 ) NOT NULL,

Password VARCHAR ( 50 ) NOT NULL

)

Insert some dummy data for testing purpose using following script

INSERT INTO tbUsers ( UserName , Password )

VALUES ( 'admin' , 'admin@007#' ),( 'sultan' , 'sultan_777' ),( 'Kabali' , 'bali#999' )

Check table data

SELECT * FROM tbUsers

Result:

UserId

UserName

Password

1

Admin

admin@007#

2

Sultan

sultan_777

3

Kabali

bali#999

Now create a stored procedure to check for username availability as:

CREATE PROCEDURE spCheckUserNameAvailability

(

@UserName VARCHAR ( 50 )

)

AS

BEGIN

SELECT COUNT (*) FROM tbUsers WHERE UserName = @UserName        

END

Now in web.config file create connection string as:

< connectionStrings >

< add name = " sqlCon " connectionString = " Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated Security=True " />

</ connectionStrings >

HTML source

< html xmlns ="http://www.w3.org/1999/xhtml">

< head runat ="server">

< title ></ title >

< style >

.success {

background-color : #5cb85c ;

font-size : 12px ;

color : #ffffff ;

padding : 3px 6px 3px 6px ;

}

.failure {

background-color : #ed4e2a ;

font-size : 12px ;

color : #ffffff ;

padding : 3px 6px 3px 6px ;

}

</ style >

< script src ="http://code.jquery.com/jquery-1.11.3.js" type ="text/javascript"></ script >

< script type ="text/javascript">

function checkUserName(txtUserName) {

$.ajax({

type: "POST" ,

async: true ,

url: 'default.aspx/CheckUserNameAvailability' ,

data: '{username: "' + $(txtUserName).val().trim() + '" }' ,

contentType: "application/json; charset=utf-8" ,

dataType: "json" ,

success: function (response) {

if (response.d != "0" ) {

$( "#spnMsg" ).html( 'Username has already been taken' );

$( "#spnMsg" ).removeClass( "success" ).addClass( "failure" );

$( "#btnRegister" ).prop( 'disabled' , true );

}

else {

$( "#spnMsg" ).html( 'Available' );

$( "#spnMsg" ).removeClass( "failure" ).addClass( "success" );

$( "#btnRegister" ).prop( 'disabled' , false );

}

}

});

}

</ script >

</ head >

< body >

< form id ="form1" runat ="server">

< div >

< fieldset style =" width : 350px ; ">

< legend > Register </ legend >

< table >

< tr >

< td >

< asp : TextBox ID ="txtUserName" runat ="server" placeholder ="User Name" onchange ="checkUserName(this)" ></ asp : TextBox >

< span id ="spnMsg"></ span ></ td >

</ tr >

< tr >

< td >

< asp : TextBox ID ="txtPassword" runat ="server" placeholder ="Password"  TextMode ="Password"

</ td >

</ tr >

< tr >

< td >

< asp : Button ID ="btnRegister" runat ="server" Text ="Register" ClientIDMode ="Static" /></ td >

</ tr >

</ table >

</ fieldset >

</ div >

</ form >

</ body >

</ html >

Asp.Net C# Code to check username availability

In .aspx.cs file create a method to check username availability as:

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.Services;

[ WebMethod ]

public static int CheckUserNameAvailability( string username)

{

string conString = ConfigurationManager .ConnectionStrings[ "sqlCon" ].ConnectionString;

using ( SqlConnection conn = new SqlConnection (conString))

{

using ( SqlCommand cmd = new SqlCommand ( "spCheckUserNameAvailability" , conn))

{

cmd.CommandType = CommandType .StoredProcedure;

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

conn.Open();

return ( int )cmd.ExecuteScalar();

}

}

}

Asp.Net VB Code to check username availability

In .aspx.vb file create a method to check username availability as:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Web.Services

< WebMethod > _

Public Shared Function CheckUserNameAvailability(username As String ) As Integer

Dim conString As String = ConfigurationManager .ConnectionStrings( "sqlCon" ).ConnectionString

Using conn As New SqlConnection (conString)

Using cmd As New SqlCommand ( "spCheckUserNameAvailability" , conn)

cmd.CommandType = CommandType .StoredProcedure

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

conn.Open()

Return CInt (cmd.ExecuteScalar())

End Using

End Using

End Function

Explanation : As soon as username is entered in textbox the onchange event gets fired and username is passed to the checkUserName function which makes ajax call to the server side function “ CheckUserNameAvailability ” with the help of jquery. 

To be able to call server side function using jquery ajax, the function must be defined as web service WebMethod and it should be public static in C# and public shared in Vb. Username passed as parameter to this function is then passed to stored procedure which counts how many similar username exists in database. 

If the returned count is greater than 0 then that means same username or email already exists in table otherwise it is available.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and  If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.  





About List