Solved

Duplicate entries in SQL database

Posted on 2008-10-20
1
300 Views
Last Modified: 2012-05-05
Hello i have attached my code for inserting a record below.
I have a checkbox which determines whether you are adding one or two clients.
If i add 1 client it works fine and just one record is added however if i tick the checkbox to add two clients i get 4 entries in the database - a duplicate for each client. I should also say that client 1 goes in twice and THEN client 2 goes in twice. Can anybody see why?
Private Sub saveAdd_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveAdd.Click

 

        Dim dbSourceNew = ConfigurationManager.AppSettings("dbSource")

        Dim objConnection As New SqlConnection("Persist Security Info=False;Initial Catalog=myo;Data Source=" + dbSourceNew + ";User Id=******;Password=******")

        Dim objCommand As SqlCommand = New SqlCommand()

        Dim objCommandtwo As SqlCommand = New SqlCommand()

 

 

 

        ' if there is a partner being added then add both to the database

        If (CheckBoxX1.CheckState = CheckState.Checked) Then

 

            'Open the connection

            objConnection.Open()

 

            'set the sqlcommand properties...

            objCommand.Connection = objConnection

            objCommand.CommandText = "INSERT INTO client " & _

                "(client_name,client_surname,client_address1,client_address2,client_town,client_postcode," & _

                "client_phone,client_dob,client_email,client_adviser,client_title,client_sex,client_mobile," & _

                "client_work_phone,client_prefered_contact,client_realname,client_country,client_natins,client_grade)" & _

                " VALUES(@client_name,@client_surname,@client_address1,@client_address2,@client_town," & _

                "@client_postcode,@client_phone,@client_dob,@client_email,@client_adviser,@client_title," & _

                "@client_sex,@client_mobile,@client_work_phone,@client_prefered_contact,@client_realname,@client_country," & _

                "@client_natins,@client_grade);" & _

                "Select Scope_Identity()"

            Dim IDone As Integer

 

            'parameters for placeholders in the SQL commandtext property

            objCommand.Parameters.AddWithValue("@client_name", cName.Text)

            objCommand.Parameters.AddWithValue("@client_surname", cSurname.Text)

            objCommand.Parameters.AddWithValue("@client_address1", cAdd1.Text)

            objCommand.Parameters.AddWithValue("@client_address2", cAdd2.Text)

            objCommand.Parameters.AddWithValue("@client_town", cTown.Text)

            objCommand.Parameters.AddWithValue("@client_postcode", cPostcode.Text)

            objCommand.Parameters.AddWithValue("@client_phone", cPhone.Text)

            objCommand.Parameters.AddWithValue("@client_dob", cDOB.Value)

            objCommand.Parameters.AddWithValue("@client_email", cEmail.Text)

            objCommand.Parameters.AddWithValue("@client_adviser", cAdviser.Text)

            objCommand.Parameters.AddWithValue("@client_title", cTitle.Text)

            objCommand.Parameters.AddWithValue("@client_sex", cSex.Text)

            objCommand.Parameters.AddWithValue("@client_mobile", cMobile.Text)

            objCommand.Parameters.AddWithValue("@client_work_phone", cWork.Text)

            objCommand.Parameters.AddWithValue("@client_prefered_contact", cPreference.Text)

            objCommand.Parameters.AddWithValue("@client_realname", cRealname.Text)

            objCommand.Parameters.AddWithValue("@client_country", cCountry.Text)

            objCommand.Parameters.AddWithValue("@client_natins", cNatins.Text)

            objCommand.Parameters.AddWithValue("@client_grade", cGrade.Text)

 

            'execute the SqlCommand to insert new data

            Try

                objCommand.ExecuteNonQuery()

                IDone = objCommand.ExecuteScalar()

            Catch SqlExceptionErr As Exception

                MessageBox.Show(SqlExceptionErr.Message)

            End Try

 

            objCommandtwo.Connection = objConnection

            objCommandtwo.CommandText = "INSERT INTO client " & _

                "(client_name,client_surname,client_address1,client_address2,client_town,client_postcode," & _

                "client_phone,client_dob,client_email,client_adviser,client_title,client_sex,client_mobile," & _

                "client_work_phone,client_prefered_contact,client_realname,client_country,client_natins,client_grade)" & _

                " VALUES(@partner_name,@partner_surname,@partner_address1,@partner_address2,@partner_town," & _

                "@partner_postcode,@partner_phone,@partner_dob,@partner_email,@partner_adviser,@partner_title," & _

                "@partner_sex,@partner_mobile,@partner_work_phone,@partner_prefered_contact,@partner_realname,@partner_country," & _

                "@partner_natins,@partner_grade);" & _

                "Select Scope_Identity()"

            Dim IDtwo As Integer

 

            'parameters for placeholders in the SQL commandtext property

            objCommandtwo.Parameters.AddWithValue("@partner_name", pName.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_surname", pSurname.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_address1", pAdd1.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_address2", pAdd2.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_town", pTown.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_postcode", pPostcode.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_phone", pPhone.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_dob", pDOB.Value)

            objCommandtwo.Parameters.AddWithValue("@partner_email", pEmail.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_adviser", pAdviser.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_title", pTitle.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_sex", pSex.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_mobile", pMobile.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_work_phone", pWork.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_prefered_contact", pPreference.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_realname", pRealname.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_country", pCountry.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_natins", pNatins.Text)

            objCommandtwo.Parameters.AddWithValue("@partner_grade", pGrade.Text)

 

            'execute the SqlCommand to insert new data

            Try

                objCommandtwo.ExecuteNonQuery()

                IDtwo = objCommandtwo.ExecuteScalar()

            Catch SqlExceptionErr As Exception

                MessageBox.Show(SqlExceptionErr.Message)

            End Try

 

            'Close the connection

            objConnection.Close()

 

            'hide the form and display a message to toolstrip.

            myoMain.ToolStripStatusLabel1.Text = IDtwo.ToString

 

 

 

 

            ' else just add the single client

        Else

 

            'Open the connection

            objConnection.Open()

 

            'set the sqlcommand properties...

            objCommand.Connection = objConnection

            objCommand.CommandText = "INSERT INTO client " & _

                "(client_name,client_surname,client_address1,client_address2,client_town,client_postcode," & _

                "client_phone,client_dob,client_email,client_adviser,client_title,client_sex,client_mobile," & _

                "client_work_phone,client_prefered_contact,client_realname,client_country,client_natins,client_grade) " & _

                " VALUES(@client_name,@client_surname,@client_address1,@client_address2,@client_town," & _

                "@client_postcode,@client_phone,@client_dob,@client_email,@client_adviser,@client_title," & _

                "@client_sex,@client_mobile,@client_work_phone,@client_prefered_contact,@client_realname,@client_country," & _

                "@client_natins,@client_grade)"

 

            'parameters for placeholders in the SQL commandtext property

 

            objCommand.Parameters.AddWithValue("@client_name", cName.Text)

            objCommand.Parameters.AddWithValue("@client_surname", cSurname.Text)

            objCommand.Parameters.AddWithValue("@client_address1", cAdd1.Text)

            objCommand.Parameters.AddWithValue("@client_address2", cAdd2.Text)

            objCommand.Parameters.AddWithValue("@client_town", cTown.Text)

            objCommand.Parameters.AddWithValue("@client_postcode", cPostcode.Text)

            objCommand.Parameters.AddWithValue("@client_phone", cPhone.Text)

            objCommand.Parameters.AddWithValue("@client_dob", cDOB.Value)

            objCommand.Parameters.AddWithValue("@client_email", cEmail.Text)

            objCommand.Parameters.AddWithValue("@client_adviser", cAdviser.Text)

            objCommand.Parameters.AddWithValue("@client_title", cTitle.Text)

            objCommand.Parameters.AddWithValue("@client_sex", cSex.Text)

            objCommand.Parameters.AddWithValue("@client_mobile", cMobile.Text)

            objCommand.Parameters.AddWithValue("@client_work_phone", cWork.Text)

            objCommand.Parameters.AddWithValue("@client_prefered_contact", cPreference.Text)

            objCommand.Parameters.AddWithValue("@client_realname", cRealname.Text)

            objCommand.Parameters.AddWithValue("@client_country", cCountry.Text)

            objCommand.Parameters.AddWithValue("@client_natins", cNatins.Text)

            objCommand.Parameters.AddWithValue("@client_grade", cGrade.Text)

 

 

            'execute the SqlCommand to insert new data

            Try

                objCommand.ExecuteNonQuery()

            Catch SqlExceptionErr As Exception

                MessageBox.Show(SqlExceptionErr.Message)

            End Try

 

            'Close the connection

            objConnection.Close()

 

            'hide the form and display a message to toolstrip.

            myoMain.ToolStripStatusLabel1.Text = "Client Added to the Database!"

 

 

        End If

 

        myoMain.windowOpen = False

        Me.Close()

 

    End Sub

Open in new window

0
Comment
Question by:drews1f
1 Comment
 
LVL 18

Accepted Solution

by:
mdougan earned 500 total points
Comment Utility
In the Else condition, you are simply executing the
                objCommand.ExecuteNonQuery()
In the IF condition you are executing the query twice
                objCommandtwo.ExecuteNonQuery()
                IDtwo = objCommandtwo.ExecuteScalar()
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now