JavaScript - check value in DB through jQuery...

Asked By shah zeb on 13-Dec-11 09:16 AM
I need to check in for a value in DB on blur through jquery .
(In below image if form Number already enterred need to display a message)
i am using asp.net with c sharp.

Need a short example
Suchit shah replied to shah zeb on 13-Dec-11 09:22 AM

You will have to use C# or Linq to get the data from the database. With jQuery and Javascript you can just avoid making a page reload when you change the dropdown contents.

Let's say you have an ASP page that get's the data from the database and displays them as a http://www.json.org/string. The output should look like this:

{key1:"value1", key2:"value2"}

You can then make an ajax request to that page (from any page on your site) using jQuery:

$.ajax({
  url: 'http://url.to.the.database.page',
  type: 'get',
  success: function(json_data){
     var dd = $('#dropdown_id'); // select the dropdown you want to change
     eval('var data = json_data;'); // you can use a parsing function here instead of eval.
     var options = "";
     for( k in data )
       options += "<option value='"+k+"'>"+data[k]+"</option>";
     dd.empty().append(options);
  }
});

If you go with JSON you may as well use jQuery's http://docs.jquery.com/Ajax/jQuery.getJSON function. If you go with another data format, you'll have to do your own parsing.

dipa ahuja replied to shah zeb on 13-Dec-11 10:12 AM
<%= Html.TextBox("Username", "", new { @onchange = "CheckAvailability()" })%>
  <div style="display:inline;" id="usernamelookupresult"></div><br/>
  <input type="button" value="Check Availability" onclick="CheckAvailability()" />
function CheckAvailability() {
  $.post("/Home/CheckAvailability",
  { Username: $("#Username").val()},
    function(data) {
      var myObject = eval('(' + data + ')');
      var newid = myObject;
      if (newid == 0) {
        $("#usernamelookupresult").html("<font color='green'>Available :-D</font>")
      }
      else {
        $("#usernamelookupresult").html("<font color='red'>Taken :-(</font>")
      }
  });
}
public ActionResult CheckAvailability(string Username)
{
    int Taken = 0;
    //  This is where you add your database lookup
    if (Username == "username")
    {
      Taken = 1;
    }
    return Json(Taken);
} http://www.billsternberger.net/asp-net-mvc/check-username-availability-with-jquery-asp-net-mvc-c/
dipa ahuja replied to shah zeb on 13-Dec-11 10:14 AM
http://www.dotnetspark.com/kb/1278-check-username-availability-from-database.aspx

also check this article
Riley K replied to shah zeb on 13-Dec-11 11:43 AM


You can have a stored procedure that checks for value in database

CREATE PROCEDURE spx_CheckUserAvailability
    @UserName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS
      (SELECT * FROM Users
       WHERE UserName = @UserName
      )
      SELECT 'true'
    ELSE
      SELECT 'false'
END

Now you can create a web method that calls the store procedure

[System.Web.Services.WebMethod]
public static string CheckUserName(string userName)
{
  string returnValue = string.Empty;
  try
  {
    string consString = ConfigurationManager
        .ConnectionStrings["conString"].ConnectionString;
    SqlConnection conn = new SqlConnection(consString);
    SqlCommand cmd = new SqlCommand("spx_CheckUserAvailability", conn);      
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@UserName", userName.Trim());
    conn.Open();
    returnValue = cmd.ExecuteScalar().ToString();
    conn.Close();
  }
  catch
  {
    returnValue = "error";
  }
  return returnValue;
}

In the aspx page you can call this in ajax way

function ShowAvailability() {
  $.ajax({
    type: "POST",
    url: "CS.aspx/CheckUserName",
    data: '{userName: "' + $("#<%=txtUserName.ClientID%>")[0].value + '" }',
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: OnSuccess,
    failure: function(response) {
      alert(response);
    }
  });
}



Regards