Help with Array(s)

  • Thread starter Thread starter Rich Wallace
  • Start date Start date
R

Rich Wallace

Hi all,

I've scanned over previous posts but I'm still not feeling comfortable with
making a decision on using an Array or a Structure for what I need to do.
If someone can point me in the right direction, I would truly appreciate it.

I have a RecordSet from third-party solution which queries an IBM AS400 and
contains the following records:

1 - "A", "900000.00"
2 - "B", "950000.00"
3 - "C", "1000000.00"

When I retrieve the recordset, I need to have the data sorted by the second
value and loop through the records and perform other tasks based on the
current record. The problem is, the tool only sends me text or string
values back instead of an integer, so I can't do an order by (SQL statement
based query) on the second value as it will put the "1000000.00" value first
instead of "900000.00". I understand that I can perform some kind of Sort
against an array and I thought maybe I could send both values within each
record to an Array and use that to loop though but I've never used Arrays
before so I'm a bit lost.

Can somebody help??

TIA
-Rich
 
Rich,

Hmm..
Would love to find out about how you are connecting to AS400.
Is there not a good .NET solution to connect to AS400 or do you need 3rd
party?

Anyhow, for your question.

You say recordset...so does that mean you are using ADO? If so are you
using VB6? If that is true you should ask that newsgroup. If not and you
are using ADO.NET, then,

There are mainly datareaders and datasets.
How many rows of data are you expecting to have returned? A lot of just a
few?

1.) If you were to use that "recordset" to build a dataset, you could sort
it I think.

2.) Or if you were going to convert from your "recordset" to an array, why
not just convert the 2nd item to Integer using
either CInt(2nd fieldvalue) or ctype(2ndfieldvalue,Integer) to do that, then
you could easily sort numerically and correctly.

Arrays
=====
There are many flavors of arrays in dotnet and classes for arrays.
ArrayList
SortedArrayList
etc.

I think SortedArrayList, may or may not be the best choice for large amounts
of data so again, how many rows max do you expect to be putting in an array?

Yes, you can take your data and stuff it into something in .NET and sort it
but really could use more information first. Such as the questions that I
have asked.

Hope to be of Help,

Shane
 
Thank you Shane...

We're using Verastream Host adapter from WRQ to connect to our AS400. My
app in in VB.NET but their most current DLL was written in VB6..soon in .NET
so that will help in the future.

The output from a passed in query returns a 'recordset' that the WRQ folks
define so I current use the following code to loop through it:

----------------------------------------------------------------------------
------------------------
Dim oRs As Interop_AppConnLib.AppConnRecordSet
Dim oRec As Interop_AppConnLib.AppConnRecord
Dim sQry As String

sQry = "myQuery"

Try
oRs = oVHA.ExecuteSQLStatement(sQry)

For Each oRec In oRs
sValue1 = oRec.Item("Value1") 'String Value
sValue2 = oRec.Item("Value2") 'String Value that I'd like to
convert to Int and then sort both values against

'...do other work based on values returned

Next

