Getting results from an array

Friends,

Okay, what I am trying to do is collect information into an array, and then extract one of the results based on a selection from a combobox.

Here is the code I use to build the array:

Public Shared RaceList(100, 1)

 Public Sub GetRaceList(ByVal dsnHistory)

        Try

            Dim data As New DataSet
            Dim r As DataRow
           
           cmbGetRaceName.Items.Clear()

            Dim con As New SqlClient.SqlConnection(dsnHistory)

            'fill in the data for the races ...
            data = New DataSet

            Dim dr As SqlClient.SqlDataReader

            con = New SqlClient.SqlConnection(dsnHistory)

            con.Open()

            Dim c As New SqlClient.SqlCommand("Select Name=RTRIM(Name), EventID from Events where SeasonID = 10 and ((Name NOT like '% Test %') and (Name NOT LIKE '% ROP %'))", con)

            dr = c.ExecuteReader(CommandBehavior.SingleResult)

            Dim found As Boolean
            Dim cmb As ComboBox

            Dim i As Integer
            found = False
            Dim LastDecoder As String = ""

            'loop through the records
            While dr.Read()
               
                cmbGetRaceName.Items.Add(dr("Name"))
                RaceList(dr("Name"), 1) = dr("EventID")  <-- Add info to array here
               
            End While

            con = Nothing            

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub

Now, Here is where I want to extract a particular EventID from the array:

Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
        Select Case cmbGetRaceName.SelectedIndex
            Case 0
                RaceNumber = "Race1"
                'Assign the EventID here!!! -- By the way, cmbGetRaceName.Text is equal to the Name field found in the array
            Case 1
                RaceNumber = "Race2"
                'Assign the EventID here!!! -- By the way, cmbGetRaceName.Text is equal to the Name field found in the array
            Case 2
                RaceNumber = "Race3"
               'Assign the EventID here!!! -- By the way, cmbGetRaceName.Text is equal to the Name field found in the array
         End Select
    End Sub

Additional info:  Unfortuantely the EventID's are not sequential because we sometimes have Open Tests in between Races.

Thanks in advance!!!
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.

eventprostrategiesCommented:
Instead of adding a 'Name' to the combobox and EventID to the array ... there are a few other things you could do.

as a quick fix ... i would ...

Class RaceEvent
     Public ID
     Public Name
     
     Public Sub New(id as integer, name as string)
          me.ID = id
          me.Name = name
     End Sub

     Public Overrides Function ToString() As String
           return me.Name
     End Sub
End Class

While dr.Read()
       cmbGetRaceName.Items.Add(New RaceEvent( dr("Name"), EventID ) )
End While

Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
       
Dim idx as integer = cmbGetRaceName.SelectedIndex
Dim re as RaceEvent = CType(cmbGetRaceName.Items(idx), RaceEvent)

'// so ... you have the Name and EventID so you can do whatever you want with them

End Sub

You could also do something like (after making Name/ID Public Properties in the class, instead of fields) ...
cmb.DisplayMember = "Name"
cmb.ValueMember = "ID"

and cmbGetRaceName.Items(idx).Value would be the EventID

