Check duplicate before inserting to Database with ASP.Net SqlDataSource using C# and VB.Net

Last Reply 7 months ago By pandeyism

Posted 7 months ago

I have a code that insert a data to DB and i want to show message after inserting data to DB And i want to avoid insert duplicate data to DB and show error message that data is duplicate.

this is my code for inserting data to DB: 

                <asp:SqlDataSource ID="sqlDtSrcEmployees" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:KDUIS-v1ConnectionString %>"
                    InsertCommand="INSERT INTO [IMS_Person] ([GuidId], [FirstName], [LastName], [FatherName], [NationalCode], [BirthDate], [CertificateNo], [BirthPlace], [BirthCertificatePlace], [Gender], [ReligionId], [MaritalId], [Mobile], [Email], [CreateDate], [Description])
                    VALUES (@GuidId, @FirstName, @LastName, @FatherName, @NationalCode, @BirthDate, @CertificateNo, @BirthPlace, @BirthCertificatePlace, @Gender, @ReligionId, @MaritalID, @Mobile, @Email, @CreateDate,@Description)"
                   
                    OnInserting="sqlDtSrcEmployees_Inserting">
                    <InsertParameters>
                        <asp:Parameter Name="GuidId"  />
                        <asp:ControlParameter ControlID="txtName" Name="FirstName" PropertyName="Text"
                            Type="String" />
                        <asp:ControlParameter ControlID="txtFamily" Name="LastName" PropertyName="Text"
                            Type="String" />
                        <asp:ControlParameter ControlID="txtFathername" Name="FatherName" PropertyName="Text"
                            Type="String" />
                        <asp:ControlParameter ControlID="txtNationalcode" Name="NationalCode" PropertyName="Text"
                            Type="String" />
                        <asp:Parameter  Name="BirthDate" Type="DateTime" />

                        <asp:ControlParameter ControlID="txtCertificateNo" Name="CertificateNo" PropertyName="Text"
                            Type="String" />
                        <asp:ControlParameter ControlID="txtBirthPlace" Name="BirthPlace" PropertyName="Text"
                            Type="String" />
                        <asp:ControlParameter ControlID="txtBirthCertificatePlace" Name="BirthCertificatePlace" PropertyName="Text"
                            Type="String" />
                        <asp:Parameter  Name="Gender" Type="Int32" />

                        <asp:Parameter Name="ReligionId" Type="Int32" />

                        <asp:Parameter Name="MaritalID" Type="Int32" />

                        <asp:ControlParameter ControlID="txtMobile" Name="Mobile" PropertyName="Text"
                            Type="String" />
                        <asp:ControlParameter ControlID="txtEmail" Name="Email" PropertyName="Text"
                            Type="String" />
                        <asp:Parameter  Name="CreateDate"  Type="DateTime" />
                        <asp:ControlParameter ControlID="txtDescription" Name="Description" PropertyName="Text"
                            Type="String" />                       
                    </InsertParameters>
                </asp:SqlDataSource>

 

        protected void sqlDtSrcEmployees_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {
            Person obj = new Person();
            int genderid = obj.Gender(listGender.Text);
            int ReligionId = obj.Religion(listReligion.Text);
            int MaritalID = obj.Marital(listMaritial.Text);
            try
            {
                int day = Convert.ToInt32(txtday.Text);
                int month = Convert.ToInt32(txtmonth.Text);
                int year = Convert.ToInt32(txtyear.Text);

                System.Globalization.PersianCalendar faDate = new System.Globalization.PersianCalendar();
                e.Command.Parameters["@Birthdate"].Value = faDate.ToDateTime(year, month, day, 23, 0, 0, 0);
                e.Command.Parameters["GuidId"].Value = obj.GenerateGuid();
                e.Command.Parameters["@genderid"].Value = obj.Gender(listGender.Text);
                e.Command.Parameters["@ReligionId"].Value = obj.Religion(listReligion.Text);
                e.Command.Parameters["@MaritalID"].Value = obj.Marital(listReligion.Text);
                e.Command.Parameters["@CreateDateTime"].Value = obj.CreateDateTime();
            }
            catch { }
        }

 

        protected void Button1_Click(object sender, EventArgs e)
        {
            sqlDtSrcEmployees.Insert();
         }

how can i do this correctly?

You are viewing reply posted by: pandeyism 7 months ago.
Posted 7 months ago

Hey dorsa,

Please refer below sample.

HTML

<div>
    Name:
    <asp:TextBox ID="txtName" runat="server" /><br />
    Country:
    <asp:TextBox ID="txtCountry" runat="server" />
    <asp:SqlDataSource ID="sqlDtSrcEmployees" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
        InsertCommand="INSERT INTO [Customers] ([Name], [Country]) VALUES (@Name, @Country)"
        OnInserting="sqlDtSrcEmployees_Inserting">
        <InsertParameters>
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="Country" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
    <asp:Button Text="Insert" runat="server" OnClick="Insert" />
</div>

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void Insert(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Name FROM Customers WHERE Name = @Name", con))
        {
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            con.Open();
            string name = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
            if (string.IsNullOrEmpty(name))
            {
                sqlDtSrcEmployees.Insert();
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "CropImage", "alert('record exists');", true);
            }
        }
    }
}

protected void sqlDtSrcEmployees_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
    try
    {
        e.Command.Parameters["@Name"].Value = txtName.Text;
        e.Command.Parameters["@Country"].Value = txtCountry.Text;
    }
    catch
    {

    }
}

VB.Net

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT Name FROM Customers WHERE Name = @Name", con)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            con.Open()
            Dim name As String = Convert.ToString(cmd.ExecuteScalar())
            con.Close()
            If String.IsNullOrEmpty(name) Then
                sqlDtSrcEmployees.Insert()
            Else
                ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "CropImage", "alert('record exists');", True)
            End If
        End Using
    End Using
End Sub

Protected Sub sqlDtSrcEmployees_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
    Try
        e.Command.Parameters("@Name").Value = txtName.Text
        e.Command.Parameters("@Country").Value = txtCountry.Text
    Catch
    End Try
End Sub