Arraylist and zero ID'S

[prgwhiting]

I have an odd problem that I cannot find any solution or explanation for. I'm using ADO.Net to create arraylists for combo boxes and listboxes and all of these work fine except for one aspect. I have for sometime now used look up tables with a starting ID of zero which indicates that the actual value is (Unknown). This has always worked fine in the past, meaning that I have no null entries, and that if the entry on a table is zero, my look up table will display the word "(Unknown)". I have now moved from using ADO to ADO.NET and this routine no longer works as any ID that is Zero gets missed from the arraylist that I create. Is there any way round this? I've included the Function that I use to create my array lists below.

Public Shared Function Create_Array_List(ByVal _Connection As String, ByVal _Data As String, _
Optional ByVal _Filter As String = "", Optional ByVal _Sort_Order As String = "") As ArrayList
            On Error GoTo err_trap

            Dim obj_Connection As SqlConnection
            Dim obj_Command As SqlCommand
            Dim obj_Reader As SqlDataReader
            Dim return_List As ArrayList

            If _Data Like "*tk_PropertyTypes*" Then Stop

            obj_Connection = New SqlConnection(_Connection)
            'Set Data to be used
            obj_Command = New SqlCommand(_Data & " " & _Filter & " " & _Sort_Order, obj_Connection)
            'Open the connection
            obj_Connection.Open()
            'Set data reader
            obj_Reader = obj_Command.ExecuteReader()
            'Create new array list
            return_List = New ArrayList()

            If obj_Reader.Read Then

                While obj_Reader.Read

                    Dim dm As New Data_Management

                    If obj_Reader.FieldCount = 1 Then

                        dm.Data_To_Display = obj_Reader.GetValue(0).ToString

                    Else

                        dm.ID = obj_Reader.GetValue(0)
                        dm.Data_To_Display = obj_Reader.GetValue(1).ToString

                    End If

                    return_List.Add(dm)

                    dm = Nothing

                End While

            End If

            Return return_List
            Exit Function

err_trap:

            MsgBox(Err.Description)

        End Function

I would be everso grateful if anyone could point me in the right direction



Actually I've just had another look at this and I don't think the problem is with a Zero ID. I think the ADO.Net is just exiting too soon before picking up the last Item. Not sure. Still would like some pointers though. Thanks


Okay. I've managed to sort this one. Here is the code as it should read.

Public Shared Function Create_Array_List(ByVal _Connection As String, ByVal _Data As String, _
Optional ByVal _Filter As String = "", Optional ByVal _Sort_Order As String = "") As ArrayList
            On Error GoTo err_trap

            Dim obj_Connection As SqlConnection
            Dim obj_Command As SqlCommand
            Dim obj_Reader As SqlDataReader
            Dim return_List As ArrayList
            Dim i As Integer

            obj_Connection = New SqlConnection(_Connection)
            'Set Data to be used
            obj_Command = New SqlCommand(_Data & " " & _Filter & " " & _Sort_Order, obj_Connection)
            'Open the connection
            obj_Connection.Open()
            'Set data reader
            obj_Reader = obj_Command.ExecuteReader()
            'Create new array list
            return_List = New ArrayList()

                Dim dm As New Data_Management

                While obj_Reader.Read

                    If obj_Reader.FieldCount = 1 Then

                        dm = New Data_Management
                        dm.Data_To_Display = obj_Reader.GetValue(0).ToString
                        return_List.Add(dm)
                        dm = Nothing

                    Else

                        dm = New Data_Management
                        dm.ID = obj_Reader.GetValue(0)
                        dm.Data_To_Display = obj_Reader.GetValue(1).ToString
                        return_List.Add(dm)
                        dm = Nothing

                    End If

                End While

                dm = Nothing

            Return return_List
            Exit Function

err_trap:

            MsgBox(Err.Description)

        End Function

I had added an extra layer of complication by trying to establish if there were any records to return by using the If obj_Reader.Read. I think this was causing the code to exit too early when it came to the last row. Happier now.

 

[cjard]

I think you need to ditch the arraylist idea. It was nice back then, but now we just create a 2 column DataTable and bind it to the combo at design time, fill it at run time and the combo shows the values. Basically what I'm trying to say is: the code you have there I can reduce to 1 line, with a few mouse clicks.. :/

For more information, read the DW2 link in my signature

ps; grammar note: You would say ArrayList IDs not ArrayList ID'S