Randomizing and For Next Loop (Resolved)

I wrote some code that picks a random customer from a list of customers I import from an Excel Spreadsheet into a datagridview. Right now I am dealing with one list so it is a pretty straight forward process. However my boss wants me to adjust it so that a random employee is picked from each store (Store number is one of the fields in the datagridview).

Here is the meat of the code I have so far.

VB Code:

Dim intRandom As Integer = GetRandom(0, CInt(txtTopNumber.Text))
 
        dgvCustomer.CurrentCell = _
            dgvCustomer.Rows(intRandom).Cells(1)
 
        dgvCustomer.Rows(intRandom).Selected = True
 
        txtRandomSelectCust.Text = (CStr(dgvCustomer.Item(0, _
            dgvCustomer.CurrentRow.Index).Value))

The txtTopnumber.text is a field that holds that value of the row count so I could get my upper number: txtTopNumber.Text = CStr(intRowCount). Now I need to figure multiple top numbers and select a random customer from each group of stores. I think I need a for next loop to accomplish this but I am not sure how to get started. Any help or advice will be appreciated. Here is a snap shot of the datagrid that holds the store number. (V_LASTSTOR)

http://www.liberty1st.org/images/grid.gif

I am closer but I am still missing the important piece. Here is my code up to this point. Basicaly I have stopped trying to do the randomization after the records are pulled into the dgv and am now trying to accomplish it at the same time using just the dataset.

VB Code:

Dim myDataset As New DataSet()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strFileName & ";" & "Extended Properties=""Excel 8.0;"""
        Dim myData As New OleDbDataAdapter("SELECT FullName, V_ADDRESS, " & _
            "V_CITY, V_STATE, V_ZIP, [Last Visit], V_LASTSTOR From [Mailing List$] WHERE " & _
            "(V_STATE = 'MI') AND (Len(V_ZIP) >= 5) AND " & _
            "(V_ADDRESS <> '') AND (V_CITY <> '') AND " & _
            "(FullName <> '')", strConn)
 
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataset)
        dgvCustomer.DataSource = myDataset.Tables(0).DefaultView
 
        With myDataset.Tables(0)
            Dim Stores(.Rows.Count - 1) As String
            For i As Integer = 0 To .Rows.Count - 1
                ' For each ' Store in dataset ??? do something
 
                ' Send to the randomization sub here.
                ' and then right to a DB (XML?) and 
                ' goto the next random customer from
                ' the next store number
 
                'Next
            Next
        End With

1. Dose your database have a primary key?
2. Do you know how many stores are there?
3. Can you show the dataset columns? That I can see what may be the solution.

I think if you want to get a random employ from each store then you need to solve the problem in a store level first.

1. What I am thinking is to get all the employees of a store.
2. Get the rows count and pick a random row (employee).
3. Do it for each store.

There is no primary key because I am working with an Excel file as my original datasource. There are allways going to be 29 Stores but the number of customers per store will vary each time the report is run. I think you meant customer ID and not employee ID but yes it sounds like you have the right idea.

The Dataset columns are:

Column 0 = FullName
Column 1 = V_ADDRESS
Column 2 = V_CITY
Column 3 = V_STATE
Column 4 = V_ZIP
Column 5 = [Last Visit]
Column 6 = V_LASTSTOR

I put some code together see if it works.

VB Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myDataset As New DataSet()
        Dim stores As String() = {CStr(1101), CStr(1010),...}
        Dim rendomCustQueue As Queue
        Dim myrows As DataRow()
        Dim value As Integer
        ' Initialize the random-number generator.
        Randomize()
        For Each storeNum As String In stores
            myrows = myDataset.Tables(0).Select("V_LASTSTOR ='" & storeNum & "'")
            ' Generate random value between 0 and myrows size.
            value = CInt(Int((myrows.GetUpperBound(0) * Rnd()) + 0))
            rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)
        Next
    End Sub

I am getting this error: Explicit initialization is not permitted for arrays declared with explicit bounds.

on this line Dim Stores(5) As String = {1101, 1115, 1116, 1121, 1126, 1129}

I'd recommedn not using Randomize and Rnd. Use a Random object:

VB Code:

Dim employeePicker As New Random
 
For Each store In myStores
    employeeIndex = employeePicker.Next(employeeCount)
Next store

This glosses over a lot of the detail but basically for each store you generate a random number. If your employees have some ID number that forms an unbroken sequence then you can genereate a random value for that number directly. It's unlikely that that's the case though, so you'd probably generate a random employee index, then get the employee at that index in the table.

I made a mstake. instad of

VB Code:

Dim Stores(5) As String = {1101, 1115, 1116, 1121, 1126, 1129}

use this

VB Code:

Dim Stores As String() = {CStr(1101), CStr(1115), CStr(1116), CStr(1121), CStr(1126), CStr(1129)}

