In this article I will explain with an example, how to check if data already exists in Database using ASP.Net with C# and VB.Net.
This article will make use of ASP.Net AJAX PageMethods for calling static Web Methods and checking whether data already exists in Database in ASP.Net.
Database
I have made use of the following table Users with the schema as follows.
I have already inserted few records in the table.
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The following HTML Markup consists of ASP.Net AJAX ScriptManager control for which the EnablePageMethods property is set to True.
There is an HTML TextBox, an HTML Button and an HTML SPAN element.
The HTML TextBox has been assigned an onkeyup event handler which triggers the ClearMessage JavaScript function.
The HTML Button has been assigned an onclick event handler which triggers the CheckAvailability JavaScript function.
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
</asp:ScriptManager>
Username:
<input id="txtUsername" type="text" onkeyup="ClearMessage()" />
<input id="btnCheck" type="button" value="Show Availability" onclick="CheckAvailability()" />
<br />
<span id="message"></span>
Stored Procedure
The following Stored Procedure will be used to check whether the supplied Username exists in the database or not.
It will return TRUE if the Username does not exists in database i.e. it is available and it will return FALSE if the Username exists in the database i.e. it is already in use.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CheckUserAvailability]
@UserName VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT UserName FROM Users
WHERE UserName = @UserName)
BEGIN
SELECT 'TRUE'
END
ELSE
BEGIN
SELECT 'FALSE'
END
END
GO
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
The Web Method
The following Web Method will be called from the Client side as ASP.Net AJAX ScriptManager PageMethod.
The Web Method calls the CheckUserAvailability Stored Procedure and returns the value returned from the Stored Procedure back to the Client.
C#
[System.Web.Services.WebMethod]
public static bool CheckUserName(string username)
{
bool status = false;
string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("CheckUserAvailability", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username.Trim());
conn.Open();
status = Convert.ToBoolean(cmd.ExecuteScalar());
conn.Close();
}
}
return status;
}
VB.Net
<System.Web.Services.WebMethod()> _
Public Shared Function CheckUserName(ByVal username As String) As Boolean
Dim status As Boolean = False
Dim constr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using conn As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("CheckUserAvailability", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@UserName", username.Trim())
conn.Open()
status = Convert.ToBoolean(cmd.ExecuteScalar())
conn.Close()
End Using
End Using
Return status
End Function
The Client Side PageMethod implementation
The CheckAvailability JavaScript function gets called when the Button is clicked, it first fetches the Username to be checked for availability from the TextBox and then calls the WebMethod using the ASP.Net AJAX ScriptManager PageMethod function.
When the response is received, based on whether the Username is available or in use, appropriate message is displayed in the HTML SPAN element.
The ClearMessage JavaScript function gets called when user types in the TextBox, it simply clears the message displayed in the HTML SPAN element.
<script type="text/javascript">
function CheckAvailability() {
var username = document.getElementById("txtUsername").value;
PageMethods.CheckUserName(username, function (response) {
var message = document.getElementById("message");
if (response) {
//Username available.
message.style.color = "green";
message.innerHTML = "Username is available";
}
else {
//Username not available.
message.style.color = "red";
message.innerHTML = "Username is NOT available";
}
});
};
function ClearMessage() {
document.getElementById("message").innerHTML = "";
};
</script>
Screenshot
Browser Compatibility
The above code has been tested in the following browsers.
* All browser logos displayed above are property of their respective owners.
Demo
Downloads