[hingsmaf003]

i have a multi dimensional array that has 4 columns date, scores, slope, and course rating. i need to sort the array by date but i get a convert to string error in this line of code: If StrComp(arToSort(sortBy, d), smallestValue) < 0 Then

my code is below for the whole forum

Public Class post
Public num As Integer
Public odataset As New DataSet
Public oConn As OleDb.OleDbConnection
Public oComm As OleDb.OleDbDataAdapter
Public sConn As String
Public sComm As String
Public port As Integer = 3306
Public options As Integer = 3
Public scoresnum As Integer = 1
Public array(19, 3) As String
Public scores As Integer

Public Sub post_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
sComm = "SELECT * FROM users WHERE uname='" & login.txtuname.Text & "'"
'Build the connection string
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\users.mdb;Persist Security Info=False"

'Create the connection and command objects
oConn = New OleDb.OleDbConnection(sConn)
oComm = New OleDb.OleDbDataAdapter(sComm, oConn)
oComm.Fill(odataset)
array(0, 0) = odataset.Tables(0).Rows(0).Item("scr0")
array(0, 1) = odataset.Tables(0).Rows(0).Item("slp0")
array(0, 2) = odataset.Tables(0).Rows(0).Item("crs0")
array(1, 0) = odataset.Tables(0).Rows(0).Item("scr1")
array(1, 1) = odataset.Tables(0).Rows(0).Item("slp1")
array(1, 2) = odataset.Tables(0).Rows(0).Item("crs1")
array(2, 0) = odataset.Tables(0).Rows(0).Item("scr2")
array(2, 1) = odataset.Tables(0).Rows(0).Item("slp2")
array(2, 2) = odataset.Tables(0).Rows(0).Item("crs2")
array(3, 0) = odataset.Tables(0).Rows(0).Item("scr3")
array(3, 1) = odataset.Tables(0).Rows(0).Item("slp3")
array(3, 2) = odataset.Tables(0).Rows(0).Item("crs3")
array(4, 0) = odataset.Tables(0).Rows(0).Item("scr4")
array(4, 1) = odataset.Tables(0).Rows(0).Item("slp4")
array(4, 2) = odataset.Tables(0).Rows(0).Item("crs4")
array(5, 0) = odataset.Tables(0).Rows(0).Item("scr5")
array(5, 1) = odataset.Tables(0).Rows(0).Item("slp5")
array(5, 2) = odataset.Tables(0).Rows(0).Item("crs5")
array(6, 0) = odataset.Tables(0).Rows(0).Item("scr6")
array(6, 1) = odataset.Tables(0).Rows(0).Item("slp6")
array(6, 2) = odataset.Tables(0).Rows(0).Item("crs6")
array(7, 0) = odataset.Tables(0).Rows(0).Item("scr7")
array(7, 1) = odataset.Tables(0).Rows(0).Item("slp7")
array(7, 2) = odataset.Tables(0).Rows(0).Item("crs7")
array(8, 0) = odataset.Tables(0).Rows(0).Item("scr8")
array(8, 1) = odataset.Tables(0).Rows(0).Item("slp8")
array(8, 2) = odataset.Tables(0).Rows(0).Item("crs8")
array(9, 0) = odataset.Tables(0).Rows(0).Item("scr9")
array(9, 1) = odataset.Tables(0).Rows(0).Item("slp9")
array(9, 2) = odataset.Tables(0).Rows(0).Item("crs9")
array(10, 0) = odataset.Tables(0).Rows(0).Item("scr10")
array(10, 1) = odataset.Tables(0).Rows(0).Item("slp10")
array(10, 2) = odataset.Tables(0).Rows(0).Item("crs10")
array(11, 0) = odataset.Tables(0).Rows(0).Item("scr11")
array(11, 1) = odataset.Tables(0).Rows(0).Item("slp11")
array(11, 2) = odataset.Tables(0).Rows(0).Item("crs11")
array(12, 0) = odataset.Tables(0).Rows(0).Item("scr12")
array(12, 1) = odataset.Tables(0).Rows(0).Item("slp12")
array(12, 2) = odataset.Tables(0).Rows(0).Item("crs12")
array(13, 0) = odataset.Tables(0).Rows(0).Item("scr13")
array(13, 1) = odataset.Tables(0).Rows(0).Item("slp13")
array(13, 2) = odataset.Tables(0).Rows(0).Item("crs13")
array(14, 0) = odataset.Tables(0).Rows(0).Item("scr14")
array(14, 1) = odataset.Tables(0).Rows(0).Item("slp14")
array(14, 2) = odataset.Tables(0).Rows(0).Item("crs14")
array(15, 0) = odataset.Tables(0).Rows(0).Item("scr15")
array(15, 1) = odataset.Tables(0).Rows(0).Item("slp15")
array(15, 2) = odataset.Tables(0).Rows(0).Item("crs15")
array(16, 0) = odataset.Tables(0).Rows(0).Item("scr16")
array(16, 1) = odataset.Tables(0).Rows(0).Item("slp16")
array(16, 2) = odataset.Tables(0).Rows(0).Item("crs16")
array(17, 0) = odataset.Tables(0).Rows(0).Item("scr17")
array(17, 1) = odataset.Tables(0).Rows(0).Item("slp17")
array(17, 2) = odataset.Tables(0).Rows(0).Item("crs17")
array(18, 0) = odataset.Tables(0).Rows(0).Item("scr18")
array(18, 1) = odataset.Tables(0).Rows(0).Item("slp18")
array(18, 2) = odataset.Tables(0).Rows(0).Item("crs18")
array(19, 0) = odataset.Tables(0).Rows(0).Item("scr19")
array(19, 1) = odataset.Tables(0).Rows(0).Item("slp19")
array(19, 2) = odataset.Tables(0).Rows(0).Item("crs19")
array(0, 3) = odataset.Tables(0).Rows(0).Item("date0")
array(1, 3) = odataset.Tables(0).Rows(0).Item("date1")
array(2, 3) = odataset.Tables(0).Rows(0).Item("date2")
array(3, 3) = odataset.Tables(0).Rows(0).Item("date3")
array(4, 3) = odataset.Tables(0).Rows(0).Item("date4")
array(5, 3) = odataset.Tables(0).Rows(0).Item("date5")
array(6, 3) = odataset.Tables(0).Rows(0).Item("date6")
array(7, 3) = odataset.Tables(0).Rows(0).Item("date7")
array(8, 3) = odataset.Tables(0).Rows(0).Item("date8")
array(9, 3) = odataset.Tables(0).Rows(0).Item("date9")
array(10, 3) = odataset.Tables(0).Rows(0).Item("date10")
array(11, 3) = odataset.Tables(0).Rows(0).Item("date11")
array(12, 3) = odataset.Tables(0).Rows(0).Item("date12")
array(13, 3) = odataset.Tables(0).Rows(0).Item("date13")
array(14, 3) = odataset.Tables(0).Rows(0).Item("date14")
array(15, 3) = odataset.Tables(0).Rows(0).Item("date15")
array(16, 3) = odataset.Tables(0).Rows(0).Item("date16")
array(17, 3) = odataset.Tables(0).Rows(0).Item("date17")
array(18, 3) = odataset.Tables(0).Rows(0).Item("date18")
array(19, 3) = odataset.Tables(0).Rows(0).Item("date19")

