Create a dataset from a Queue Collection (Resolved)

[FastEddie]

Is it possible to take these Queue items and put them back into a (new) dataset?

Private Function GetCustomerInfo() As DataSet
        Dim Stores As String() = { _
            CStr(1128), _
            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 = GetRandomCustomers.Tables(0).Select("LastStore = '" & storeNum & "'")
            If myrows.Length > 0 Then
                value = CustomerPicker.Next(0, myrows.Length)[b]
                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)[/b]
            End If
            Dim en As System.Collections.IEnumerator = rendomCustQueue.GetEnumerator()
 
            While en.MoveNext()
                ' Create and Return Dataset here???
            End While
        Next
    End Function

I tried this but it didn't work.

Dim ds As New DataSet
                Dim dt As New DataTable
 
                Dim FullName As DataColumn
                Dim Address As DataColumn
                Dim City As DataColumn
                Dim State As DataColumn
                Dim Zip As DataColumn
                Dim Store As DataColumn
 
                FullName = (rendomCustQueue.Enqueue(myrows(value).Item("FullName").ToString))
etc....

[Jumpercables]

You are going to have to create the dataset structure you want and then insert the items from the queue into the dataset in their respective columns.

Try looking at the following link.
http://msdn2.microsoft.com/en-us/library/ms171909.aspx

[FastEddie]

I think I understand that part of it and I can create a datatable on the fly with this code.

Dim ds As DataSet
        Dim tbl As DataTable
        Dim col1, col2 As DataColumn
 
        ds = New DataSet()
        tbl = New DataTable("mytable")
        col1 = New DataColumn("Name", Type.GetType("System.String"))
        col2 = New DataColumn("Address", Type.GetType("System.String"))
 
        tbl.Columns.Add(col1)
        tbl.Columns.Add(col2)
        tbl.Rows.Add("Fast Eddie", "123 Nowhere")
        tbl.Rows.Add("Tom Servo", "123 Space")
 
        ds.Tables.Add(tbl)
        DataGridView1.DataSource = tbl

My problem is that I don't know how to pluck the items from the queue and place them in my dataset.

[Jumpercables]

You can cycle through your queue using similiar code, however where they belong in your dataset depends on your information in the queue.

Dim numbers As New Queue(Of String)
        numbers.Enqueue("one")
        numbers.Enqueue("two")
        numbers.Enqueue("three")
        numbers.Enqueue("four")
        numbers.Enqueue("five")
 
        ' A queue can be enumerated without disturbing its contents.
        For Each number As String In numbers
            Dim myRow As DataRow = myDataSet.Table(0).NewRow ' I think this is correct
            myRow(0) = number 
            Debug.Print(number)
             
           myDataSet.Table(0).Row.Add(myRow)
 
        Next

I cannot be positive the syntax is correct so dont just copy and paste it becuase I might be missing something, at work right now but not at a computer with VS on it.

[FastEddie]

I am lost. Is this anywhere near close to what I need?

For Each storeNum As String In Stores
            myrows = GetRandomCustomers.Tables(0).Select("LastStore = '" & storeNum & "'")
            If myrows.Length > 0 Then
                value = CustomerPicker.Next(0, myrows.Length)
                Dim aDs As New DataSet
                Dim numbers As New Queue(Of String)
                numbers.Enqueue("FullName")
                numbers.Enqueue("Adresss")
                numbers.Enqueue("three")
                numbers.Enqueue("four")
                numbers.Enqueue("five")
                For Each number As String In numbers
                    Dim myRow As DataRow = aDs.Tables(0).NewRow
                    myRow(0) = number
                    Debug.Print(number)
                    ' aDs.Tables(0).Row.Add(myRow)
                Next
            End If
        Next

[VBDT]

Hi FastEddie, why you want to put the queue items in to another dataset?

[FastEddie]

Because my last step is to take the customer list and address information and create an XML file so that it can be used with a merge doc or mailing label program.

From start to finish I had a list of 500 customers (all stores) that needed to be narrowed down to one winner from each store (29). I have that now so I need to take the 29 randomly selected customers and send them to an XML file for merging into a form letter and mailing labels.

[VBDT]

I think I did not explain the queue in my last post. The queue is an abstract construction and the order of data it has is firs in firs out. Now one thing to make the queue usable in your case is to save an entire array with the customer’s info in the queue. In that way it will be more usable. Like this:

rendomCustQueue.Enqueue(myrows(value))

instad of this

numbers.Enqueue("FullName")
 numbers.Enqueue("Adresss")
 numbers.Enqueue("three")
 numbers.Enqueue("four")
 numbers.Enqueue("five")

and you can get the queue items using

rendomCustQueue.Dequeue() 'it will return the first item in the queue and remove the item from the queue.

or

rendomCustQueue.Peek() 'it will return the first item in the queue with out removing the item from the queue.

Now I am not sure if you need a dataset to creat an xml file. If you want to creat dataset in order to have indexed access to the wining customers information then the abov exaple will do. just Dequeue the queue items and put them in xml file.

