Reading an Access Database

[shuey79]

Hello,
I am a beginner trying to make a networked game called Guess Who.
I have 2 combo boxes
I am trying to read a access database. Whatever is selected in the 1st combo box will be the column to 'lookup' the values in the database. These values should go into the 2nd combo box. I keep getting this error:"Conversion from type 'DBNull' to type 'String' is not valid" on line 21 when I select "Eye Color" in the 1st combo box. I'm guessing its because its reading the null value after the reading "Brown".
How do you stop this from happening?

My database looks like the following:
The column names are in between the [ ] sorry don't know how to format this..

[Eye Color]   [Hair Color]
 Blue               Black
Brown             Blonde
                      Brown
                      Red

This is the code I have to read the database:

Public Sub Question_Update()
        Dim Cmd As OleDb.OleDbCommand
        Dim Con As OleDb.OleDbConnection
        Dim Sql As String = Nothing
        Dim Reader As OleDb.OleDbDataReader
        Dim ComboRow As Integer = -1
        Dim Columns As Integer = 0
        Dim Category As String = Nothing

        Dim based As String = cmbFeatures.Text
        cmbQuestion.Items.Clear()

        Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Questions.mdb")
        Sql = "SELECT" & " [" & based & "] " & "FROM [Questions]"
        Cmd = New OleDb.OleDbCommand(Sql, Con)
        Con.Open()
        Reader = Cmd.ExecuteReader() 
        While Reader.Read()
            For Columns = 0 To Reader.FieldCount - 1
                MsgBox(Reader.Item(Columns).ToString)
                Category = Reader.Item(Columns) 'READ COLUMN FROM DATABASE
            Next
            cmbQuestion.Items.Add(Category)
            ComboRow += 1
        End While


        Con.Close()
    End Sub

[Chris147]

Hi, I haven't implemented this but it should work:

<ul><li>While Reader.Read<strong>()</strong></li>
<li><strong>     Try</strong><LI class=li1>            <strong>For</strong> Columns = 0 <strong>To</strong> Reader.FieldCount - 1
<LI class=li2>                MsgBox<strong>(</strong>Reader.Item<strong>(</strong>Columns<strong>)</strong>.ToString<strong>)</strong>
<LI class=li1>                Category = Reader.Item<strong>(</strong>Columns<strong>)</strong> 'READ COLUMN FROM DATABASE
<LI class=li1>            <strong>Next</strong>
<LI class=li1>            cmbQuestion.Items.Add<strong>(</strong>Category<strong>)</strong>
<LI class=li1>            ComboRow += 1
<LI class=li1>    Catch ex As Exception
<LI class=li1>            Exit While
<LI class=li1>    End Try</li>
<li>End While </li>
</ul>

HTH,

Chris.

[Chris147]

Oops, not sure what happened to the formatting in my post - sorry. (Ignore the <...> bits.)
Chris.

[shuey79]

Thanks so much, it works great.

[Chris147]

No worries, glad I could help.:)

[shuey79]

How would I read the values in based on a specific value in a row. Say I want to read all of Adam's properties in.
my table is:

[Name]     [Hair Color]       [Eye Color]      [Glasses]
       Frank        Blonde              Blue            Yes
       Adam         Brown               Brown           No
       Sal          Black               Brown           Yes

My code is same as above:

Public Sub Question_Update(ByVal name As String)
        '****************************************************************
        'Reads access database table to instantiate players properties
        '****************************************************************

        Dim Cmd As OleDb.OleDbCommand
        Dim Con As OleDb.OleDbConnection
        Dim Sql As String = Nothing
        Dim Reader As OleDb.OleDbDataReader
        Dim ComboRow As Integer = -1
        Dim Columns As Integer = 0
        Dim Category As String = Nothing

        Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Resources\BoardPeople.mdb")
        Sql = "SELECT [Name] FROM [BoardPeople]"
        'Sql = "SELECT" & " [" & name & "] " & "FROM [BoardPeople]"
        Cmd = New OleDb.OleDbCommand(Sql, Con)
        Con.Open()
        Try
            Reader = Cmd.ExecuteReader()
            While Reader.Read()
                Try
                    For Columns = 0 To Reader.FieldCount - 1
                        Category = Reader.Item(Columns) 
                    'Based on what Category is it shoudl then read the entire row.
                    Next
                    ComboRow += 1
                Catch ex As Exception
                    Exit While
                End Try
            End While
        Catch ex As Exception
           
        End Try
        Con.Close()
    End Sub

Thanks in advance!

[bmndayakar]

It throws an error for datareader as E 'System.Data.OleDb.OleDbDataReader.Friend Sub New(connection As System.Data.OleDb.OleDbConnection, command As System.Data.OleDb.OleDbCommand, depth As Integer, commandBehavior As System.Data.CommandBehavior)' is not accessible in this context because it is 'Friend'

can some one help me