Check for existing record using stored procedure in ASP.NET

Check for existing record in SQL Server database using stored procedure

In this example I will explain, how to check for record exist before insert data into a table in sql server 2005 or 2008.

In one of my web project I had to implement some sort of validation to check for existing record in the database before inserting. This is quite important when you try to avoid duplicate entry or multiple registrations and so on. Specially when you have self registering facility in the website, it is a good practice to maintain data quality by checking for any duplicate record in the database before insert action. If record exists with the same information provided by the user it will flag user with error message and prevent from inserting another record. To implement this functionality in asp.net website I have used stored procedure.

Store procedures are considered to be better approach for database interaction than normal sql statement for this type of operation. I already have a resident table with those fields showing below, now creating a procedure to work with that table.

Create PROCEDURE [dbo].[wp_create_resident]     

        -- Add the parameters for the stored procedure here
        @first_name varchar(60),
        @last_name varchar(50),
        @Address1 varchar(250),
        @City varchar(50),
        @country varchar(50),
        @Telephone varchar(50),
        @Email varchar(50),
        @Website varchar(50),
        @address2 varchar(150),
        @ip_address varchar(50),
        @Mobile varchar(50)    

    AS BEGIN  

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.    

        SET NOCOUNT ON;
        IF (EXISTS(SELECT last_name FROM dbo.temp_resident
                        WHERE lower(First_name)=LOWER(@first_name)
                        and lower(Last_name)=lower(@Last_name)
                        and lower(Address1)=lower(@Address1)
                        and LOWER(city)=LOWER(@city)))    

BEGIN
            --raise error

            (N'Record exist with the same name.',16,-1);     

RETURN (0);    

END    

ELSE     

BEGIN    

INSERT INTO dbo.temp_resident
        (first_name,
        Last_name,
        Address1,
        City,
        Country,
        Telephone,
        Email,
        Website,
        create_date,
        address2,
        ip_address,
        Mobile,
        approved) 

VALUES
    (@first_name,
    @last_name,
    @Address1,
    @City,
    @country,
    @Telephone,
    @Email,
    @Website,
    GETDATE(),
    @address2,
    @ip_address,
    @Mobile,
    'NO')    

    RETURN(1);
END  

END

In the aspx page I have created all the associated fields with a submit button id btnRegister. I have not included the aspx page code here. Now in the aspx code page, I have used the following VB code for calling the store procedure and execute.

Protected Sub btnbRegister_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnbRegister.Click        

Dim ipAddress As String
Dim bus As New SqlDataSource()
ipAddress = Request.ServerVariables("REMOTE_ADDR")        

'Variables declaration for sql connection
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ToString()
Dim sqlConn As New SqlConnection(strConnString)        

'calling stored procedure to insert record
Dim sqlcmd As New SqlCommand("wp_create_business", sqlConn)
sqlcmd.CommandType = Data.CommandType.StoredProcedure        

'getting return value from stored procedure
Dim returnValue As SqlParameter = New SqlParameter("returnValue", Data.SqlDbType.Int)        

returnValue.Direction = Data.ParameterDirection.ReturnValue
sqlcmd.Parameters.Add(returnValue)		

'assigning insert parameters to stored procedure
sqlcmd.Parameters.Add("@business_name", Data.SqlDbType.Text).Value = txtBname.Text
sqlcmd.Parameters.Add("@Proprietor", Data.SqlDbType.Text).Value = txtProprietor.Text
sqlcmd.Parameters.Add("@Address1", Data.SqlDbType.Text).Value = txtAddress1.Text
sqlcmd.Parameters.Add("@Address2", Data.SqlDbType.Text).Value = txtAddress2.Text
sqlcmd.Parameters.Add("@City", Data.SqlDbType.Text).Value = txtCity.Text
sqlcmd.Parameters.Add("@Country", Data.SqlDbType.Text).Value = txtCountry.Text
sqlcmd.Parameters.Add("@Telephone1", Data.SqlDbType.Text).Value = txtTel1.Text
sqlcmd.Parameters.Add("@Telephone2", Data.SqlDbType.Text).Value = txtTel2.Text
sqlcmd.Parameters.Add("@Mobile", Data.SqlDbType.Text).Value = txtMobile.Text
sqlcmd.Parameters.Add("@Fax", Data.SqlDbType.Text).Value = txtFax.Text
sqlcmd.Parameters.Add("@Email", Data.SqlDbType.Text).Value = txtEmail.Text
sqlcmd.Parameters.Add("@website", Data.SqlDbType.Text).Value = txtWeb.Text
sqlcmd.Parameters.Add("@Comments", Data.SqlDbType.Text).Value = txtcomments.Text
sqlcmd.Parameters.Add("@ip_address", Data.SqlDbType.Text).Value = ipAddress
sqlcmd.Parameters.Add("@catId", Data.SqlDbType.Int).Value = dlCategory.SelectedValue        

