[Jiro_]

I am writing an application that has to manage a small database. one of the tasks of the application is that it has to retrieve the information of a company. I tried reading the data into my List(Of String) but somehow it keeps claiming not to return any data. But when I use the debugger, I see that it has 9 fields in it (visibleFields property)
this is the code I am using for the moment:

Public Function getFirmaDetails(ByVal firma As String) As List(Of String)
        Dim Query As String = "SELECT tblplatenfirma.fldaanspreking, tblplatenfirma.fldtav, tblplatenfirma.fldnaam, tblplatenfirma.fldstraat, tblplatenfirma.fldpost, tblplatenfirma.fldgem, tblplatenfirma.fldtel, tblplatenfirma.fldfax, tblplatenfirma.fldreknr FROM tblplatenfirma WHERE (((tblplatenfirma.fldnaam)=""" + firma + """));"
        Dim connection As New OleDbConnection(My.Settings.Oefen2010__Peeters_ConnectionString)
        connection.Open()
        Dim command As New OleDbCommand(Query, connection)
        Dim reader As OleDbDataReader = command.ExecuteReader()
        If reader.HasRows Then
            Dim results As List(Of String) = New List(Of String)
            For i = 0 To reader.FieldCount - 1
                'now read the data in the database
                results.Add(reader(i))
            Next
            Return results
        Else
            Return Nothing
        End If
    End Function

(please ignore the huge SQL injection security hole )
the strange thing is, that with this code I am able to retrieve the names of the fields:

Public Function getFirmaDetails(ByVal firma As String) As List(Of String)
        Dim Query As String = "SELECT tblplatenfirma.fldaanspreking, tblplatenfirma.fldtav, tblplatenfirma.fldnaam, tblplatenfirma.fldstraat, tblplatenfirma.fldpost, tblplatenfirma.fldgem, tblplatenfirma.fldtel, tblplatenfirma.fldfax, tblplatenfirma.fldreknr FROM tblplatenfirma WHERE (((tblplatenfirma.fldnaam)=""" + firma + """));"
        Dim connection As New OleDbConnection(My.Settings.Oefen2010__Peeters_ConnectionString)
        connection.Open()
        Dim command As New OleDbCommand(Query, connection)
        Dim reader As OleDbDataReader = command.ExecuteReader()
        If reader.HasRows Then
            Dim results As List(Of String) = New List(Of String)
            For i = 0 To reader.FieldCount - 1
                'now read the data in the database
                Console.WriteLine(reader.GetName(i))
            Next
            Return results
        Else
            Return Nothing
        End If
    End Function

any help would be appreciated, if you have more questions just ask.
thanks in advance!

[andrewsw]

Use reader.GetValue(i)

Alternatively, use GetValues to get all the values in one go into an array.

MSDN

Quote

For most applications, the GetValues method provides an efficient means for retrieving all columns, instead of retrieving each column individually.

[Jiro_]

andrewsw, on 02 February 2013 - 10:21 AM, said:

Use reader.GetValue(i)

Alternatively, use GetValues to get all the values in one go into an array.

MSDN

Quote

For most applications, the GetValues method provides an efficient means for retrieving all columns, instead of retrieving each column individually.

that was one of the many solutions I tried, unfortunatly that won't work either. the solution I found now (and that works) is this:

Public Function getFirmaDetails(ByVal firma As String) As List(Of String)
        'SELECT tblplatenfirma.fldaanspreking, tblplatenfirma.fldtav, tblplatenfirma.fldnaam, tblplatenfirma.fldstraat, tblplatenfirma.fldpost, tblplatenfirma.fldgem, tblplatenfirma.fldtel, tblplatenfirma.fldfax, tblplatenfirma.fldreknr FROM tblplatenfirma WHERE (((tblplatenfirma.fldnaam)=""" + firma + """));
        Dim Query As String = "SELECT tblplatenfirma.fldaanspreking, tblplatenfirma.fldtav, tblplatenfirma.fldstraat, tblplatenfirma.fldpost, tblplatenfirma.fldgem, tblplatenfirma.fldtel, tblplatenfirma.fldfax, tblplatenfirma.fldreknr FROM tblplatenfirma WHERE (((tblplatenfirma.fldnaam)=""" + firma + """));"
        Dim connection As New OleDbConnection(My.Settings.Oefen2010__Peeters_ConnectionString)
        connection.Open()
        Dim command As New OleDbCommand(Query, connection)
        Dim reader As OleDbDataReader = command.ExecuteReader(Commandbehavior.CloseConnection)
        If reader.HasRows Then
            Dim result As New List(Of String)
            While reader.Read()
                For index = 0 To reader.FieldCount - 1
                    Try
                        result.Add(reader.Item(index))
                    Catch ex As InvalidCastException
                        result.Add("") 'add emtpy string since a dbNull was returned
                    End Try
                Next
            End While
            Return result
        Else
            Return Nothing
        End If
    End Function

pay attention to the fact that the entire reading function is inside a while reader.read() loop, which seems to be vital to retrieve data