-
Oct 18th, 2015, 05:11 AM
#1
Thread Starter
New Member
-
Oct 19th, 2015, 07:40 AM
#2
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
-
Oct 19th, 2015, 08:25 AM
#3
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
-
Oct 19th, 2015, 09:02 AM
#4
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.
-
Oct 20th, 2015, 07:36 AM
#5
Thread Starter
New Member
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
-
Oct 20th, 2015, 08:25 AM
#6
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.
-
Oct 20th, 2015, 04:07 PM
#7
Thread Starter
New Member
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.
-
Oct 21st, 2015, 09:16 AM
#8
Re: problem with datatable constraint
Originally Posted by _XPI_
@ 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|