Question ConstraintException was unhandled

D

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
I perform a calculation in order to add 2 or 3 rows of data to my sql server 2005 database.

However I'm having problems stopping vb.net generating a exception for duplicate records.

VB.NET: 
    Private Sub SaveData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveData.Click
        Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "select * from VsrData"
        Dim vsrsql As String = "select * from RatingsData"

        Dim conn As SqlConnection = New SqlConnection(connstring)

        If (Form1.showndata = 1 And Form1.datedata = 0 And Form1.racedata = 0) Then
            builder = New SqlCommandBuilder(Form1.shownda)

            conn.Open()

            builder.GetUpdateCommand()
            Form1.shownda.Update(Form1.shownds, "VsrData")
            Form1.shownds.Clear()
            Form1.shownda.Fill(Form1.shownds, "VsrData")
            DataGridView1.DataSource = Form1.shownds.Tables("VsrData")

            If Form1.VsrDataDataGridView.Rows(0).Cells(8).Value > 2 Then
                SaveRow0()
                SaveRow1()
                SaveRow2()
                conn.Close()
            ElseIf Form1.VsrDataDataGridView.Rows(0).Cells(8).Value < 3 Then
                SaveRow0()
                SaveRow1()
                conn.Close()
            Else
                ' Do Nothing
            End If
        ElseIf (Form1.showndata = 0 And Form1.datedata = 1 And Form1.racedata = 0) Then

            builder = New SqlCommandBuilder(Form1.dateda)

            conn.Open()

            builder.GetUpdateCommand()
            Form1.dateda.Update(Form1.dateds, "VsrData")
            Form1.dateds.Clear()
            Form1.dateda.Fill(Form1.dateds, "VsrData")
            DataGridView1.DataSource = Form1.dateds.Tables("VsrData")


            If Form1.VsrDataDataGridView.Rows(0).Cells(8).Value > 2 Then
                SaveRow0()
                SaveRow1()
                SaveRow2()
                conn.Close()
            ElseIf Form1.VsrDataDataGridView.Rows(0).Cells(8).Value < 3 Then
                SaveRow0()
                SaveRow1()
                conn.Close()
            Else
                ' Do Nothing
            End If
        ElseIf (Form1.showndata = 0 And Form1.datedata = 0 And Form1.racedata = 1) Then

            builder = New SqlCommandBuilder(Form1.raceda)

            conn.Open()

            builder.GetUpdateCommand()
            Form1.raceda.Update(Form1.raceds, "VsrData")
            Form1.raceds.Clear()
            Form1.raceda.Fill(Form1.raceds, "VsrData")
            DataGridView1.DataSource = Form1.raceds.Tables("VsrData")

            MsgBox(Form1.VsrDataDataGridView.Rows(0).Cells(8).Value.ToString)
            If Form1.VsrDataDataGridView.Rows(0).Cells(8).Value > 2 Then
                SaveRow0()
                SaveRow1()
                SaveRow2()
                conn.Close()
            ElseIf Form1.VsrDataDataGridView.Rows(0).Cells(8).Value < 3 Then
                SaveRow0()
                SaveRow1()
                conn.Close()
            Else
                ' Do Nothing
            End If

        End If


    End Sub

    Private Sub SaveRow0()
        DupAlertCount1 = 0
        Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "select * from VsrData"
        Dim vsrsql As String = "select * from RatingsData"

        Dim conn As SqlConnection = New SqlConnection(connstring)

        VsrDataRow = Me.EquinexMasterSqlDataSet.RatingsData.NewRow()

        VsrDataRow.CustomRecordId = Form1.VsrDataDataGridView.Rows(0).Cells(0).Value
        VsrDataRow.RaceDate = Form1.VsrDataDataGridView.Rows(0).Cells(1).Value
        VsrDataRow.Time = Form1.VsrDataDataGridView.Rows(0).Cells(2).Value
        VsrDataRow.Venue = Form1.VsrDataDataGridView.Rows(0).Cells(3).Value
        VsrDataRow.CHF = Form1.VsrDataDataGridView.Rows(0).Cells(4).Value
        VsrDataRow.Hcap = Form1.VsrDataDataGridView.Rows(0).Cells(5).Value
        VsrDataRow.RaceMiles = Form1.VsrDataDataGridView.Rows(0).Cells(6).Value
        VsrDataRow.RaceFurlongs = Form1.VsrDataDataGridView.Rows(0).Cells(7).Value
        VsrDataRow.RaceRunners = Form1.VsrDataDataGridView.Rows(0).Cells(8).Value
        VsrDataRow.RaceClass = Form1.VsrDataDataGridView.Rows(0).Cells(9).Value
        VsrDataRow.Going = Form1.VsrDataDataGridView.Rows(0).Cells(10).Value
        VsrDataRow.RaceTitle = Form1.VsrDataDataGridView.Rows(0).Cells(11).Value
        VsrDataRow.SaddleCloth = Form1.VsrDataDataGridView.Rows(0).Cells(13).Value
        VsrDataRow.HorseName = Form1.VsrDataDataGridView.Rows(0).Cells(14).Value
        VsrDataRow.Lf3 = Form1.VsrDataDataGridView.Rows(0).Cells(15).Value
        VsrDataRow.Lf2 = Form1.VsrDataDataGridView.Rows(0).Cells(16).Value
        VsrDataRow.Lf1 = Form1.VsrDataDataGridView.Rows(0).Cells(17).Value
        VsrDataRow.LastRan = Form1.VsrDataDataGridView.Rows(0).Cells(18).Value
        VsrDataRow.PS = DataGridView1.Rows(0).Cells(13).Value
        VsrDataRow.Rating = DataGridView1.Rows(0).Cells(14).Value
        VsrDataRow.FDB = DataGridView1.Rows(0).Cells(15).Value
        VsrDataRow.B = DataGridView1.Rows(0).Cells(16).Value
        VsrDataRow.BettingPos = Form1.VsrDataDataGridView.Rows(0).Cells(19).Value
        VsrDataRow.FcPrice = Form1.VsrDataDataGridView.Rows(0).Cells(22).Value

        'Me.RatingsDataTableAdapter.Fill(Me.EquinexMasterSqlDataSet.RatingsData) ', "RatingsData")
        RatingsRow1 = Me.EquinexMasterSqlDataSet.RatingsData.Rows.Count
        MsgBox("sr0 rr1 " + RatingsRow1.ToString)


        MsgBox("Row 0 " + RatingsRow1.ToString)
        If (RatingsRow1 = 0) Then
            Try
                Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
            Catch
            End Try
        Else

            For DupRaceCount1 = 0 To EquinexMasterSqlDataSet.RatingsData.Rows.Count - 1 Step 1
                If (VsrDataRow.CustomRecordId.ToString.Trim = EquinexMasterSqlDataSet.RatingsData.Rows(DupRaceCount1).Item(EquinexMasterSqlDataSet.RatingsData.CustomRecordIdColumn).ToString.Trim) Then
                    If (DupAlertCount0 = 0) Then
                        MsgBox("715 - This race has already been calculated")
                        DupAlertCount0 = 1
                        conn.Close()
                        'Exit Sub
                    Else
                        'DupAlertCount0 = 0
                        Me.EquinexMasterSqlDataSet.RatingsData.Rows.Add(VsrDataRow)
                        conn.Close()
                        'Exit Sub
                    End If
                Else
                    ' Do Nothing
                End If
            Next
            If (DupAlertCount0 = 1) Then
                ' Do Nothing
            Else
                Try
                    Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
                Catch
                End Try
            End If
        End If
    End Sub

    Private Sub SaveRow1()
        DupAlertCount1 = 0
        Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "select * from VsrData"
        Dim vsrsql As String = "select * from RatingsData"

        Dim conn As SqlConnection = New SqlConnection(connstring)

        VsrDataRow = Me.EquinexMasterSqlDataSet.RatingsData.NewRow()

        VsrDataRow.CustomRecordId = Form1.VsrDataDataGridView.Rows(1).Cells(0).Value
        VsrDataRow.RaceDate = Form1.VsrDataDataGridView.Rows(1).Cells(1).Value
        VsrDataRow.Time = Form1.VsrDataDataGridView.Rows(1).Cells(2).Value
        VsrDataRow.Venue = Form1.VsrDataDataGridView.Rows(1).Cells(3).Value
        VsrDataRow.CHF = Form1.VsrDataDataGridView.Rows(1).Cells(4).Value
        VsrDataRow.Hcap = Form1.VsrDataDataGridView.Rows(1).Cells(5).Value
        VsrDataRow.RaceMiles = Form1.VsrDataDataGridView.Rows(1).Cells(6).Value
        VsrDataRow.RaceFurlongs = Form1.VsrDataDataGridView.Rows(1).Cells(7).Value
        VsrDataRow.RaceRunners = Form1.VsrDataDataGridView.Rows(1).Cells(8).Value
        VsrDataRow.RaceClass = Form1.VsrDataDataGridView.Rows(1).Cells(9).Value
        VsrDataRow.Going = Form1.VsrDataDataGridView.Rows(1).Cells(10).Value
        VsrDataRow.RaceTitle = Form1.VsrDataDataGridView.Rows(1).Cells(11).Value
        VsrDataRow.SaddleCloth = Form1.VsrDataDataGridView.Rows(1).Cells(13).Value
        VsrDataRow.HorseName = Form1.VsrDataDataGridView.Rows(1).Cells(14).Value
        VsrDataRow.Lf3 = Form1.VsrDataDataGridView.Rows(1).Cells(15).Value
        VsrDataRow.Lf2 = Form1.VsrDataDataGridView.Rows(1).Cells(16).Value
        VsrDataRow.Lf1 = Form1.VsrDataDataGridView.Rows(1).Cells(17).Value
        VsrDataRow.LastRan = Form1.VsrDataDataGridView.Rows(1).Cells(18).Value
        VsrDataRow.PS = DataGridView1.Rows(1).Cells(13).Value
        VsrDataRow.Rating = DataGridView1.Rows(1).Cells(14).Value
        VsrDataRow.FDB = DataGridView1.Rows(1).Cells(15).Value
        VsrDataRow.B = DataGridView1.Rows(1).Cells(16).Value
        VsrDataRow.BettingPos = Form1.VsrDataDataGridView.Rows(1).Cells(19).Value
        VsrDataRow.FcPrice = Form1.VsrDataDataGridView.Rows(1).Cells(22).Value

        If (Form1.VsrDataDataGridView.Rows(1).Cells(7).Value < 3) Then
            VsrDataRow.EndOfRace = "Y"
            VsrDataRow.EndOfMeeting = "Y"
        Else
            ' Do Nothing
        End If


        RatingsRow1 = Me.EquinexMasterSqlDataSet.RatingsData.Rows.Count
        MsgBox("sr1 rr1 " + RatingsRow1.ToString)
        Me.EquinexMasterSqlDataSet.RatingsData.Rows.Add(VsrDataRow)
        If (RatingsRow1 = 0) Then
            Try
                Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
            Catch
            End Try
        Else
            For DupRaceCount1 = 0 To EquinexMasterSqlDataSet.RatingsData.Rows.Count - 1 Step 1
                If (VsrDataRow.CustomRecordId.ToString.Trim = EquinexMasterSqlDataSet.RatingsData.Rows(DupRaceCount1).Item(EquinexMasterSqlDataSet.RatingsData.CustomRecordIdColumn).ToString.Trim) Then
                    If (DupAlertCount1 = 0) Then
                        MsgBox("766 - This race has already been calculated")
                        DupAlertCount1 = 1
                        conn.Close()
                        Exit Sub
                    Else
                        conn.Close()
                        Exit Sub
                    End If
                End If
            Next
            If (DupAlertCount1 = 1) Then
                ' Do Nothing
            Else
                Try
                    Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
                Catch
                End Try
            End If
        End If


    End Sub

    Private Sub SaveRow2()
        DupAlertCount2 = 0
        Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "select * from VsrData"
        Dim vsrsql As String = "select * from RatingsData"

        Dim conn As SqlConnection = New SqlConnection(connstring)

        VsrDataRow = Me.EquinexMasterSqlDataSet.RatingsData.NewRow()

        VsrDataRow.CustomRecordId = Form1.VsrDataDataGridView.Rows(2).Cells(0).Value
        VsrDataRow.RaceDate = Form1.VsrDataDataGridView.Rows(2).Cells(1).Value
        VsrDataRow.Time = Form1.VsrDataDataGridView.Rows(2).Cells(2).Value
        VsrDataRow.Venue = Form1.VsrDataDataGridView.Rows(2).Cells(3).Value
        VsrDataRow.CHF = Form1.VsrDataDataGridView.Rows(2).Cells(4).Value
        VsrDataRow.Hcap = Form1.VsrDataDataGridView.Rows(2).Cells(5).Value
        VsrDataRow.RaceMiles = Form1.VsrDataDataGridView.Rows(2).Cells(6).Value
        VsrDataRow.RaceFurlongs = Form1.VsrDataDataGridView.Rows(2).Cells(7).Value
        VsrDataRow.RaceRunners = Form1.VsrDataDataGridView.Rows(2).Cells(8).Value
        VsrDataRow.RaceClass = Form1.VsrDataDataGridView.Rows(2).Cells(9).Value
        VsrDataRow.Going = Form1.VsrDataDataGridView.Rows(2).Cells(10).Value
        VsrDataRow.RaceTitle = Form1.VsrDataDataGridView.Rows(2).Cells(11).Value
        VsrDataRow.SaddleCloth = Form1.VsrDataDataGridView.Rows(2).Cells(13).Value
        VsrDataRow.HorseName = Form1.VsrDataDataGridView.Rows(2).Cells(14).Value
        VsrDataRow.Lf3 = Form1.VsrDataDataGridView.Rows(2).Cells(15).Value
        VsrDataRow.Lf2 = Form1.VsrDataDataGridView.Rows(2).Cells(16).Value
        VsrDataRow.Lf1 = Form1.VsrDataDataGridView.Rows(2).Cells(17).Value
        VsrDataRow.LastRan = Form1.VsrDataDataGridView.Rows(2).Cells(18).Value
        VsrDataRow.PS = DataGridView1.Rows(2).Cells(13).Value
        VsrDataRow.Rating = DataGridView1.Rows(2).Cells(14).Value
        VsrDataRow.FDB = DataGridView1.Rows(2).Cells(15).Value
        VsrDataRow.B = DataGridView1.Rows(2).Cells(16).Value
        VsrDataRow.BettingPos = Form1.VsrDataDataGridView.Rows(2).Cells(19).Value
        VsrDataRow.FcPrice = Form1.VsrDataDataGridView.Rows(2).Cells(22).Value

        If (Form1.VsrDataDataGridView.Rows(2).Cells(7).Value > 2) Then
            VsrDataRow.EndOfRace = "Y"
            VsrDataRow.EndOfMeeting = "Y"
        Else
            ' Do Nothing
        End If
        RatingsRow1 = Me.EquinexMasterSqlDataSet.RatingsData.Rows.Count
        MsgBox("sr2 rr1 " + RatingsRow1.ToString)
        Me.EquinexMasterSqlDataSet.RatingsData.Rows.Add(VsrDataRow)

        'MsgBox("Row 2 " + RatingsRow1.ToString)
        If (RatingsRow1 = 0) Then
            Try
                Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
            Catch
            End Try
        Else
            For DupRaceCount1 = 0 To EquinexMasterSqlDataSet.RatingsData.Rows.Count - 1 Step 1
                If (VsrDataRow.CustomRecordId.ToString.Trim = EquinexMasterSqlDataSet.RatingsData.Rows(DupRaceCount1).Item(EquinexMasterSqlDataSet.RatingsData.CustomRecordIdColumn).ToString.Trim) Then
                    If (DupAlertCount2 = 0) Then
                        MsgBox("821 - This race has already been calculated")
                        DupAlertCount2 = 1
                        conn.Close()
                        Exit Sub
                    Else
                        conn.Close()
                        Exit Sub
                    End If
                End If
            Next

        If (DupAlertCount2 = 1) Then
            ' Do Nothing
        Else
                Try
                    Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
                Catch
                End Try
        End If
        End If
    End Sub
 