Or you can use Random object as jmcilhinney pointed.

VB Code:

Dim myDataset As New DataSet()
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Stores As String() = {CStr(1101), CStr(1115), CStr(1116), CStr(1121), CStr(1126), CStr(1129)}
        Dim rendomCustQueue As New Queue
        Dim employeePicker As New Random
        Dim myrows As DataRow()
        Dim value As Integer
 
        For Each storeNum As String In Stores
            myrows = myDataset.Tables(0).Select("V_LASTSTOR ='" & storeNum & "'")
            ' Generate random value between 0 and myrows size.
            value = employeePicker.Next(0, myrows.GetUpperBound(0))
            rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)
        Next
    End Sub

Thanks VBDT we are getting closer. I am getting this error: Index was outside the bounds of the array.

I tried to figure out where the bounds are coming from but couldn't.

on wich line?

The maximum value you specify should be one more than the maximum value you actually want returned. That measn that this line:

VB Code:

value = employeePicker.Next(0, myrows.GetUpperBound(0))

should actually be:

VB Code:

value = employeePicker.Next(0, myrows.GetUpperBound(0) + 1)

or, more sensibly:

VB Code:

value = employeePicker.Next(0, myrows.Length)

Given that zero is the default minimum that simplifies to:

VB Code:

value = employeePicker.Next(myrows.Length)

much as I posted previously.

and if you are using the second one with Random object then change the line

VB Code:

value = employeePicker.Next(0, myrows.GetUpperBound(0))

with this

VB Code:

value = employeePicker.Next(0, myrows.Length)

Ye a little too late but I figured that out. I have never used the random object so this is one more thing I added into my database (brain).

Here is the code I am using.

VB Code:

Dim Stores As String() = { _
            CStr(1101), _
            CStr(1115), _
            CStr(1116), _
            CStr(1121), _
            CStr(1126), _
            CStr(1129)}
 
        Dim rendomCustQueue As New Queue
        Dim CustomerPicker As New Random
        Dim myrows As DataRow()
        Dim value As Integer
 
        For Each storeNum As String In Stores
            myrows = myDataset.Tables(0).Select("LastStore ='" & storeNum & "'")
            ' Generate random value between 0 and myrows size.
            value = CustomerPicker.Next(0, myrows.Length)
            rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)
        Next

Index out of range on this line: rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)

I am going to change the "FullName" to the index number and see if that works. I 'll be back.

Update: No that didn't make a difference.

That would mean that "myrows" size is negative. Check this line to see if you are getting the rows from particular stores.

VB Code:

myrows = myDataset.Tables(0).Select("V_LASTSTOR ='" & storeNum & "'")

Quote:

---

Originally Posted by VBDT

and if you are using the second one with Random object then change the line

VB Code:

value = employeePicker.Next(0, myrows.GetUpperBound(0))

with this

VB Code:

value = employeePicker.Next(0, myrows.Length)

---

I forgat to ask, did you make the changes abov?

Yea I did make the changes. When I set the store like this

myrows = myDataset.Tables(0).Select("LastStore =1126")

There are no errors so you are right it has something to do with the store numbers line.

Now that I have had some more time to think about I think it it is because store numbers are numbers and not text. I should be able to adjust that in the code. Be right back.

Same error with this myrows = myDataset.Tables(0).Select("LastStore = " & storeNum)

well it has to be like this. you need to have the store number between two single quotes for string number! I would recomend to make the numbers as strings.

VB Code:

myrows = myDataset.Tables(0).Select("LastStore ='1126'")

myrows = myDataset.Tables(0).Select("LastStore = '1126'") worked. Although I don't know where the output is going.

the output is all the data rows from that store asigned to myrows.
now sorry i have to go but i will try to answer your questions when i get home.

No need to apologize. I appreciate any and all the help you can and have provided.

My question on the output is where can I verify that it is pulling a random customer from each store? I am not seeing any thing visual to confirm that it is doing so.

By the way I am going to post the entire code block minus the Try Catch block just in case I am doing something wrong. It may stand out better this way.

VB Code:

