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.
2 comments
Click here for commentswonderful!! really useful!! thank you very much
ReplyGood work.... Explaning Wonderful!!
ReplyIf you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..