... not sure if that helps.  Just remember that you can throw whatever you want into a combobox -- you just need to set the DisplayMember to a public property you want to show as the text (or it'll use the ToString() by default which can be fine) ... and a ValueMember if you want to use that.  then you can always pull the object back out of the combobox and use it however you want.
indy500fanAuthor Commented:
Nope, I have to have the combobox so that my user can select the Race of their choice.  

Any other suggestions on getting the EventID from the array?

I know the logic I want to use, I guess what I'm asking for is the syntax required to extract the EventID from the Array.

Thanks
eventprostrategiesCommented:
dim id as integer = cint( RaceList(index, 1) ) ... ?

or ... it looks like Name is an integer as you're setting RaceList( name, 1 ) = EventID ... ?  so ... just pull it out the same way.

dim id as integer = RaceList( combobox1.selecteditem, 1 ) '// as you put the name in the combobox as well.

I'm not entirely sure what problem you're having getting the EventID out of the array, i guess ...

cmbGetRaceName.Items.Add(dr("Name"))
RaceList(dr("Name"), 1) = dr("EventID")  <-- Add info to array here

you have a value in the combobox that's equal to the index of RaceList where EventID resides in dimension 1 ... right?  so ... on the selected index changed of the combobox, you have the index and can therefore get "Name" and ... RaceList(name, 1) should = the EventID you want.

or maybe i've just been living in collections and arraylists too long ...
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

indy500fanAuthor Commented:
Crude, no Name will be a string.  I am really very new to programming and I don't know array's very well.

I'm having trouble with the syntax.  Maybe because you can't hold a string in an array, possbily?

This is the code I have:

 Select Case cmbGetRaceName.SelectedIndex
            Case 0
                RaceNumber = "Race1"
                EventID = (RaceList(Name, 1) = cmbGetRaceName.Text)
                MessageBox.Show(EventID)

Am I close?
indy500fanAuthor Commented:
Uh I think I see what you are getting at:

Instead of building the array on name, build it ont he selectedindex item, right?
eventprostrategiesCommented:
Well, if your RaceList is just an array of integers, then what you want is ...

Public Shared RaceList(100) as integer

[quote] EventID = (RaceList(Name, 1) = cmbGetRaceName.Text) [/quote]

RaceList(Name, 1) isn't valid.  Arrays take integer indices to get/set values.  so,
RaceList(i, 1) = something || something = RaceList(i, 1) -- where i is an integer

If you try RaceList("hi", 1) = something, you should get an invalidCastException (cast from string to integer not valid)

You can hold a string in an array just fine (dim str() as string would be an array of strings)), but you can't get/set values using a string to get the index.  

You might want to use a HashTable if you want to use string keys.  HashTable:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemcollectionshashtableclasstopic.asp

----
eventprostrategiesCommented:
Well, if your RaceList is just an array of integers, then what you want is ...

Public Shared RaceList(100) as integer

[quote] EventID = (RaceList(Name, 1) = cmbGetRaceName.Text) [/quote]

RaceList(Name, 1) isn't valid.  Arrays take integer indices to get/set values.  so,
RaceList(i, 1) = something || something = RaceList(i, 1) -- where i is an integer

If you try RaceList("hi", 1) = something, you should get an invalidCastException (cast from string to integer not valid)

You can hold a string in an array just fine (dim str() as string would be an array of strings)), but you can't get/set values using a string to get the index.  

You might want to use a HashTable if you want to use string keys.  HashTable:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemcollectionshashtableclasstopic.asp

----
eventprostrategiesCommented:
sorry about the double-post.  connection timed out.
indy500fanAuthor Commented:
Okay, I'm really lost now.

Can you help me with the syntax?

If we build the array, on the index created as the race names are added to the combobox, what would the code look like?

Then, if I am going to pull the eventid out of the array based on the selected index, what would that code look like?

I appologise if you already have it above, but I'm very new at this stuff.

Thanks!
eventprostrategiesCommented:
I would use objects ...

but if you want a RaceList array to hold the eventIDs for the combobox, here:

Public Shared RaceList(100) As Integer '// as String if the EventIDs are strings

'// instead of copying your datareader code, these are for testing:
Dim RaceNames() As String = {"a", "b", "c", "d", "e"} '// same as your dr("Name")
Dim RaceIDs() As Integer = {1, 2, 3, 4, 5} '// same as your dr("EventID")

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

ComboBox1.Items.Clear

Dim i As Integer
For i = 0 To 4
     ComboBox1.Items.Add(RaceNames(i))
     RaceList(i) = RaceIDs(i)
Next

'// preview the items in RaceList:
ListBox1.Items.Add("RaceList:")
For i = 0 To 100 - 1
      ListBox1.Items.Add(RaceList(i))
Next

End Sub

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim idx As Integer = ComboBox1.SelectedIndex
        MsgBox(RaceList(idx))
End Sub
indy500fanAuthor Commented:
I know you are trying to teach me here and I appreciate that, but I am really struggling.  I think I see what you are doing, but I don't know how this will be integrated in to the example from the beginning.

Can I really do the for loop inside my do while?  And, if I do, I don't know what the total number of races will be to that point.  Won't setting the for loop to a set number create problems, if there are more or less races that are specified by that number?

  While dr.Read()                

                cmbGetRaceName.Items.Add(dr("Name"))
                RaceList(i1) = dr("EventID")  <-- Add info to array here
               
  End While


indy500fanAuthor Commented:
Am I going down the right path?

Putting Data in the Array (Don't know if it's working):