Sort by date Sort by votes
Er.. what's your question? Am I supposed to read through all 1000 of lines code you pasted, work out where the error is and tell you? I dont have the time, sorry.. If you can help me help you by narrowing it down a bit, that would be great
 
Upvote 0 Downvote
Question ConstraintException was unhandled

I have a calculation procedure which adds its results to a database.

As the database is setup to prevent the adding of duplicate records, on some occasions my app throws an exception at the line highlighted in red.

I am trying to find a easy way to stop duplicate records getting added to the database.

Thanks

VB.NET: 
    Private Sub SaveRow1()
        DupAlertCount1 = 0
        Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "select * from VsrData"
        Dim vsrsql As String = "select * from RatingsData"

        Dim conn As SqlConnection = New SqlConnection(connstring)

        VsrDataRow = Me.EquinexMasterSqlDataSet.RatingsData.NewRow()

        If (Form1.VsrDataDataGridView.Rows(1).Cells(7).Value < 3) Then
            VsrDataRow.EndOfRace = "Y"
            VsrDataRow.EndOfMeeting = "Y"
        Else
            ' Do Nothing
        End If


        RatingsRow1 = Me.EquinexMasterSqlDataSet.RatingsData.Rows.Count
        MsgBox("sr1 rr1 " + RatingsRow1.ToString)
