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 ! 

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.