connect Visual Basic and Access 2003

Hey,

I am trying to access a MS Access 2003 database from a Visual Basic project. I have written the following code in a module of the project. I call the dbOpen() function from some form code and then on another event I call the setAttracRecord() function, which causes an error.

Code:

---

Imports System.Data.OleDb

Module mdlDbConnect
    Private strConnect As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=database.mdb;Persist Security Info=False"
    Private objConnect As New OleDbConnection(strConnect)

    Sub dbOpen()
        objConnect.Open()
    End Sub

    Sub setAttracRecord(ByVal s As String)
        Dim strSQL As String = "SELECT * FROM tblAttrac"
        Dim cmd As New OleDbCommand(strSQL, objConnect)
        Dim dtRdr As OleDbDataReader
        dtRdr = cmd.ExecuteReader
        If dtRdr.HasRows Then
            MsgBox("Reader DOES have rows.")
        Else
            MsgBox("Reader does NOT have rows.")
        End If

        'error occurs on next line
        Dim strTest As String = dtRdr.GetString(0)
        MsgBox("Returned:" & strTest)
    End Sub

End Module

---

I edited the code to try and track down the problem. When I execute the code, the msgbox saying "Reader DOES have rows." is displayed and then the GetString() method causes and error. I get the debug message "No data exists for the row/column.".

I've tried to figure out this problem, on my own, but I've now run out of ideas. Because as far as I can tell dtRdr does have data in it, as HasRows() method returns true, but the debug message is telling me that it doesn't. Therefore, if anyone could help, I would very much appreciate it.


Thanks in advance,

- user0108

Try this

dtRdr.Read()
Dim strTest As String = dtRdr.GetString(0)

Edit:
You need to read in data before you can parse it out using GetString()

Darksnake's correct - you need to read a row before you can access the data. Quite a common thing you might want to do is go through all the rows like so:

Code:

---

while(reader.HasRows())
{
  reader.Read();

  // get some data and use it
}

---

One thing you have to be careful of (at least in SQL databases - I don't know about Access) is that you can have problems reading null values. So you might want to call reader.IsDBNull(x) (where x is the column) before using Getxxx().

ac

[edit]Bear in mind I use C# so the code above is probably not correct in VB[/edit]