none
Catch Dataset error unique constraint RRS feed

  • Question

  • Hi all

    I have a win form with textboxs and a DataSet and one Datatable (filled by sql database trhu TableAdapter)

    Each TextBox binds to datatable column. One of the columns has the Unique Constraint set to True by designer .

    By designer I get the Dataset Partial class to handle ColumnChange events like the following code

     Partial Public Class TabRegioniDataTable
            Private Sub TabRegioniDataTable_ColumnChanging(sender As Object, e As DataColumnChangeEventArgs) Handles Me.ColumnChanging
                '   If (e.Column.ColumnName = Me.RegioneDesColumn.ColumnName) Then
                MessageBox.Show("Column Changed " & sender.ToString & vbCrLf &
                              "proposed Value " & e.ProposedValue.ToString & vbCrLf &
                            " row " & e.Row.ToString & vbCrLf &
                            "Columns " & e.Column.ToString & vbCrLf &
                             "type " & e.GetType.ToString)
                '   End If
            End Sub
    

    Now if in TextBox I insert a value that already exists in DataTable, the cursor go back to the TextBox avoiding me to go to next textbox since there is the Unique constraints for that column bind to Textbox.

    I would like to catch the uniqe constraint error to ive user a friendly message.

    Ho to catch this dataset error (I don't need to catch the sql error because I Update database later if there are not errors in the form)

    Wednesday, January 16, 2019 8:13 AM

Answers

  • Hello,

    One way to handle this is to relax the constraint while working with user interaction then test for a constraint violation as shown below. And this will apply to working with typed data with slight code changes.

    The idea is to see if the proposed value exists currently and if so reject it.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With {.ColumnName = "FirstName", .DataType = GetType(String), .Unique = True})
            AddHandler dt.ColumnChanging, AddressOf ColumnChanging
    
            dt.Rows.Add("Ann")
            dt.Rows.Add("Karen")
    
            dt.AcceptChanges() ' to simulate data above came from a database table.
    
            dt.Columns("FirstName").Unique = False ' relax constraint as we handle this in an event below
            dt.Rows(0).SetField(Of String)("FirstName", "Karen")
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub ColumnChanging(sender As Object, args As DataColumnChangeEventArgs)
            If args.Row.Table.AsEnumerable().FirstOrDefault(Function(row) row.Field(Of String)("FirstName") = args.ProposedValue.ToString()) IsNot Nothing Then
                args.ProposedValue = args.Row.Field(Of String)("FirstName")
                MessageBox.Show("Can not duplicate first name")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Wednesday, January 16, 2019 5:03 PM
    Wednesday, January 16, 2019 11:37 AM
    Moderator

All replies

  • Hello,

    One way to handle this is to relax the constraint while working with user interaction then test for a constraint violation as shown below. And this will apply to working with typed data with slight code changes.

    The idea is to see if the proposed value exists currently and if so reject it.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With {.ColumnName = "FirstName", .DataType = GetType(String), .Unique = True})
            AddHandler dt.ColumnChanging, AddressOf ColumnChanging
    
            dt.Rows.Add("Ann")
            dt.Rows.Add("Karen")
    
            dt.AcceptChanges() ' to simulate data above came from a database table.
    
            dt.Columns("FirstName").Unique = False ' relax constraint as we handle this in an event below
            dt.Rows(0).SetField(Of String)("FirstName", "Karen")
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub ColumnChanging(sender As Object, args As DataColumnChangeEventArgs)
            If args.Row.Table.AsEnumerable().FirstOrDefault(Function(row) row.Field(Of String)("FirstName") = args.ProposedValue.ToString()) IsNot Nothing Then
                args.ProposedValue = args.Row.Field(Of String)("FirstName")
                MessageBox.Show("Can not duplicate first name")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Wednesday, January 16, 2019 5:03 PM
    Wednesday, January 16, 2019 11:37 AM
    Moderator
  • Hi Karen

    ok it works, but if I want to use the Unique Constraint = true set by designer  to the DataTable column ?

    Is there any other solution without relax the constraint ?

    Wednesday, January 16, 2019 4:19 PM
  • Hi Karen

    ok it works, but if I want to use the Unique Constraint = true set by designer  to the DataTable column ?

    Is there any other solution without relax the constraint ?

    No, otherwise I would had provided other recommendations. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 16, 2019 4:48 PM
    Moderator