DataView

[jimmy1981]

Hi,

Im trying to write a program that pulls in 3 tables from SQL Server and then checks those tables for specific values.

the code below is working when i try to find an operator in the operators table:

            Dim dv As DataView

            Dim iOp As String = Mid(LineText, 5, 3)


            dv = New DataView(ds.Tables("dbOperator"))
            dv.Sort = "OperatorCode"
            Dim NumberFound As Integer = dv.Find(iOp)


            If NumberFound = -1 Then
                ValidOp = False
            Else
                ValidOp = True
            End If

The problem I'm having is how to find a value that isn't exact, i.e. in SQL my statement to search was as below:

"SELECT 1 FROM dbo.servd WHERE SUBSTRING(route,1,4) = " & "'" & iRoute & "'"

the code below just searches for an exact match, how do I code the above SQL statement?

            Dim dv As DataView

            Dim iRoute As String = Mid(LineText, 9, 4)
            iRoute = iRoute.TrimStart("0c")


            dv = New DataView(ds.Tables("dbServD"))
            dv.Sort = "Route"
            Dim NumberFound As Integer = dv.Find(iRoute)


            If NumberFound = -1 Then
                ValidRoute = False
            Else
                ValidRoute = True
            End If

 

[Dunfiddlin]

Kinda difficult to say without an example of the data being searched and the intended result!

 

[jimmy1981]

sorry,

The SQL table has a column called route, in this are a number of routes with letter/s after the number (" 42 AA", " 42 AB", " 101 EL", etc).

I have the route as just the integer so 42, so i need to look for the first 4 characters of the column route as these are always the integers and not any letters.

 

[Dunfiddlin]

So the first 4 characters would be SPACE,SPACE,3,2, for example? So why convert to integers at all. The only invalid entry would be " " (4 spaces) so why not simply test for that?

Other mysteries include why you're processing linetext (a string including all the row data?) rather than operating on the column directly from a datatable.

 

[jimmy1981]

no the first 4 characters could be space,1,1,1 or space,1,1,space.

i'm using linetext as this is the file i'm comparing to the database, it goes through each line in the file and checks to see if the value exists in the databasetable which is stored in my dataset.

the dv.find just returns the row number i believe, so if it returns a number >-1 then the value exists.

what i'm asking is how do i use dv.find to find only the number part of the database, so ignore all characters after the number.

 

[jmcilhinney]

Don't use Find. It will only match whole values. Use DataTable.Select. It allows you to specify a filter expression that can match in various ways, much like Filter property of the DataView. If you have already filtered the DataView and you only want to find matches within that filtered group then you need to combine that filter expression with the new one. That could look like this:

Dim filterExpression As String

If String.IsNullOrEmpty(myDataView.Filter) Then
    filterExpression = "blahblahblah"
Else
    filterExpression = "(" & myDataView.Filter & ") AND blahblahblah"
End If

 

[jimmy1981]

Hi Jmc,
Thanks, i'm not sure how i implement this in my code, once i have read a line of my file i jump in to my CheckRoute sub a follows:

    Private Sub CheckRoute()

        Try
            Dim iRoute As String = Mid(LineText, 9, 4)
            iRoute = iRoute.TrimStart("0c")
            dvserv.Sort = "Route"
            Dim NumberFound As Integer = dvserv.Find(iRoute)


            If NumberFound = -1 Then
                ValidRoute = False
            Else
                ValidRoute = True
            End If


        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


    End Sub

how do i implement the DataTable.Select for my query of

"SELECT 1 FROM dbo.servd WHERE SUBSTRING(route,1,4) = " & "'" & iRoute & "'"

thanks for the help

 

[jmcilhinney]

Have you read the documentation for the DataTable.Select method? If you don;t know how to use it, that would be the first place to look. As with all Help topics that relate to members that support SQL-like syntax, it will direct you to the topic for the DataColumn.Expression property, which explains everything that is supported.

As you're using .NET 3.5, you might also consider using LINQ. You could call the Where extension method on the DataView or call AsEnumerable on the DataTable and then Where on the result. If the DataView is already filtered then using the DataView would probably be easier.

 

[Dunfiddlin]

Where does the file come from? Could you let us see a couple of sample lines?

And all you're checking is that each value in the line appears somewhere in the datatable, so if you had a line say ...

1230 FC24 78 W

It would be considered valid in the datatable

1230 AC24 74 Z
1234 CC24 78 Y
1256 EC24 82 X
1400 FC24 86 W

... even though there is no actual row matching the line?

 

[jimmy1981]

OK,
So a sample from the file is:

 1 !126!0044!XL!0044!1015!1018!06/27 10:21:37! 39 !"X"

I use streamreader to pull a line, then i get the route number by using MID which gives me 0044, i then trim all leading 0's to leave me 44.

I then need to check this value is in the database in a column called routes. The problem is, is that this column of routes also has letters which i don't need, I just need to check that there is a number 44 in this column.

so for the 44 in the database there are all these routes:

 44 FF
 44 ED
 44 EA
 44 AA

And so on, so long as there is a 44, i want it to return True i don't care about the letters, if there isn't a 44 then return False.

 

[Dunfiddlin]

Then I don't understand the source of the problem in identifying the route number. If the number is always 4 digits from char 8 where do these problem spaces come from?

the first 4 characters could be space,1,1,1 or space,1,1,space.

If the first number in the line increases beyond 99 then that would move everything else over 1 but in that case your Operator code routine will be off too.

 

[jimmy1981]

Thanks Jmc,

I read up and it's easy when you know how eh, got it working using this:

            Dim table As DataTable = ds.Tables("dbServD")
            Dim foundRows() As DataRow
            Dim iRoute As String = Mid(LineText, 9, 4)
            iRoute = iRoute.TrimStart("0c")


            foundRows = table.Select("SUBSTRING(route,1,4) = " & "'" & iRoute & "'")


            If foundRows.GetUpperBound(0) > 0 Then
                ValidRoute = True
            Else
                ValidRoute = False
            End If

Thanks again.

 

[jmcilhinney]

Not that it's wrong specifically but I find it curious how so many people seem to want to concatenate String literals. This:

foundRows = table.Select("SUBSTRING(route,1,4) = " & "'" & iRoute & "'")

is the same as this:

foundRows = table.Select("SUBSTRING(route,1,4) = '" & iRoute & "'")

so why use the former? Also, in situations like that, I actually find that String.Format makes the code easier to read:

foundRows = table.Select(String.Format("SUBSTRING(route,1,4) = '{0}'", iRoute))

 

[Dunfiddlin]

It seems a little odd that you should decry a few additional keystrokes in the first example, which I actually find more logical as it identifies the two sides of the 'equation' more clearly for the swift reader, while preferring to add a whole new function for what is not a complicated expression. String.Format is certainly a useful tool when there's a multitude of insertions but for this it just seems like overkill.

 

[jmcilhinney]

It seems a little odd that you should decry a few additional keystrokes in the first example, which I actually find more logical as it identifies the two sides of the 'equation' more clearly for the swift reader, while preferring to add a whole new function for what is not a complicated expression. String.Format is certainly a useful tool when there's a multitude of insertions but for this it just seems like overkill.

It's not a matter of keystrokes but readability. The first code snippet hardly delimits the two sides of the comparison when there are three concatenation operators present. Also, having just a single quote between two sets of double quotes is not easier to read and makes errors far more likely. As for using String.Format, I think it's better specifically because it keeps the entire expression within a single string literal. Given that the actual values are masked by variables anyway, String.Format makes the whole filter expression easier to read, and that is the point.