array = arraySort(array, 1, True)

txtScr.Text = array(0, 0)
txtSlp.Text = array(0, 1)
txtCrs.Text = array(0, 2)
DateTimePicker1.Value = array(0, 3)
End Sub

Private Sub cmdPost_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
array = arraySort(array, 1, True)
End Sub

Private Sub cmdBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBack.Click
welcome.Show()
Hide()
End Sub

'==================================================
Function arraySort(ByVal arToSort, ByVal sortBy, ByVal compareDates)
'==================================================

Dim c, d, e, smallestValue, smallestIndex, tempValue

For c = 0 To uBound(arToSort, 2) - 1

smallestValue = arToSort
smallestIndex = c
For d = c + 1 To UBound(arToSort, 2)
If Not compareDates Then
If StrComp(arToSort(sortBy, d), smallestValue) < 0 Then
smallestValue = arToSort(sortBy, d)
smallestIndex = d
End If
Else
If Not IsDate(smallestValue) Then
arraySort = arraySort(arToSort, sortBy, False)
Exit Function
Else

If DateDiff("d", arToSort(sortBy, d), smallestValue) > 0 Then '=== no carriage return

smallestValue = arToSort(sortBy, d)
smallestIndex = d

End If
End If
End If
Next

If smallestIndex <> c Then
For e = 0 To UBound(arToSort, 1)
tempValue = arToSort(e, smallestIndex)
arToSort(e, smallestIndex) = arToSort(e, c)
arToSort(e, c) = tempValue
Next
End If

Next
arraySort = arToSort
End Function

End Class

[hingsmaf003]

If there was another way to sort the array by date please let me know but this was the only way i knew how to do it.

[AtmaWeapon]

Declare your data types in functions. My best guess at what your signature should look like is this:

Function arraySort(ByVal arToSort(,) As String, ByVal sortBy As Integer, ByVal compareDates As Boolean)

When you leave off the type, VB assumes you mean Object. Most of the time, VB can figure out what's going on and it just works. Sometimes, it gets confused and in this case it just can't figure out what arToSort is.

