Tajuddin's Blog

My little world of sharing

ASP.NET C# SQL VB.NET

Check for existing record using stored procedure in ASP.NET

Bytajuddin

Sep 12, 2011

Com And Hosting

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.

view source
01Create PROCEDURE [dbo].[wp_create_resident]    
02 
03        -- Add the parameters for the stored procedure here
04        @first_name varchar(60),
05        @last_name varchar(50),
06        @Address1 varchar(250),
07        @City varchar(50),
08        @country varchar(50),
09        @Telephone varchar(50),
10        @Email varchar(50),
11        @Website varchar(50),
12        @address2 varchar(150),
13        @ip_address varchar(50),
14        @Mobile varchar(50)   
15 
16    AS BEGIN 
17 
18-- SET NOCOUNT ON added to prevent extra result sets from
19-- interfering with SELECT statements.   
20 
21        SET NOCOUNT ON;
22        IF (EXISTS(SELECT last_name FROM dbo.temp_resident
23                        WHERE lower(First_name)=LOWER(@first_name)
24                        and lower(Last_name)=lower(@Last_name)
25                        and lower(Address1)=lower(@Address1)
26                        and LOWER(city)=LOWER(@city)))   
27 
28BEGIN
29            --raise error
30 
31            (N'Record exist with the same name.',16,-1);    
32 
33RETURN (0);   
34 
35END   
36 
37ELSE    
38 
39BEGIN   
40 
41INSERT INTO dbo.temp_resident
42        (first_name,
43        Last_name,
44        Address1,
45        City,
46        Country,
47        Telephone,
48        Email,
49        Website,
50        create_date,
51        address2,
52        ip_address,
53        Mobile,
54        approved)
55 
56VALUES
57    (@first_name,
58    @last_name,
59    @Address1,
60    @City,
61    @country,
62    @Telephone,
63    @Email,
64    @Website,
65    GETDATE(),
66    @address2,
67    @ip_address,
68    @Mobile,
69    'NO')   
70 
71    RETURN(1);
72END 
73 
74END

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.

view source
01Protected Sub btnbRegister_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnbRegister.Click       
02 
03Dim ipAddress As String
04Dim bus As New SqlDataSource()
05ipAddress = Request.ServerVariables("REMOTE_ADDR")       
06 
07'Variables declaration for sql connection
08Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ToString()
09Dim sqlConn As New SqlConnection(strConnString)       
10 
11'calling stored procedure to insert record
12Dim sqlcmd As New SqlCommand("wp_create_business", sqlConn)
13sqlcmd.CommandType = Data.CommandType.StoredProcedure       
14 
15'getting return value from stored procedure
16Dim returnValue As SqlParameter = New SqlParameter("returnValue", Data.SqlDbType.Int)       
17 
18returnValue.Direction = Data.ParameterDirection.ReturnValue
19sqlcmd.Parameters.Add(returnValue)     
20 
21'assigning insert parameters to stored procedure
22sqlcmd.Parameters.Add("@business_name", Data.SqlDbType.Text).Value = txtBname.Text
23sqlcmd.Parameters.Add("@Proprietor", Data.SqlDbType.Text).Value = txtProprietor.Text
24sqlcmd.Parameters.Add("@Address1", Data.SqlDbType.Text).Value = txtAddress1.Text
25sqlcmd.Parameters.Add("@Address2", Data.SqlDbType.Text).Value = txtAddress2.Text
26sqlcmd.Parameters.Add("@City", Data.SqlDbType.Text).Value = txtCity.Text
27sqlcmd.Parameters.Add("@Country", Data.SqlDbType.Text).Value = txtCountry.Text
28sqlcmd.Parameters.Add("@Telephone1", Data.SqlDbType.Text).Value = txtTel1.Text
29sqlcmd.Parameters.Add("@Telephone2", Data.SqlDbType.Text).Value = txtTel2.Text
30sqlcmd.Parameters.Add("@Mobile", Data.SqlDbType.Text).Value = txtMobile.Text
31sqlcmd.Parameters.Add("@Fax", Data.SqlDbType.Text).Value = txtFax.Text
32sqlcmd.Parameters.Add("@Email", Data.SqlDbType.Text).Value = txtEmail.Text
33sqlcmd.Parameters.Add("@website", Data.SqlDbType.Text).Value = txtWeb.Text
34sqlcmd.Parameters.Add("@Comments", Data.SqlDbType.Text).Value = txtcomments.Text
35sqlcmd.Parameters.Add("@ip_address", Data.SqlDbType.Text).Value = ipAddress
36sqlcmd.Parameters.Add("@catId", Data.SqlDbType.Int).Value = dlCategory.SelectedValue       
37 
38Try           
39 
40sqlConn.Open()           
41 
42sqlcmd.ExecuteNonQuery()
43sqlConn.Close()
44lblMsg.Text = ""       
45 
46Catch Exp As SqlException           
47 
48lblMsg.Text = "Sorry there is a technical error in our site."       
49 
50End Try       
51 
52Dim rowsaffected As Int32 = Convert.ToInt32(returnValue.Value)       
53 
54If rowsaffected = 1 Then           
55 
56'sending user to registration complete page           
57 
58Response.Redirect("~/resComplete.aspx")       
59 
60Else          
61 
62'displaying error message for record exist           
63 
64lblMsg.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."       
65 
66End If   
67 
68End Sub

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

