Checking Record Availability using JOSN and jQuery

Introduction

This code snippet explains how to check the user name that end user is entering already exist in database or not if it exist then message will show. In this entire process page is not refreshing.

I am using JOSN and ajax as in the following example:

  1. <script src="Scripts/jquery-1.3.2.min.js" type="text/javascript"></script>   
  2. <script type = "text/javascript">  
  3. function ShowAvailability() {  
  4.     $.ajax({  
  5.         type: "POST",  
  6.         url: "CheckUserAvalibleusingJOSN.aspx/CheckUserName",  
  7.         data: '{userName: "' + $("#<%=txtUserName.ClientID%>")[0].value + '" }',  
  8.         contentType: "application/json; charset=utf-8",  
  9.         dataType: "json",  
  10.         success: OnSuccess,  
  11.         failure: function (response) {  
  12.             alert(response);  
  13.         }  
  14.     });  
  15. }  
  16.   
  17. function OnSuccess(response) {  
  18.     var mesg = $("#mesg")[0];  
  19.     switch (response.d) {  
  20.         case "true":  
  21.         mesg.style.color = "green";  
  22.         mesg.innerHTML = "Available";  
  23.         break;  
  24.         case "false":  
  25.         mesg.style.color = "red";  
  26.         mesg.innerHTML = "Not Available";  
  27.         break;  
  28.         case "error":  
  29.         mesg.style.color = "red";  
  30.         mesg.innerHTML = "Error occured";  
  31.         break;  
  32.     }  
  33. }  
  34.   
  35. function OnChange(txt) {  
  36.     $("#mesg")[0].innerHTML = "";  
  37.     ShowAvailability();//hide this function from here if we want to check avability by using button click  
  38. }  
  39. </script>  
  40. </head>  
  41. <body>  
  42.     <form id="form1" runat="server">  
  43.     <div >  
  44.         UserName : <asp:TextBox ID="txtUserName" runat="server" onkeyup = "OnChange(this)"></asp:TextBox>  
  45.         <%--<input id="btnCheck" type="button" value="Show Availability" onclick = "ShowAvailability()" />--%>  
  46.         <br />  
  47.         <span id = "mesg"></span>  
  48.         </div>  
  49.     </form>  
  50. </body> 

Code

  1. [System.Web.Services.WebMethod]  
  2. public static string CheckUserName(string userName)  
  3. {  
  4.     string returnValue = string.Empty;  
  5.     try  
  6.     {  
  7.         string consString = ConfigurationManager.ConnectionStrings["manish_dbCS"].ConnectionString;  
  8.         SqlConnection conn = new SqlConnection(consString);  
  9.         SqlCommand cmd = new SqlCommand("spx_CheckUserAvailability", conn);  
  10.         cmd.CommandType = CommandType.StoredProcedure;  
  11.         cmd.Parameters.AddWithValue("@UserName", userName.Trim());  
  12.         conn.Open();  
  13.         returnValue = cmd.ExecuteScalar().ToString();  
  14.         conn.Close();  
  15.     }  
  16.     catch  
  17.     {  
  18.         returnValue = "error";  
  19.     }  
  20.     return returnValue;  

SQL Query

  1. USE [manish_db]  
  2. GO  
  3. /****** Object: StoredProcedure [dbo].[spx_CheckUserAvailability] Script Date: 07/19/2014 02:17:13 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. ALTER PROCEDURE [dbo].[spx_CheckUserAvailability]  
  9. @UserName VARCHAR(50)  
  10. AS  
  11. BEGIN  
  12. SET NOCOUNT ON;  
  13. IF NOT EXISTS  
  14. (SELECT UserName FROM dbo.UserDetails  
  15. WHERE UserName = @UserName  
  16. )  
  17. SELECT 'true'  
  18. ELSE  
  19. SELECT 'false'  
  20. END 

Note: If we use web services just add webservices and replace the page path with *.asmx file path.

Code

  1. [WebService(Namespace = "http://tempuri.org/")]  
  2. [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]  
  3. [System.ComponentModel.ToolboxItem(false)]  
  4. // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.  
  5. [System.Web.Script.Services.ScriptService]  
  6. public class CheckUserAvalible : System.Web.Services.WebService  
  7. {  
  8.     [WebMethod]  
  9.     public string CheckUserName(string userName)  
  10.     {  
  11.         string returnValue = string.Empty;  
  12.         try  
  13.         {  
  14.             string consString = ConfigurationManager.ConnectionStrings["manish_dbCS"].ConnectionString;  
  15.             SqlConnection conn = new SqlConnection(consString);  
  16.             SqlCommand cmd = new SqlCommand("spx_CheckUserAvailability", conn);  
  17.             cmd.CommandType = CommandType.StoredProcedure;  
  18.             cmd.Parameters.AddWithValue("@UserName", userName.Trim());  
  19.             conn.Open();  
  20.             returnValue = cmd.ExecuteScalar().ToString();  
  21.             conn.Close();  
  22.         }  
  23.         catch  
  24.         {  
  25.             returnValue = "error";  
  26.         }  
  27.         return returnValue;  
  28.     }  

Summary

In this illustration we came to learn about a user name checking without page refresh by using JOSN and AJEX . Please put your valuable comments about this code snippet.