Results 1 to 8 of 8

Thread: problem with datatable constraint

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    3

    Unhappy problem with datatable constraint

    Code:
    Dim DT As New System.Data.DataTable
    DT.CaseSensitive = True
    DT.Columns.Add("Text1", GetType(System.String))
    DT.Columns.Add("Text2", GetType(System.String))
    DT.Constraints.Add("PK", DT.Columns(0), True)
    DT.Rows.Add("Text1","Text2")
    DT.Rows.Add(" Text1"," Text2")
    DT.Rows.Add("Text1 ","Text2 ")
    Why primary key error?
    How to work with all 3 rows?
    (I need a constraint, I allowed adding rows, I use datagridview + dataview filter)
    Thank you!

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: problem with datatable constraint

    You should look at the following where having a constraint will raise a runtime exception if you attempt to break the constraint so you would use Find method to first determine if the value exists first

    Code:
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim DT As New System.Data.DataTable
            DT.CaseSensitive = True
            DT.Columns.Add("Text1", GetType(System.String))
            DT.Columns.Add("Text2", GetType(System.String))
            DT.Constraints.Add("PK", DT.Columns(0), True)
    
            Try
                DT.Rows.Add("Text1", "Text2")
                If DT.Rows.Find("Text1") IsNot Nothing Then
                    DT.Rows.Add(" Text1", " Text2")
                Else
                    MessageBox.Show("Value already exist")
                End If
    
                ' raises an exception
                DT.Rows.Add("Text1 ", "Text2 ")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: problem with datatable constraint

    It's working as it should. You told it that the first column is a primary key and that it should be unique (like all good PKeys)... so the first one can be added just fine. The second one is different, so it gets added as well, the third one has the same value for the first col as the first row, it is NOT unique, so it throws an exception - as it should. So before you add a new row, you should check to make sure the new PKey doesn't first exist.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: problem with datatable constraint

    Your thread is titled "problem with datatable constraint" and tg explained that you're not really having a problem, SQL is enforcing the constraint you defined. But I am curious that you asked "How to work with all 3 rows?". If you want rows 1 and 3 to be identical, then you would simply not create the constraint. Do you have an ultimate goal?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    3

    Resolved Re: problem with datatable constraint

    I solved it self:
    Code:
    Option Strict On
    Option Explicit On
    Option Infer Off
    Option Compare Text
    
    Public Class Form1
        Dim DT As New System.Data.DataTable
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            DT.CaseSensitive = True
            DT.Columns.Add("Text1", GetType(System.String))
            DT.Columns.Add("Text2", GetType(System.String))
            DT.Columns.Add("PKText", GetType(System.String))
            DT.Columns.Add("PKSpacesLeft", GetType(System.Int32))
            DT.Columns.Add("PKSpacesRight", GetType(System.Int32))
            DT.Constraints.Add("PK", {DT.Columns(2), DT.Columns(3), DT.Columns(4)}, True)
    
            Dim DGV As New DataGridView
            DGV.Dock = DockStyle.Fill
            DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            Me.Controls.Add(DGV)
            DGV.DataSource = DT
            DGV.Columns(2).Visible = False
            DGV.Columns(3).Visible = False
            DGV.Columns(4).Visible = False
    
            AddRow(DT, "Text1", "Text2") ' OK
            AddRow(DT, " Text1", " Text2") ' OK
            AddRow(DT, "Text1 ", "Text2 ") ' OK
            AddRow(DT, "Text1 ", "Text2 ") ' ErrMsg - duplicates!
        End Sub
        Private Sub AddRow(Table As DataTable, Text1 As String, Text2 As String)
            If Not Text1 = String.Empty Then
                Dim TT As String = Trim(Text1) ' trimmed text
                Dim SL As Integer = Text1.IndexOf(TT) ' spaces left
                Dim SR As Integer = Text1.Length - SL - TT.Length ' spaces right
                Try
                    DT.Rows.Add(Text1, Text2, TT, SL, SR)
                Catch ex As System.Data.ConstraintException
                    If DirectCast(ex.TargetSite, System.Reflection.MethodInfo).Name = "CheckConstraint" Then
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    End If
                End Try
            Else
                MsgBox("Text1 is empty!", MsgBoxStyle.Critical) ' Write log, dont show Msg
            End If
        End Sub
    End Class

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: problem with datatable constraint

    I don't understand why you would use a Try-Catch when you know what the constraint is and don't handle this via assertion. To me that is the lazy way out of this.

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    3

    Re: problem with datatable constraint

    @ kevininstructor:
    Your code don't Support
    Code:
    Dim T1 as String = "Text"
    Dim T2 as String = "Text "
    Dim T3 as String = "Text  "
    Dim T4 as String = "Text   "
    Dim T5 as String = "Text    "
    Dim T6 as String = "Text     "
    NO duplicates here. This Forum is removing spaces too
    Your code make it allways to: "Text".
    Last edited by _XPI_; Oct 20th, 2015 at 04:10 PM.

  8. #8
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: problem with datatable constraint

    Quote Originally Posted by _XPI_ View Post
    @ kevininstructor:
    Your code don't Support
    Code:
    Dim T1 as String = "Text"
    Dim T2 as String = "Text "
    Dim T3 as String = "Text  "
    Dim T4 as String = "Text   "
    Dim T5 as String = "Text    "
    Dim T6 as String = "Text     "
    NO duplicates here. This Forum is removing spaces too
    Your code make it allways to: "Text".
    But of course the code presented does not handle this as it was never mentioned. If you had mentioned these possibilities I would had told you that spaces in the start and/or end are ignored.

    When this was reported to Microsoft the response was
    After carefully analizing it, we concluded that if we fix this problem, we may potentially break exisiting applications that relies on this issue. Because of that, we are closing this issue as Won't Fix.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width