Do yourself a favor and turn Option Strict on so you can stop yourself from feeling this kind of pain. Always, always, always use datatypes.

[jo0ls]

I think you would be better off with 3 tables (if there is only one row in your current table, then just 2 tables would be required).

Let's say your data is automated readings taken from a train. It records the slope of the track, heading, data and scores - er it scores how nice the scenery is outside the cabin. So one table could store one Reading. Lets call the table the READING table. It would have columns for slope, heading, data and scores. It would need an ID too, lets call it ReadingID.

The next Table would store one rows worth of data (ie the row in your original table). This comprises 20 individual Readings, and another ID. Let's call the table ReadingSet, and the ID ReadingID. In each column you would record the ReadingID - this is a foreign key.

If the table you are using at the moment has many rows, then a final table is required to list the different ReadingSets.

Anyway, that's the general idea. At the moment you have too much crammed into one row. By breaking it up like that, you could SELECT the data using ORDER BY and not have to sort it at all.

Is all the data stored as String? You can use Types more suited to the data, for example the Date could be stored as a DateTime, the course as a Single.

If you can't alter the layout of the database then...

So each item that you work with has 4 properties - date, scores, slope, and course rating. It seems sensible to group all these together in a Class:

Public Class Thing

    Private myDate As String
    Public Property [Date]() As String
        Get
            Return myDate
        End Get
        Set(ByVal value As String)
            myDate = value
        End Set
    End Property

    Private myScores As String
    Public Property Scores() As String
        Get
            Return myScores
        End Get
        Set(ByVal value As String)
            myScores = value
        End Set
    End Property

    Private mySlope As String
    Public Property Slope() As String
        Get
            Return mySlope
        End Get
        Set(ByVal value As String)
            mySlope = value
        End Set
    End Property

    Private myCourseRating As String
    Public Property CourseRating() As String
        Get
            Return myCourseRating
        End Get
        Set(ByVal value As String)
            myCourseRating = value
        End Set
    End Property

End Class

That looks complicated but all it does is store the 4 variables, and provide ways to Get and Set them. This allows you to put validation code in the Get/Set blocks and ensure that the state is never invalid. The [Date] syntax is because Date is a keyword. If you type property and then press tab twice, it will fill in skeleton code, so it doesn't take long to type all that.

It's a bit ugly having all these values as String. If your database stores them as Integer, or Single, or Double or DateTime then you should modify the types in the class to match.

Anyway, now that you have a class you can create instances from the data in the dataset. There's no need for multidimensional arrays, you can use a flat array. I've used a List which is similar to an array:

        ' Instead of an array, use a list, as they are
        ' more useful - they have some extra methods, 
        ' and don't cost much over an array.
        ' 20 means it will start with a capacity of 20.
        ' If you stick another one in it will increase in 
        ' size to fit it in automatically. It is an optimization
        ' to specify the capacity, it is not required.
        Dim things As New List(Of Thing)(20)

        For i As Integer = 0 To 19
            ' create the column names for 1 item...
            Dim scores As String = String.Concat("scr" & i.ToString)
            Dim slope As String = String.Concat("clp" & i.ToString)
            Dim courseRating As String = String.Concat("crs" & i.ToString)
            Dim date1 As String = String.Concat("date" & i.ToString)
            ' Create a Thing object
            Dim thing As New Thing
            ' Set its state via properties:
            thing.Scores = oDataSet.Tables(0).Rows(0).Item(scores)
            thing.Slope = oDataSet.Tables(0).Rows(0).Item(slope)
            thing.CourseRating = oDataSet.Tables(0).Rows(0).Item(courseRating)
            thing.Date = oDataSet.Tables(0).Rows(0).Item(date1)
            ' Add it to the list.
            things.Add(thing)

        Next

So now there is a List(Of Thing). Each thing has the four properties you want. We just need to sort it. The framework uses the QuickSort algorithm to sort things. The algorithm needs to know how to compare two of the things that you are sorting. So we need a function to compare two things:

    Private Function CompareThingsByDate(ByVal thing1 As Thing, ByVal thing2 As Thing) As Integer
        ' Do your comparison here.
        ' You want to compare thing1.Date with thing2.Date
        ' If thing1.Date should come before thing2.Date in the list then return a negative number.
        ' If there is no difference between the two then return 0
        ' If thing2.Date should come before thing1.Date in the list then return a positive number.
    End Function

You'll have to do the implementation yourself, I don't know the Type you use for the Date. If it is String then it will be very complicated.

Finally then, what is the syntax to perform the sort. We need to call some function, it a method of our list called Sort. It needs to know where the function is for the comparison, so we use the AddressOf operator:

things.Sort(AddressOf CompareThingsByDate)