Try            

sqlConn.Open()            

sqlcmd.ExecuteNonQuery()
sqlConn.Close()
lblMsg.Text = ""        

Catch Exp As SqlException            

lblMsg.Text = "Sorry there is a technical error in our site."        

End Try        

Dim rowsaffected As Int32 = Convert.ToInt32(returnValue.Value)        

If rowsaffected = 1 Then            

'sending user to registration complete page            

Response.Redirect("~/resComplete.aspx")        

Else           

'displaying error message for record exist            

lblMsg.Text = "The business name you are trying to submit is already exist in our database, please make sure your business name is not exist in our system before submitting. Please contact us if you have any query regarding this."        

End If    

End Sub

If you are doing in C# then use the code below –

protected void btnbRegister_Click(object sender, System.EventArgs e)
{
	string ipAddress = null;
	SqlDataSource bus = new SqlDataSource();
	ipAddress = Request.ServerVariables("REMOTE_ADDR");

	//Variables declaration for sql connection
	string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ToString();
	SqlConnection sqlConn = new SqlConnection(strConnString);

	//calling stored procedure to insert record
	SqlCommand sqlcmd = new SqlCommand("wp_create_business", sqlConn);
	sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;

	//getting return value from stored procedure
	SqlParameter returnValue = new SqlParameter("returnValue", System.Data.SqlDbType.Int);

	returnValue.Direction = System.Data.ParameterDirection.ReturnValue;
	sqlcmd.Parameters.Add(returnValue);

	//assigning insert parameters to stored procedure
	sqlcmd.Parameters.Add("@business_name", System.Data.SqlDbType.Text).Value = txtBname.Text;
	sqlcmd.Parameters.Add("@Proprietor", System.Data.SqlDbType.Text).Value = txtProprietor.Text;
	sqlcmd.Parameters.Add("@Address1", System.Data.SqlDbType.Text).Value = txtAddress1.Text;
	sqlcmd.Parameters.Add("@Address2", System.Data.SqlDbType.Text).Value = txtAddress2.Text;
	sqlcmd.Parameters.Add("@City", System.Data.SqlDbType.Text).Value = txtCity.Text;
	sqlcmd.Parameters.Add("@Country", System.Data.SqlDbType.Text).Value = txtCountry.Text;
	sqlcmd.Parameters.Add("@Telephone1", System.Data.SqlDbType.Text).Value = txtTel1.Text;
	sqlcmd.Parameters.Add("@Telephone2", System.Data.SqlDbType.Text).Value = txtTel2.Text;
	sqlcmd.Parameters.Add("@Mobile", System.Data.SqlDbType.Text).Value = txtMobile.Text;
	sqlcmd.Parameters.Add("@Fax", System.Data.SqlDbType.Text).Value = txtFax.Text;
	sqlcmd.Parameters.Add("@Email", System.Data.SqlDbType.Text).Value = txtEmail.Text;
	sqlcmd.Parameters.Add("@website", System.Data.SqlDbType.Text).Value = txtWeb.Text;
	sqlcmd.Parameters.Add("@Comments", System.Data.SqlDbType.Text).Value = txtcomments.Text;
	sqlcmd.Parameters.Add("@ip_address", System.Data.SqlDbType.Text).Value = ipAddress;
	sqlcmd.Parameters.Add("@catId", System.Data.SqlDbType.Int).Value = dlCategory.SelectedValue;

	try {
		sqlConn.Open();

		sqlcmd.ExecuteNonQuery();
		sqlConn.Close();
		lblMsg.Text = "";

	} catch (SqlException Exp) {
		lblMsg.Text = "Sorry there is a technical error in our site.";

	}

	Int32 rowsaffected = Convert.ToInt32(returnValue.Value);

	if (rowsaffected == 1) {
		//sending user to registration complete page            

		Response.Redirect("~/resComplete.aspx");

	} else {
		//displaying error message for record exist            

		lblMsg.Text = "The business name you are trying to submit is already exist in our database, please make sure your business name is not exist in our system before submitting. Please contact us if you have any query regarding this.";

	}

}

Any question then contact me.