Need help with a simple table lookup using either a dataset or datareader

[Andrews222]

Hello all,

New user here trying to figure out how to perform a (seemingly) simple task using VB.Net which I've never used before.
I'm adding a custom button and two textboxes to a form within an ERP system (Epicor) that we're installing - it's based on SQL Server.

Example: I have a table consisting of approx. 500 rows. Key field (key1) contains Zip codes and is unique, key2 also stores zip codes and another field "State" contains the state.

................Key1........Key2.......State
Row 324: "02200", "02499", "State1" // This row valid for zip codes from 02200 to 02499
Row 325: "02500", "02999", "State2" // This row valid for zip codes 02500 to 02999

The user types a zip code (let's say "02213") into TextBox01 and presses the "Lookup" button.
In MS Access w/VBA I can the result by doing this: "Select State, from UD07 where Key1 <= mZip AND Key2 >= mZip"

Here's what I've cobbled together from various internet posts so far.

The adapter (adZip) is already provided so I can connect to the appropriate database table (UD07). The table has 5 key fields - Key1 holds the lower zip range and Key2 holds the upper. Field "State" holds the state.
The variable "mZip" contains a valid zip code, but one that falls within row boundaries.

Dim dsZip As System.Data.DataSet = adZip.GetData(mZip,"","","","") ' DataSet
Dim dtTable As System.Data.DataTable = dsZip.Tables("UD07") ' DataTable
Dim ZipRow() as System.Data.Datarow
ZipRow = dsZip.Tables("UD07").Select("Key1 <= " & mZip & " AND Key2 >= " & mZip)

' Also tried - Dim ZipRow() as System.Data.Datarow = dsZip.Tables("UD07").select("Key1 = 02213")

mState = ZipRow(0)("State").ToString

So... Errors abound. I really just need to know how these objects work together, and how to assemble for this one specific task. I intend to learn the language, but can't today. Any thoughts? Just a pointer or two will be immensely helpful.

Thanks,

Andrew

 

[MarkSweetland]

I've found LINQ to be helpful in culling data from a DataTable. It's not intuitive, but once you get the hang of it becomes easy to extract subsets from a datatable.

        ' define the datatable for the example
        Dim dtTable As New DataTable
        dtTable.Columns.Add("Key1", GetType(String))
        dtTable.Columns.Add("Key2", GetType(String))
        dtTable.Columns.Add("State", GetType(String))

        ' fill the DataTable example
        Dim newRow As DataRow = dtTable.NewRow()
        newRow("Key1") = "01800"
        newRow("Key2") = "02199"
        newRow("State") = "State1"
        dtTable.Rows.Add(newRow)

        newRow = dtTable.NewRow()
        newRow("Key1") = "02200"
        newRow("Key2") = "02499"
        newRow("State") = "State2"
        dtTable.Rows.Add(newRow)

        newRow = dtTable.NewRow()
        newRow("Key1") = "02500"
        newRow("Key2") = "02999"
        newRow("State") = "State3"
        dtTable.Rows.Add(newRow)

        newRow = dtTable.NewRow()
        newRow("Key1") = "03000"
        newRow("Key2") = "03499"
        newRow("State") = "State4"
        dtTable.Rows.Add(newRow)
     
       ' zip looking for
        Dim SearchForZip As String = "02213"

        ' Use LINQ to find the zip
        Dim state = From z In dtTable
                    Select ZipState = z.Field(Of String)("State"), _
                    Key1 = z.Field(Of String)("Key1"), _
                    Key2 = z.Field(Of String)("Key2")
                    Where SearchForZip >= Key1 And SearchForZip <= Key2

        'result
        Dim TheState As String = String.Empty

        If state.Count > 0 Then
            ' Get the first, should only be one
            TheState = state(0).ZipState
        Else
            TheState = "Unknown"
        End If

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[jebenson]

I think the easiest way to do this is using a DataView and applying a RowFilter:

Dim dvTable As DataView

dvTable = dtTable.DefaultView

dvTable.RowFilter = "Key1 <= " & mZip & " AND Key2 >= " & mZip

mState = dvTable(0).Items("State").ToString

'don't forget to clear the RowFilter
dvTable.RowFilter = ""




Note that a DataView is a collection of DataRowViews, not a collection of DataRows like a DataTable. To loop through a DataView:

For Each drv As DataRowView in dvTable
'do stuff
Next



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Andrews222]

Wow, I'm so pleased to find these responses. Thank you Mark and JeBenson!
I'm not sure what Linq is or whether its available to me. The interface I am using is NOT visual Studio, rather, it is a fairly decent code-editing workspace provided by the application. It supports VB.NET and C#.NET. It does turn commands blue and comments green, but that's about it for debugging. I insert lots of msgbox() comments in order to track the executing code.

All that said, the point of entry to the application is where a form is visible (I can add controls) and a dataAdapter to the table is available and waiting for whatever I want to do with it. I don't think I can directly access that table, except through the adapter.

In my example, I created a dataSet, then a dataTable from that, and then a dataRow from that. I need to learn (in time) how they all interact and when each is necessary or best.

So, Jebenson, if I attempt your code, should I proceed as my example shows, all the way through creating the dataTable, then introduce the dataView? Can you please respond with some info on inspecting these various objects as they're created? Again, I need to display some messages while the code executes, so I can see what's happening. Perhaps msgbox("The DataView contains " & dvTable.rows & "rows! So far so good")

Thanks,

Andrew

 

[jebenson]

Here's some info to help you out:

A DataRow is a collection of DataColumn objects
A DataTable is a collection of DataRow objects
A DataSet is a collection of DataTable objects

A DataView is just that...a view of a DataDable, used for sorting, filtering, navigation, editing, etc. Any changes made in a DataView will affect the DataTable, and vice versa. A DataView is a collection of DataRowView objects

In answer to your question, yes, you do need to create the DataSet so you can have a DataTable to make the DataView. You don't *need* to make a separate DataTable object to do this, but I would do it anyway just to keep things clear.

As for inspecting the objects as they are created, you could do this:

Dim dsZip As System.Data.DataSet = adZip.GetData(mZip,"","","","") ' DataSet
MsgBox("dsZip has " & dsZip.Tables.Count & " tables.")

Dim dtTable As System.Data.DataTable = dsZip.Tables("UD07") ' DataTable
MsgBox("dtTable has " & dtTable.Rows.Count & " rows.")

Dim dvTable As DataView = dtTable.DefaultView
MsgBox("dvTable has " & dvTable.Count & " rows.") 'should be the same as dtTable .Rows.Count

'apply the RowFilter
dvTable.RowFilter = "Key1 <= " & mZip & " AND Key2 >= " & mZip
MsgBox("dvTable has " & dvTable.Count & " rows.") 'should be 1???

'extract the info needed
mState = dvTable(0).Items("State").ToString

'don't forget to clear the RowFilter
dvTable.RowFilter = ""
MsgBox("dvTable has " & dvTable.Count & " rows.") 'should be the same as dtTable .Rows.Count






I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!