[VBDT]

By the way if you want to get the customer’s row and info from the queue you can use

Dim name, address As String
 Dim dr As DataRow
 dr = CType(rendomCustQueue.Dequeue, DataRow)
 name = CStr(dr.Item("FullName"))
 address = CStr(dr.Item("Adresss"))

[VBDT]

I have never created xml file. Is it the same as you create a text file? You open the file and write in it. If it so then you don’t need a queue even. You can write the customer info in the For Each loop and close the file after the loop is over.

[FastEddie]

Yes there is a little more involved but it is essentialy the same thing. Can you point me to a sample?

[FastEddie]

I already have code that creates an XML file that is perfect for my mail merge so if I can get this to work in a dataset I am home free.

By the way if anyone is interested here is the code to turn a DS into an XML file here it is.

myDataset.WriteXml("c:\Customers.xml", XmlWriteMode.WriteSchema)
        Dim xmlSW2 As System.IO.StreamWriter = New System.IO.StreamWriter("Winners.xml")
        Try
            myDataset.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            xmlSW2.Flush()
            xmlSW2.Close()
        End Try

[VBDT]

I have not created xml file but I searched this forum and I got some code which I put to show how you can do it in the for each loop. This only writes the customer name but you can add address and all the info in the loop.

Imports System
Imports System.Xml
 
Public Class Form1
 
    Private Sub GetRandomCustomers()
 
        Dim myDataset As New DataSet()
 
 
        '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
 
        ' Create the XmlDocument.
        Dim doc As XmlDocument = New XmlDocument()
        doc.LoadXml("<item><name>wrench</name></item>")
 
        '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 write it on xml file.
                'Add a customer name element.
                Dim newElem As XmlElement = doc.CreateElement("FullName")
                newElem.InnerText = CStr(myrows(value).Item("FullName"))
                doc.DocumentElement.AppendChild(newElem)
            End If
            'Go to the next store
        Next
        ' Save the document to a file and auto-indent the output.
        Dim writer As XmlTextWriter = New XmlTextWriter("data.xml", Nothing)
        writer.Formatting = Formatting.Indented
        doc.Save(writer)
 
    End Sub
End Class

[VBDT]

or if you want to use dataset for writing xml file then this is what you want

Private Sub GetRandomCustomers()
 
        Dim myDataset As New DataSet()
        '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
 
        'create a new dataset
        Dim ds As New DataSet
        ds = myDataset.Copy
        ds.Clear()
 
        '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 wining customer data row and adds it to a new dataSet.
                ds.Tables(0).Rows.Add(myrows(value))
            End If
            'Go to the next store
        Next
 
        'write ds to xml.
        ds.WriteXml("c:\Customers.xml", XmlWriteMode.WriteSchema)
        Dim xmlSW2 As System.IO.StreamWriter = New System.IO.StreamWriter("Winners.xml")
        Try
            ds.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            xmlSW2.Flush()
            xmlSW2.Close()
        End Try
    End Sub

[FastEddie]

I am really trying to do this without having to come back here for help on every step of the way but this has (obviously) been kicking my butt. I had to change my Sub slightly from the way I had it last night so I don't know if that is causing the problem or it is something else. Here is the entire sub.

Private Sub GetCustomers()
        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
 
        'create a new dataset
        Dim ds As New DataSet
        ds = myDataset.Copy
        ds.Clear()
 
        '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 wining customer data row and adds it to a new dataSet.
                ds.Tables(0).Rows.Add(myrows(value))
            End If
            'Go to the next store
        Next

The error is This row already belongs to another table.

[VBDT]

Hey I am not tired to help you so you come back and ask as much as you like, it is OK with me. Now the error spouse to happen because the row we were tring to add is already a row for the other table so what I did here is to copy the row into a new “ds.Table” row and then add the now row to the “ds.Table”. Unfortunately I can’t run the code in my computer. I don’t have the customers’ data but try this to see if it works.

'create a new dataset
        Dim ds As New DataSet
        ds = myDataset.Copy
        ds.Clear()
        Dim dr As DataRow
        '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)
                dr = ds.Tables(0).NewRow
                dr.ItemArray = myrows(value).ItemArray
                'Use the random number as an index in "myrows" 
                'to get the wining customer data row and adds it to a new dataSet.
                ds.Tables(0).Rows.Add(dr)
            End If
            'Go to the next store
        Next

[FastEddie]

VBDT you nailed it. Thank you very much! With your help I was able to complete this project and get the letters out on time. I'll send you a virtual drink of your choice. Cheers!

[VBDT]

Well, that should be “Skyy Vodka” or tequila
I am glad it worked.
Cheers!

[Jumpercables]

You can write a dataset to xml with one call.

myDataSet.WriteXml("C:\MyXML.xml")

[FastEddie]

Here's to ya!

[FastEddie]

JC thanks for the shortcut. I thought I was going to need the SCHEMA with my file but it turns out I didn't so your one liner works perfectly.