view source
01protected void btnbRegister_Click(object sender, System.EventArgs e)
02{
03    string ipAddress = null;
04    SqlDataSource bus = new SqlDataSource();
05    ipAddress = Request.ServerVariables("REMOTE_ADDR");
06 
07    //Variables declaration for sql connection
08    string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ToString();
09    SqlConnection sqlConn = new SqlConnection(strConnString);
10 
11    //calling stored procedure to insert record
12    SqlCommand sqlcmd = new SqlCommand("wp_create_business", sqlConn);
13    sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
14 
15    //getting return value from stored procedure
16    SqlParameter returnValue = new SqlParameter("returnValue", System.Data.SqlDbType.Int);
17 
18    returnValue.Direction = System.Data.ParameterDirection.ReturnValue;
19    sqlcmd.Parameters.Add(returnValue);
20 
21    //assigning insert parameters to stored procedure
22    sqlcmd.Parameters.Add("@business_name", System.Data.SqlDbType.Text).Value = txtBname.Text;
23    sqlcmd.Parameters.Add("@Proprietor", System.Data.SqlDbType.Text).Value = txtProprietor.Text;
24    sqlcmd.Parameters.Add("@Address1", System.Data.SqlDbType.Text).Value = txtAddress1.Text;
25    sqlcmd.Parameters.Add("@Address2", System.Data.SqlDbType.Text).Value = txtAddress2.Text;
26    sqlcmd.Parameters.Add("@City", System.Data.SqlDbType.Text).Value = txtCity.Text;
27    sqlcmd.Parameters.Add("@Country", System.Data.SqlDbType.Text).Value = txtCountry.Text;
28    sqlcmd.Parameters.Add("@Telephone1", System.Data.SqlDbType.Text).Value = txtTel1.Text;
29    sqlcmd.Parameters.Add("@Telephone2", System.Data.SqlDbType.Text).Value = txtTel2.Text;
30    sqlcmd.Parameters.Add("@Mobile", System.Data.SqlDbType.Text).Value = txtMobile.Text;
31    sqlcmd.Parameters.Add("@Fax", System.Data.SqlDbType.Text).Value = txtFax.Text;
32    sqlcmd.Parameters.Add("@Email", System.Data.SqlDbType.Text).Value = txtEmail.Text;
33    sqlcmd.Parameters.Add("@website", System.Data.SqlDbType.Text).Value = txtWeb.Text;
34    sqlcmd.Parameters.Add("@Comments", System.Data.SqlDbType.Text).Value = txtcomments.Text;
35    sqlcmd.Parameters.Add("@ip_address", System.Data.SqlDbType.Text).Value = ipAddress;
36    sqlcmd.Parameters.Add("@catId", System.Data.SqlDbType.Int).Value = dlCategory.SelectedValue;
37 
38    try {
39        sqlConn.Open();
40 
41        sqlcmd.ExecuteNonQuery();
42        sqlConn.Close();
43        lblMsg.Text = "";
44 
45    } catch (SqlException Exp) {
46        lblMsg.Text = "Sorry there is a technical error in our site.";
47 
48    }
49 
50    Int32 rowsaffected = Convert.ToInt32(returnValue.Value);
51 
52    if (rowsaffected == 1) {
53        //sending user to registration complete page           
54 
55        Response.Redirect("~/resComplete.aspx");
56 
57    } else {
58        //displaying error message for record exist           
59 
60        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.";
61 
62    }
63 
64}

Any question then contact me.

 

By tajuddin

Related Post

APIM Azure C#

Setting up CORS in Azure APIM policy

Nov 27, 2023 tajuddin
ASP.NET C# Dotnet Core

jQuery Datatable with Blazor in ASP.NET 6

Apr 11, 2022 tajuddin
Azure C# Dotnet Core

Send message to Azure Service Bus Topic using dotnet core application

Dec 21, 2020 tajuddin
6 thought on “Check for existing record using stored procedure in ASP.NET”

  1. I’m extremely inspired together with your writing abilities and also with the format to your weblog. Is this a paid topic or did you customize it yourself? Anyway keep up the excellent high quality writing, it’s rare
    to look a nice weblog like this one today..

    Reply

  2. My developer is trying to convince me to move to .net from PHP.
    I have always disliked the idea because of the expenses. But he’s tryiong none the less. I’ve been using WordPress on numerous
    websites for about a year and am worried about switching to another platform.
    I have heard great things about blogengine.
    net. Is there a way I can import all my wordpress content into it?
    Any help would be really appreciated!

    Reply

  3. Howdy would you mind letting me know which web host you’re utilizing? I’ve loaded your
    blog in 3 different browsers and I must say this blog loads a lot faster then most.
    Can you suggest a good web hosting provider at a fair price?

    Thanks, I appreciate it!

    Reply

  4. hi, I used your sp model but it’s generating an error after the set nocount:

    Msg 102, Level 15, State 1, Procedure spICEPAGregistrationData2, Line 29
    Incorrect syntax near ‘Record exist with the same name.’.

    using sql server 2008.

    Reply

    1. Please send your procedure code otherwise it is hard to say what are you doing wrong in the code.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You missed

APIM Azure

Backup and restore Azure APIM

August 6, 2024 tajuddin
Cyber Security General Other

Why we should not save passwords in browsers – Cyber security awareness

March 3, 2024 tajuddin
APIM Azure C#

Setting up CORS in Azure APIM policy

November 27, 2023 tajuddin
Azure

Add Azure DevOps agent IP address to Storage Account firewall using PowerShell and CI/CD pipeline

February 23, 2023 tajuddin