oRs = Nothing
Catch ex As Exception
MsgBox(ex.Message
End Try
----------------------------------------------------------------------------
------------------------

The WRQ tool will return the data back to XML but the structure is so
complex, it would take an hour or two just to code for theri structe to use
it.

Please let me know if you need anything else.

-Rich
 
Hi Rich,

Did you look how to get a recordset in a datatable.
\\\
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
da.Fill(dt, myrecordset)
///

Maybe this helps?

Cor
 
Wow,
I would think XML would return in something with an XML schema and you could
use that to build a dataset from it easily. But will take your word for it.

no matter what you do, do this
For Each oRec In oRs
sValue1 = oRec.Item("Value1") 'String Value
iValue2 = cInt(oRec.Item("Value2")) 'make an int
Click to expand...

Then when you add it will be sorted as a number and do as you like.

You could
dim x(2,n) where n is number of records and then sort that

Array.Sort sorts one-dimentional arrays, but don't think it will 2dim arrays
like this one.

Or you could use a sortedlist--again don't know how many records you are
talking about.

You could use a Datatable if it is a lot of records.. just depends

sortedlist:
so if you want list sorted by number and the other is a key then you could
use the first as the key and number as the value and it would be sorted

Depends on what you want to do.

Help sample for sortedlist, taken from vb.net help
=====================================
Imports System
Imports System.Collections
Imports Microsoft.VisualBasic

Public Class SamplesSortedList

Public Shared Sub Main()

' Creates and initializes a new SortedList.
Dim mySL As New SortedList()
mySL.Add("First", "Hello")
mySL.Add("Second", "World")
mySL.Add("Third", "!")

' Displays the properties and values of the SortedList.
Console.WriteLine("mySL")
Console.WriteLine(" Count: {0}", mySL.Count)
Console.WriteLine(" Capacity: {0}", mySL.Capacity)
Console.WriteLine(" Keys and Values:")
PrintKeysAndValues(mySL)
End Sub

Public Shared Sub PrintKeysAndValues(myList As SortedList)
Console.WriteLine(ControlChars.Tab & "-KEY-" & ControlChars.Tab & _
"-VALUE-")
Dim i As Integer
For i = 0 To myList.Count - 1
Console.WriteLine(ControlChars.Tab & "{0}:" & ControlChars.Tab &
_
"{1}", myList.GetKey(i), myList.GetByIndex(i))
Next i
Console.WriteLine()
End Sub
End Class

' This code produces the following output.
'
' mySL
' Count: 3
' Capacity: 16
' Keys and Values:
' -KEY- -VALUE-
' First: Hello
' Second: World
' Third: !

Let me know if this helps any....
Don't know if your first value is a unique key or what... Assume you want
to sort list by second int value and display.
Still if you can import XML into dataset which should be easy if you have
the schema I'd think, then that would be the easiest way by far.

Shane
 
Gidday Rich,

There are a number of options but my first question is when you say
Recordset, in what form is that? A dataset or a datareader?

If it were a dataset, a really straightforward way to do this is to
just change the column type of the table the numeric data is in from
String to Int32. or else just suck the data out of the first table
into another table with a schema with Int32 as your 2nd column
datatype.

If its a data reader, then you could just add the values to a
datatable and
Cint(dr("My2ndCol")) as you go. There are heaps of options.

I wouldn't recommend an array, you'd need to go multi dimensional,
theres no strong typing for this scenario 'cause you have different
data types and the whole thing could get really messy, really quickly.
Unless you like tying your brain in knots.

hth

Richard
 
I wouldn't recommend an array either unless you were going to stuff it with
objects holding the data and somehow sort.

By far getting it into a datatable or somehow reading the XML into a dataset
seems the best option.

Shane
 
Sorry Mate,

I was a bit behind the 8-ball, with this slow as Google....but I'll
get it sorted. Does this help? Note you may have to set up your
references to get and import ADODB.

Richard


Private Function SortThatDirtyOldRecordSet_Uuughh() As DataTable

Dim ds As DataSet = New DataSet("Recordset")
Dim da As OleDbDataAdapter = New OleDbDataAdapter
Dim oRs As Interop_AppConnLib.AppConnRecordSet
Dim oRec As Interop_AppConnLib.AppConnRecord
Dim sQry As String


sQry = "myQuery"
Try
oRs = oVHA.ExecuteSQLStatement(sQry)

' Call data adapter's Fill method to fill data from ADO
' Recordset to the dataset
da.Fill(ds, oRs, "MyData")
Catch ex As Exception
MsgBox(ex.Message)
Finally
oRs = Nothing
End Try

If ds Is Nothing OrElse ds.Tables.Count = 0 OrElse
ds.Tables(0).Rows.Count = 0 Then Return Nothing
ds.Tables(0).Columns(1).DataType = GetType(Int32)
Dim dv As DataView
dv = ds.Tables(0).DefaultView

With dv
.Sort = "ColumnName"

' You've now got a sorted data view - Do as you please
End With

Return ' whatever you want
Return Nothing
End Function
 
Thank you all very much for your responses.

I did some playing around with the datatable and dataview objects. Could
use some tweaking to kind of match it up closer to Richard's suggestion but
I'm still new at these :)

Private Sub PremiumLookup()

Dim oRs As Interop_AppConnLib.AppConnRecordSet
Dim oRec As Interop_AppConnLib.AppConnRecord
Dim sPremium As String

'Prepare datatable structure
Dim dtPremium As DataTable
Dim drPremium As DataRow

sPremium = "myQuery"

Try
oRs = oVHA.ExecuteSQLStatement(sPremium)
dtPremium = MakePriceTable()

For Each oRec In oRs
drPremium = dtPremium.NewRow()
drPremium("Elev") = oRec.Item("Elevation")
drPremium("Price") = CInt(oRec.Item("BasePrice"))
dtPremium.Rows.Add(drPremium)
Next

Dim drFoundRows() As DataRow = dtPremium.Select("id >= 0",
"Price ASC")
Dim i As Integer

For i = 0 To drFoundRows.GetUpperBound(0)
sElev = drFoundRows(i)("Elev")
sBasePrice = drFoundRows(i)("Price")

'Process data

Next

oRs = Nothing
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

Private Function MakePriceTable() As DataTable

' Create a new DataTable titled 'Premiums'
Dim priceTable As DataTable = New DataTable("Premiums")

' Add three column objects to the table.
Dim idColumn As DataColumn = New DataColumn()
idColumn.DataType = System.Type.GetType("System.Int32")
idColumn.ColumnName = "id"
idColumn.AutoIncrement = True
priceTable.Columns.Add(idColumn)

Dim elevColumn As DataColumn = New DataColumn()
elevColumn.DataType = System.Type.GetType("System.String")
elevColumn.ColumnName = "Elev"
priceTable.Columns.Add(elevColumn)

Dim priceColumn As DataColumn = New DataColumn()
priceColumn.DataType = System.Type.GetType("System.Int32")
priceColumn.ColumnName = "Price"
priceTable.Columns.Add(priceColumn)

' Create an array for DataColumn objects.
Dim keys(0) As DataColumn
keys(0) = idColumn
priceTable.PrimaryKey = keys

' Return the new DataTable.
priceTable = priceTable

End Function
 
Back
Top