Need a method to insert 8 values into some sort of array, hashtable, etc....

Friends,

Background:

I am looking to fill out a spreadsheet (created through vb.net) with values I have stored in a table.

Here is the method for getting the values I want to put in the Excel Spreadsheet:

Public Shared IRLPrizeMoneyArray As New ArrayList

    Public Sub BuildIRLPrizeMoneyArray(ByVal dsnPoints)
        Try
            Dim data As New DataSet
            Dim r As DataRow
            Dim con As New SqlClient.SqlConnection(dsnPoints)
            data = New DataSet
            Dim dr As SqlClient.SqlDataReader
            con = New SqlClient.SqlConnection(dsnPoints)
            con.Open()
            Dim c As New SqlClient.SqlCommand("SELECT" & vbCrLf _
            & "FinishPosition," & vbCrLf _
            & "CarNumber, " & vbCrLf _
            & "Entrant, " & vbCrLf _
            & "IRLBasic, " & vbCrLf _
            & "IRLQual, " & vbCrLf _
            & "IRLTotal, " & vbCrLf _
            & "IRLPointsFund, " & vbCrLf _
            & "Total, " & vbCrLf _
            & "FROMPrizeMoney" & vbCrLf _
            & "ORDER BY FinishPosition", con)
            dr = c.ExecuteReader(CommandBehavior.SingleResult)
            Dim i As Integer = 0

            Dim Position As Integer
            Dim CarNumber As String
            Dim Entrant As String
            Dim IRLBasic As Decimal
            Dim IRLQual As Decimal
            Dim IRLTotal As Decimal
            Dim IRLPointsFund As Decimal
            Dim Total As Decimal

            'loop through the records
            If dr.HasRows Then
                While dr.Read()
                    ' fill array
                    Position = dr.Item("FinishPosition")
                    CarNumber = dr.Item("FinishPosition")
                    Entrant = dr.Item("FinishPosition")
                    IRLBasic = dr.Item("FinishPosition")
                    IRLQual = dr.Item("FinishPosition")
                    IRLTotal = dr.Item("FinishPosition")
                    IRLPointsFund = dr.Item("FinishPosition")
                    Total = dr.Item("FinishPosition")

                    IRLPrizeMoneyArray.Add(Position, CarNumber, Entrant, IRLBasic, IRLQual, IRLTotal, IRLPointsFund, Total)

                End While
            End If

            con.Close()
            con = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Sub: BuildIRLPrizeMoneyArray")
        End Try
    End Sub

Well, I'd like to say it works, but apparently, you can't put that many values into an array list.  Any suggestions?
indy500fanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
You could create a class (or struct if thats allowed in VB) to hold the values, then add that to the ArrayList.

Here's a very simplistic example:

  Public Class Form1

      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

          Dim arr As New ArrayList()
          Dim info As MyInfo

          For i As Integer = 1 To 10
              info = New MyInfo()
              info.Position = i
              info.CarNumber = i
              info.Entrant = i
              info.IRLBasic = i
              info.IRLQual = i
              info.IRLTotal = i
              info.IRLPointsFund = i
              info.Total = i

              arr.Add(info)
          Next

      End Sub

  End Class

  Public Structure MyInfo

      Public Position As Integer
      Public CarNumber As Integer
      Public Entrant As Integer
      Public IRLBasic As Integer
      Public IRLQual As Integer
      Public IRLTotal As Integer
      Public IRLPointsFund As Integer
      Public Total As Integer

  End Structure


Hope this helps.
0
indy500fanAuthor Commented:
????

No, I don't think this is what I am looking for..

With regards to an array, I am drawing a blank...

Can't I just define an array to have 8 elements by 30 records long???

Wouldn't it be something like

Dim myArray(8,100)???
0
Carl TawnSystems and Integration DeveloperCommented:

        Dim arr(8, 30) As Integer
        Dim Count As Integer = 0

        If dr.HasRows Then
            While dr.Read()
                ' fill array
                arr(0, Count) = dr.Item("FinishPosition")
                arr(1, Count) = dr.Item("FinishPosition")
                arr(2, Count) = dr.Item("FinishPosition")
                arr(3, Count) = dr.Item("FinishPosition")
                arr(4, Count) = dr.Item("FinishPosition")
                arr(5, Count) = dr.Item("FinishPosition")
                arr(6, Count) = dr.Item("FinishPosition")
                arr(7, Count) = dr.Item("FinishPosition")
                Count += 1
            End While
        End If


Kinda defeats the purpose of OOP, but its your app.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

indy500fanAuthor Commented:
Thanks carl.

That's what I was looking for.
0
indy500fanAuthor Commented:
Wait, what if every value isn't an integer?  As you can see there are decimals, strings and integers..

I screwed up with the dr.Read, it should be:

            Dim Position As Integer
            Dim CarNumber As String
            Dim Entrant As String
            Dim IRLBasic As Decimal
            Dim IRLQual As Decimal
            Dim IRLTotal As Decimal
            Dim IRLPointsFund As Decimal
            Dim Total As Decimal

            'loop through the records
            If dr.HasRows Then
                While dr.Read()
                    ' fill array
                    Position = dr.Item("FinishPosition")
                    CarNumber = dr.Item("CarNumber")
                    Entrant = dr.Item("Entrant")
                    IRLBasic = dr.Item("IRLBasic")
                    IRLQual = dr.Item("IRLQual")
                    IRLTotal = dr.Item("IRLTotal")
                    IRLPointsFund = dr.Item("IRLPointsFund")
                    Total = dr.Item("Total")
                End While
            End If
0
Carl TawnSystems and Integration DeveloperCommented:
The you'll have to declare the array as Object and do a cast on every value when you want to retrieve it from the array.
0
indy500fanAuthor Commented:
But everything else is the same?

So the syntax to add to the array would be:

       Dim arr(8, 30) As Object
        Dim Count As Integer = 0

        If dr.HasRows Then
            While dr.Read()
                ' fill array
                arr(0, Count) = dr.Item("FinishPosition")
                arr(1, Count) = dr.Item("CarNumber")
                arr(2, Count) = dr.Item("Entrant")
                arr(3, Count) = dr.Item("IRLBasic")
                arr(4, Count) = dr.Item("IRLQual")
                arr(5, Count) = dr.Item("IRLTotal")
                arr(6, Count) = dr.Item("IRLPointsFund")
                arr(7, Count) = dr.Item("Total")
                Count += 1
            End While
        End If

Now to retrieve the finish position for example, what would the syntax be with the cast?

0
Carl TawnSystems and Integration DeveloperCommented:
Dim val As Integer
val = Convert.Int32(arr(0, 0))
0
indy500fanAuthor Commented:
Carl,

Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.