Solved

Duplicate entries in SQL database

Posted on 2008-10-20
1
301 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
ID: 22757160
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

919 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

14 Experts available now in Live!

Get 1:1 Help Now