Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Duplicate entries in SQL database

Posted on 2008-10-20
1
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 18

Accepted Solution

by:
mdougan earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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