[RESOLVED] [2008] Returning multiple values from an Access Database

[robyn.berry]

....

[talkro]

what is the purpose of using commandBehavior.SingleRow?
You will then return only one row from your db even if they are more than one.

[robyn.berry]

....

[talkro]

and in your db you have multiple rows for sure?
have tried executing your query directly in your db to see what the result is?

[robyn.berry]

....

[talkro]

instead of

if reader.read

use

if reader.hasrows()
  while reader.read()
    'your code here
  end while
 
  reader.close
end if

[robyn.berry]

....

[talkro]

I noticed that you are over writing the value of itemGroup.ItemCode and itemgroup.ItemGroupID every time.
if I'm right those values should always correspond to the last item read in the reader.
may be you meat to work in some kind of collection where you store all the records.

[robyn.berry]

I am not sure how to write a collection for this...

???

[talkro]

Public Shared Function ReturnItems(ByVal sGroup As String) As list(of cItemGroup)
        Dim itemgroup As New cItemGroup
        dim myList as List(of cItemGroup) = new List(of cItemGroup)
 
        Dim connection As OleDbConnection = DBconnect.GetConnection3
        Dim sSQL As String = "SELECT Itemcode, M_ItemGroupID " & _
        "FROM Cust WHERE M_ItemGroupID = '" & sGroup & "'"
 
        Dim runSQL As New OleDbCommand(sSQL, connection)
        Try
            connection.Open()
            Dim reader As OleDbDataReader = runSQL.ExecuteReader(CommandBehavior.SingleRow)
            while reader.Read
                itemgroup.Itemcode = reader("Itemcode").ToString
                'itemgroup.ItemID = reader("M_ItemID").ToString
                itemgroup.ItemGroupID = reader("M_ItemGroupID").ToString
                myList.add(itemGroup)
            end while
            reader.Close()
        Catch ex As OleDbException
            itemgroup = Nothing
        Finally
            connection.Close()
        End Try
        Return myList
    End Function

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        dim myList as List(of cItemGroup) = cDBItemGroup.ReturnItems(Group.Text)
 
        for each cig as sItemGroup in myList
            messageBox.show(cig.ItemCode & " " & cig.ItemGroupID)
        next cig
    End Sub

[robyn.berry]

....

[talkro]

if you want to view all the item at once, you can put them in a listbox or listview(more likely because yo have at list two columns in your query). it is easier that having to maintain a bunch of labels

edited: Listview link

[robyn.berry]

....

[Tom Sawyer]

I've had this problem before too. The reason is that reader("M_ItemGroupID").ToString is not a string, it's a pointer to an object reference and the item in the listbox is not a string, it's an object. When the reader moves onto the next row, the pointer is then pointing to a reference with a different value, rather than what was there during the previous loop, so the object that gets displayed in the listbox is now something completely different than what you wanted to put in there.

Why it works this way, I don't know - it strikes me as somewhat silly behaviour that likely has a boring technical rationale behind it which only four people in the world care about.

To fix it, change your code to this:

while reader.Read      

    dim sCode as String =  reader("Itemcode").ToString 
    dim sGroupID as String = reader("M_ItemGroupID").ToString 
    itemgroup.Itemcode = sCode
    itemgroup.ItemGroupID = sGroupID             
    myList.add(itemGroup)            
end while

This creates new strings each time through the loop and adds those values to the listbox, so each item is referencing something different and they don't change as the reader moves onto the next row.

[robyn.berry]

....

[robyn.berry]

....

[talkro]

can you post the code you are using at this moment?

[robyn.berry]

....

[robyn.berry]

....

[talkro]

The error you get about Index out or range is because in your query you are only getting one column from the database. The number of items you read from the sqlDataReader must be consistent with the number of columns you are retrieving from the database.

[talkro]

replace this code

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
   Dim myList As List(Of cItemGroup) = cDBItemGroup.ReturnItems(Group.Text)
   For Each item As cItemGroup In myList
      ListBox1.Text = item.Itemcode
   Next item
End Sub

by

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
   Dim myList As List(Of cItemGroup) = cDBItemGroup.ReturnItems(Group.Text)
   me.listbox1.Items.AddRange(myList)
End Sub

[robyn.berry]

....

[robyn.berry]

....

[talkro]

my mistake, the AddRange method takes an array as parameter, so what you have to do is convert the list of to a convetional array

Me.ListBox1.Items.AddRange(myList.ToArray())

[robyn.berry]

....

[talkro]

I tried this piec of code which is similar to yours except for the database and it works fine

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim input() As String = New String() {"1", "2", "3", "4", "5", "6", "7", "8"}
        Dim mylist As List(Of String) = New List(Of String)
 
        'mylist.AddRange(input)
        For Each str As String In input
            mylist.Add(str)
        Next str
 
        Me.ListBox1.Items.AddRange(mylist.ToArray)
End Sub

Use

Dim sCode As String = reader.GetString(0)

instead of

Dim sCode As String = reader("Itemcode").ToString

[robyn.berry]

....

[talkro]

the problem is comming from the fact that the list that you return from the function that reads the table is collection of your object. When you use the 'ToArray' method, it accesses each object in the list and then gets the description ('Stockist_Price_Book.cItemGroup' ...) of the given object as defined in the 'ToString' function by default.

perhaps you should ignore post #26, but then overwrite the ToString method in you class 'cItemGroup' and make it return the 'itemcode'

public class cItemGroup
  Public Overrides Function ToString() As String
        return me.m_itemCode
    End Function
end class

[robyn.berry]

....

[talkro]

debug the Returnitem function and check what are the values added to 'myList'. what are the item in the list?

[robyn.berry]

....

[robyn.berry]

....