Wednesday, January 20, 2010

.Net DataTable.Select bug

Be very careful how you write you FilterExpression using DataTable.Select(FilterExpression).

You add some columns to your DataTable without specifying what type they are it will be set to String as default. Later when you filter out data expecting integer datatype you get some corrupt stuff back. So don't forget ' ' around string parameters in your FilterExpression if you column are of String type.
The Net framework accept invalid filters and return crappy data back instead of saying that this FilterExpressing is crap, if your unlucky you will not find out until its too late.

Code example: (Same bad behavior occurs in C#)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim dt As New DataTable
dt.Columns.Add("ix1")
dt.Columns.Add("ix2")
dt.Columns.Add("ix3")

dt.Rows.Add(New Object() {1, 1, 8})
dt.Rows.Add(New Object() {3, 1, 8})
dt.Rows.Add(New Object() {3, 1, 8})
dt.Rows.Add(New Object() {3, 1, 8})
dt.Rows.Add(New Object() {3, 1, 8})
dt.Rows.Add(New Object() {3, 1, 8})
dt.Rows.Add(New Object() {5, 1, 8})
dt.Rows.Add(New Object() {5, 1, 8})
dt.Rows.Add(New Object() {13, 1, 8})
dt.Rows.Add(New Object() {13, 1, 8})

Dim x As Integer
Dim cnt As Integer
x = 1
cnt = dt.Select("ix1=" + x.ToString).Length
' THIS will return cnt=1
MsgBox("ix1=" + x.ToString + ", cnt= " + cnt.ToString)

x = 3
cnt = dt.Select("ix1=" + x.ToString).Length
' THIS will return cnt=5
MsgBox("ix1=" + x.ToString + ", cnt= " + cnt.ToString + ", this should be 5, wtf")

' Fix, column(0) is string instead of integer so filter will be for string
x = 3
cnt = dt.Select("ix1='" + x.ToString + "'").Length
' THIS will return cnt=5
MsgBox("ix1='" + x.ToString + "', cnt= " + cnt.ToString)

x = 13
cnt = dt.Select("ix1=" + x.ToString).Length
' THIS will return cnt=0 and not 2
MsgBox("ix1=" + x.ToString + ", cnt= " + cnt.ToString + ", this should be 2, wtf")

' Fix, column(0) is string instead of integer so filter will be for string
x = 13
cnt = dt.Select("ix1='" + x.ToString + "'").Length
' THIS will return cnt=2
MsgBox("ix1='" + x.ToString + "', cnt= " + cnt.ToString)

End Sub

No comments:

MySQL to MariaDB

 Here is a good description how to migrate to MariaDb MySQL to MariaDb Example of how to dump data from MySQL port=3307: mysqldump --user=ro...