Dim c As New SqlClient.SqlCommand("Select Name=RTRIM(Name), EventID from Events where SeasonID = 10 and ((Name NOT like '% Test %') and (Name NOT LIKE '% ROP %'))", con)

            dr = c.ExecuteReader(CommandBehavior.SingleResult)

             Dim i As Integer = 0
         
            'loop through the records
            While dr.Read()

                cmbGetRaceName.Items.Add(dr("Name"))
                RaceList(EventNameIndex, 1) = dr("EventID")
                EventNameIndex = EventNameIndex + 1

            End While  


Extracting ID from the Array (Currently doesn't work)

Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
        Select Case cmbGetRaceName.SelectedIndex
            Case 0
                RaceNumber = "Race1"
                EventID = (RaceList(EventNameIndex, 1) = cmbGetRaceName.SelectedIndex)
                MessageBox.Show(EventID)
            Case 1
                RaceNumber = "Race2"
                EventID = (RaceList(EventNameIndex, 1) = cmbGetRaceName.SelectedIndex)
                MessageBox.Show(EventID)
SanclerCommented:
I don't want to complicate things any further, but your SqlClient can provide you - ready made - with the sort of "array" you are looking for, in the form of a datatable.  Rather than getting the rows produced by your SQLCommandText one by one, and adding things item by item to your combobox and an extra array, you can use that query to fill a datatable.  And then you can bind your combobox to that datatable, set its display member to "Name" and its value member to "EventID".  Then when a user clicks on any name displayed in the combobox you can just read the EventID from that.  Here's how it would work.

    'declare the table outside the sub, so it is generally accessible
    Dim dt As DataTable = New DataTable("RaceList")

    Private Sub GetRaceList(ByVal dsnHistory)

        'we use a data Adapter rather than a data Reader
        Dim c As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        'the connection is the same
        Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(dsnHistory)
        'we put the sql query into a commnd object ....
        Dim command As SqlCommand = New SqlCommand("Select Name=RTRIM(Name), EventID from Events where SeasonID = 10 and ((Name NOT like '% Test %') and (Name NOT LIKE '% ROP %'))", con)
        '... and add it to the data Adapter
        c.SelectCommand = command
        'Then the data Adapter fill the table (it automatically deals with opening and closing the connection
        c.Fill(dt)

        'bind the combo to the table
        cmbGetRaceName.DataSource = dt
        'tell it which item to show
        cmbGetRaceName.DisplayMember = "Name"
        'and which item we really want to know about
        cmbGetRaceName.ValueMember = "EventID"

    End Sub

    Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
        Console.WriteLine(cmbGetRaceName.SelectedValue) '<<< this is what you want
    End Sub

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
indy500fanAuthor Commented:
Oh so close.  I got so excited that I had to go back to my office to try it.

The only problem I'm having with it is that when I add the line:

EventID = cmbGetRaceName.SelectedValue into the Sub below, I get all kinds of problems.


  Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
        Console.WriteLine(cmbGetRaceName.SelectedValue) '<<< this is what you want
        EventID = cmbGetRaceName.SelectedValue  '<--How else do I assign the selected value to EventID?
    End Sub

1st:  As soon as I click the down-arrow for the combo box, I get the error, "Cast from type "DataRowView" to type 'Integer' is not valid."

Then, it replaces the names of the events with the eventid.

Then it gives a more serious error:  "An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: --Same error as in 1st.

Also, is there a way to clear the items in the combobox before readding them?  I used to clear the items using:

cmbGetRaceName.Items.Clear(), but this seems to just create more problems.




SanclerCommented:
Sorry, I went to bed immediately after posting, so I've only just learned of the problems.  I should have taken my original code that step further.

    Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
        Console.WriteLine(cmbGetRaceName.SelectedValue) '<<< this is what you want
        'EventID = cmbGetRaceName.SelectedValue  '<--How else do I assign the selected value to EventID? REPLACE THIS ...
        EventID = DirectCast(cmbGetRaceName.SelectedItem, DataRowView).Item("EventID") 'WITH THIS ...
    End Sub

I've tried that - although with slightly different data than yours - and it seems to be working OK.

You shouldn't need "to clear the items in the combobox before readding them".  The point of binding the combobox to the datatable is that when the datatable changes, so does what is in the combobox.  It happens automatically.

Roger
SanclerCommented:
This is a bit neater.

    Private Sub cmbGetRaceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbGetRaceName.SelectedIndexChanged
        If TypeOf cmbGetRaceName.SelectedValue Is Integer Then
            Dim EntryID As Integer = cmbGetRaceName.SelectedValue '<<< if you've already declared EntryID as Integer you can just use ...
            'EntryID = cmbGetRaceName.SelectedValue
            MsgBox("EntryID = " & EntryID) 'just for demo purposes, to show it's working
        End If
    End Sub

The problems arose because the first time the SelectedIndex changed there was no SelectedValue.  This overcomes that by doing nothing if the SelectedValue is not an integer.

Roger
indy500fanAuthor Commented:
Roger,

That works much better.  The only thing left is that it still keeps adding the the list in the listbox.  Everytime I click the dropdown, it adds the events in question to the list.  In other words, if I click it once, I get all the events once.  If I select an event, and then re-click the dropdown list, there are there in duplicate.

Now, maybe this is the problem.  The first part of the code you sent wouldn't compile, so I modified it to be able to compile.  Maybe that's the problem:

Private Sub GetRaceList(ByVal dsnHistory)

        'we use a data Adapter rather than a data Reader
        Dim c As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        'the connection is the same
        Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(dsnHistory)
        'we put the sql query into a commnd object ....
        Dim command As SqlCommand = New SqlCommand("Select Name=RTRIM(Name), EventID from Events where SeasonID = 10 and ((Name NOT like '% Test %') and (Name NOT LIKE '% ROP %'))", con)
        '... and add it to the data Adapter
        c.SelectCommand = command
        'Then the data Adapter fill the table (it automatically deals with opening and closing the connection
        c.Fill(dt)

        'bind the combo to the table
        cmbGetRaceName.DataSource = dt
        'tell it which item to show
        cmbGetRaceName.DisplayMember = "Name"
        'and which item we really want to know about
        cmbGetRaceName.ValueMember = "EventID"

    End Sub


Was changed to:

 Private Sub GetRaceList(ByVal dsnHistory)

        'we use a data Adapter rather than a data Reader
        Dim c As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        'the connection is the same
        Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(dsnHistory)
        'we put the sql query into a commnd object ....
        Dim command As New SqlClient.SqlCommand("Select Name=RTRIM(Name), EventID from Events where SeasonID = 10 and ((Name NOT like '% Test %') and (Name NOT LIKE '% ROP %'))", con)
        '... and add it to the data Adapter
        c.SelectCommand = command
        'Then the data Adapter fill the table (it automatically deals with opening and closing the connection
        c.Fill(dt)

        'bind the combo to the table
        cmbGetRaceName.DataSource = dt
        'tell it which item to show
        cmbGetRaceName.DisplayMember = "Name"
        'and which item we really want to know about
        cmbGetRaceName.ValueMember = "EventID"

    End Sub

Could that be the problem?

My compiler wouldn't accept:

 Dim command As SqlCommand = New SqlCommand("Select Na...

It says that 'Type SqlCommand not defined'

I won't be back for a couple of days.  So no hurry.  I'll try this again on Monday.

Thanks for all your help so far!  It really is close!
SanclerCommented:
1)   No, the change to SqlClient.Command would not cause that problem.  I'd assumed (wrongly) that you had Imports System.Data.SqlClient at the start of your form, but how you sorted it was OK.

2)  What is happening is that GetRaceList() is being called for a second (and third, etc.) time when you click on the combo.  There's nothing in the code I've seen that would do that.  Have you got any other code in the combo.click, or combo.gotfocus, or combo.textchanged, or anything?  There are various ways we can work round the problem - see below - but I'd rather actually find its cause if we can.  One way of finding out might be to use Edit>Find to look for GetRaceList.

3)  If we can't find out what is calling GetRaceList() for the additional times there are either of two things to do:

3.1)  add this line

        dt.Rows.Clear()

as the first line in the GetRaceList() sub

OR

3.2)  add these lines at the start of the sub

        Static tablefilled As Boolean
        If tablefilled Then Exit Sub

and this line at the end of the sub

        tablefilled = True

Roger
 
indy500fanAuthor Commented:
Roger,

 The line dt.Rows.Clear() did the trick.  I don't know why it was adding to the list each time I clicked the down arrow; however, this works great.

Thanks for all your help!

Eric
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.