How to check for the existence of record into the database?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 15087 red flag
Rating: 5 out of 5  
 1 vote(s)

In this article, we shall learn How to check for the existence of record into the database.

Introduction

ADO.NET is a data access mechanism to communicate with the data sources such as SQL server, XML, ODBC & OLEDB data sources. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the in-memory data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In order to check whether a record exists into the database or not, we can follow this approach.

Below is my ASPX code that contains a simple form.

ASPX PAGE

<h3>Check for the existence of the record</h3>

<table cellpadding="3" cellspacing="1">

<tr><td>First name: </td><td><asp:TextBox ID="txtFirstName"

runat="server" /> </td></tr>

<tr><td>Last name: </td><td><asp:TextBox ID="txtLastName" runat="server"

/> </td></tr>

<tr><td>&nbsp;</td><td><asp:Button ID="btnSubmit" runat="server"

Text="Submit" OnClick="CheckRecord" /></td></tr>

</table>

<p><asp:Label ID="lblMessage" runat="server" EnableViewState="false"

ForeColor="Red" /></p>

In the above code snippet we have a two textboxes and a button.

CODE BEHIND

string _connStr =

ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

 

 

protected void CheckRecord(object sender, EventArgs e)

{

// get the connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// write the sql statement to execute

string sql = "SELECT AutoId FROM PersonalDetail WHERE FirstName = @FirstName

AND LastName = @LastName";

 

// instantiate the command object to fire

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

// attach the parameter to pass, if no parameter is in the sql no need to

attach

SqlParameter[] prms = new SqlParameter[2];

prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);

prms[0].Value = txtFirstName.Text.Trim();

prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);

prms[1].Value = txtLastName.Text.Trim();

 

cmd.Parameters.AddRange(prms);

conn.Open();

object obj = cmd.ExecuteScalar();

conn.Close();

 

if (obj != null)

{

lblMessage.ForeColor = System.Drawing.Color.Green;

lblMessage.Text = "Record exists and its AutoID is : " +

obj.ToString();

}

else

{

lblMessage.Text = "Record doesn't exists.";

}

}

}

}

On the click of the button, CheckRecord method executes. In this method, we have done almost same as we had done for the Insert method in the NormalCURD.aspx (in earlier article) page but instead of calling the ExecuteNonQuery method it calls ExecuteScalar method that returns an object (the first column of the first row of the result set returned by the SELECT statement), if no record is returned by the SELECT statements it returns NULL.

We can check for that object, if it is null then no record exists otherwise that record exists.

OUTPUT

Note: This is very frequently used to validate the credential (Username and Password) of the user in the Login Form.

Hope this was useful. Thanks for reading !

I have received many good feedback about my article series. Thanks to all of you for your support and keep supporting us. Do not forget to refer your friends about these articles, in this way you would be helping him/her to remain and excel in his/her job ! 

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)