Private Sub GetRandomCustomers()
 
        Dim myDataset As New DataSet()
        Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strFileName & ";" & _
            "Extended Properties=""Excel 8.0;"""
        Dim myData As New OleDbDataAdapter("SELECT " & _
            "FullName, " & _
            "V_ADDRESS as Address, " & _
            "V_CITY as City, " & _
            "V_STATE as State, " & _
            "V_ZIP as Zip, " & _
            "[Last Visit] as LastVisit, " & _
            "V_LASTSTOR as LastStore " & _
            "From [Mailing List$] WHERE " & _
            "(V_STATE = 'MI') AND (Len(V_ZIP) >= 5) AND " & _
            "(V_ADDRESS <> '') AND (V_CITY <> '') AND " & _
            "(FullName <> '')", strConn)
 
        myData.TableMappings.Add("Table", "CustomerList")
        myData.Fill(myDataset)
        dgvCustomer.DataSource = myDataset.Tables(0).DefaultView
 
        Dim Stores As String() = { _
            CStr(1101), _
            CStr(1115), _
            CStr(1116), _
            CStr(1121), _
            CStr(1126), _
            CStr(1129)}
 
        Dim rendomCustQueue As New Queue
        Dim CustomerPicker As New Random
        Dim myrows As DataRow()
        Dim value As Integer
 
        For Each storeNum As String In Stores
 
            myrows = myDataset.Tables(0).Select("LastStore = '1126'") '& storeNum & "'")
            value = CustomerPicker.Next(0, myrows.Length)
            rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)
        Next
 
        myData.Dispose()
 
    End Sub

Let me explain to you what we are doing here.
Since we can not get a random numbers from mixed dataset with rows from different stores, we should separate the rows of each store and then try to find a random number for each store. For example:
Store 1126 has 30 rows in the dataset and the rest is from different stores. Now, we need to separate these 30 rows and find a random number from 0 to 30. The random number let say 19 is returned after calling the “CustomerPicker.Next” method is used to get the customer name from “myrows(19)” data array. Of course we need to use this process for all stores which we are doing it in the For Each loop. After the process is done you will have the random chosen customers in the Queue (rendomCustQueue). And this is about it. This is the code with coments

VB Code:

Private Sub GetRandomCustomers()
 
        Dim myDataset As New DataSet()
        Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strFileName & ";" & _
            "Extended Properties=""Excel 8.0;"""
        Dim myData As New OleDbDataAdapter("SELECT " & _
            "FullName, " & _
            "V_ADDRESS as Address, " & _
            "V_CITY as City, " & _
            "V_STATE as State, " & _
            "V_ZIP as Zip, " & _
            "[Last Visit] as LastVisit, " & _
            "V_LASTSTOR as LastStore " & _
            "From [Mailing List$] WHERE " & _
            "(V_STATE = 'MI') AND (Len(V_ZIP) >= 5) AND " & _
            "(V_ADDRESS <> '') AND (V_CITY <> '') AND " & _
            "(FullName <> '')", strConn)
 
        myData.TableMappings.Add("Table", "CustomerList")
        myData.Fill(myDataset)
        dgvCustomer.DataSource = myDataset.Tables(0).DefaultView
 
        'Assign the store numbers to a string array
        Dim Stores As String() = { _
            CStr(1101), _
            CStr(1115), _
            CStr(1116), _
            CStr(1121), _
            CStr(1126), _
            CStr(1129)}
 
        Dim rendomCustQueue As New Queue
        Dim CustomerPicker As New Random
        Dim myrows As DataRow()
        Dim value As Integer
 
        'Loop through each store
        For Each storeNum As String In Stores
            'Get all the rows from specified store by "storeNum"
            myrows = myDataset.Tables(0).Select("LastStore = '" & storeNum & "'")
            'Checks to see if the store has any customer records. 
            'If not go to next store.
            If myrows.Length > 0 Then
                'Use the rows count from the particular store to get a random number
                value = CustomerPicker.Next(0, myrows.Length)
                'Use the random number as an index in "myrows" 
                'to get the customer name and push it in to Queue.
                rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)
            End If
            'Go to the next store
        Next
        myData.Dispose()
 
        'The "rendomCustQueue has all the customers' names chosen randomly.
 
    End Sub

I hope it is clear now! :bigyello: ;)

VBDT the error is gone and I think I understand what the code is doing. The part that is / was confusing me is the Queue. I have never worked with that before so I wasn't aware of how to take the data stored there and present it to the user.

I Googled queue and found I can get to the data doing something like this (I hope I am doing it right).

VB Code:

For Each storeNum As String In Stores
            myrows = myDataset.Tables(0).Select("LastStore = '" & storeNum & "'")
            If myrows.Length > 0 Then
                value = CustomerPicker.Next(0, myrows.Length)
 
                rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString)
                rendomCustQueue.Enqueue(myrows(value).Item("Address").ToString)
                rendomCustQueue.Enqueue(myrows(value).Item("City").ToString)
                rendomCustQueue.Enqueue(myrows(value).Item("State").ToString)
                rendomCustQueue.Enqueue(myrows(value).Item("Zip").ToString)
                rendomCustQueue.Enqueue(myrows(value).Item("LastStore").ToString)
            End If
            Dim en As System.Collections.IEnumerator = rendomCustQueue.GetEnumerator()
            While en.MoveNext()
                txtRandomSelectCust.Text &= (en.Current.ToString & " ") & vbNewLine
            End While
        Next

EDIT: I removed this part of the post because I think it should have its own thread, which I started here.

Thanks again VBDT