[COLOR="Red"]Me.EquinexMasterSqlDataSet.RatingsData.Rows.Add(VsrDataRow)[/COLOR]

        If (RatingsRow1 = 0) Then
            Try
                Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
            Catch
            End Try
        Else
            For DupRaceCount1 = 0 To EquinexMasterSqlDataSet.RatingsData.Rows.Count - 1 Step 1
                If (VsrDataRow.CustomRecordId.ToString.Trim = EquinexMasterSqlDataSet.RatingsData.Rows(DupRaceCount1).Item(EquinexMasterSqlDataSet.RatingsData.CustomRecordIdColumn).ToString.Trim) Then
                    If (DupAlertCount1 = 0) Then
                        MsgBox("766 - This race has already been calculated")
                        DupAlertCount1 = 1
                        conn.Close()
                        Exit Sub
                    Else
                        conn.Close()
                        Exit Sub
                    End If
                End If
            Next
            If (DupAlertCount1 = 1) Then
                ' Do Nothing
            Else
                Try
                    Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
                Catch
                End Try
            End If
        End If


    End Sub
 
Upvote 0 Downvote
DekaFlash said:
I have a calculation procedure which adds its results to a database.

As the database is setup to prevent the adding of duplicate records, on some occasions my app throws an exception at the line highlighted in red.

I am trying to find a easy way to stop duplicate records getting added to the database.
Click to expand...

Adding a row to a DataSet is not the same as uplaoding it to a database.. The row youre adding to the set is already present, which is why youre getting a constraint exception. If youre looking to prevent dupes, simply catch the exception and move on
 
Upvote 0 Downvote

Similar threads

amnon
  • Question
Resolved Registry Function
Replies
5
Views
576
amnon
amnon
H
  • Question
Fill cells with color
Replies
2
Views
702
jdelano
J
I
  • Locked
  • Question
Generar reporte Excel desde un datagridview
Replies
1
Views
821
JohnH
JohnH
chacham
  • Question
Adding a handler that uses a variable, uses the value of the variable at event time
Replies
3
Views
1K
JohnH
JohnH
R
  • Question
Question Issue for a Nested Timer Class
Replies
3
Views
1K
cjard
cjard

Share this page

Latest posts

Back
Top