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 duplicatethis is my code for inserting data to DB ltaspSqlDataSource ID34sqlDtSrcEmployees34 runat34server34 ConnectionString34lt ConnectionStringsKDUISv1ConnectionString gt34 InsertCommand34INSERT INTO IMSPerson GuidId F

Hey dorsaPlease refer below sampleHTMLltdivgt Name ltaspTextBox ID34txtName34 runat34server34 gtltbr gt Country ltaspTextBox ID34txtCountry34 runat34server34 gt ltaspSqlDataSource ID34sqlDtSrcEmployees34 runat34server34 ConnectionString34lt ConnectionStringsTestConnectionString gt34 InsertCommand34INSERT INTO Customers Name Country VALUES Name Country34 OnInserting34sqlDtSrcEmployeesInserting34gt ltInsertParametersgt ltaspParameter Name34Name34 Type34String34 gt ltaspParameter Name34Country34 Type34String34 gt ltInsertParametersgt ltaspSqlDataSourcegt ltaspButton Text34Insert34 runat34server34 OnClick34Insert34 gtltdivgtNamespacesCusing SystemDataSqlClientusing SystemConfigurationVBNetImports SystemDataSqlCl

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

dorsa

SOLVED CLOSED ACTIVE ANSWERED
User: dorsa
Posted: on Dec 11, 2018 02:08 AM
Forum: Data Controls
Answer: 1
Views: 8572
Sample Code: Download

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?

pandeyis...

0 4

Reply
ANSWER
User: pandeyism
Replied: on Dec 11, 2018 04:29 AM
Report

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

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

VB.Net

Imports System.Data.SqlClient

Code

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

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

Company

Explore

Follow us

Contact Us