• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Error in Select Statement VB 2008

Hi EE,

Below I have a select statement to access a company table in MS Access.
There is one record in the table, but it returns an error saying that there is no data in the row or column... there is...
It also passes the has rows, but when it gets to the first field "CompanyID", I get the error.
What am I doing wrong?
Thanks
Dim ds As New DataSet
 
        If DatabaseToUse() = BizObjs.DBType.Access Then
            'Access Database
            Dim dc As New OleDb.OleDbConnection(ConnectionString)
            Dim cmd As New OleDb.OleDbCommand("Select * from Company", dc)
 
 
            Try
                dc.Open()
                Dim dr As OleDbDataReader = cmd.ExecuteReader()
                If dr.HasRows Then
                    CompanyID = dr("CompanyID")
                    CompanyName = dr("CompanyName")
                    Add1 = dr("Address1")
                    Add2 = dr("Address2")
                    City = dr("CityName")
                    StateID = dr("StateID")
                    Postal = dr("Postal")
                    CntryID = dr("CntryID")
                    DefaultStateID = dr("DefaultStateID")
                    DefaultCntryID = dr("DefaultCntryID")
 
                End If
                dr.Close()
            Catch ex As Exception
 
                MessageBox.Show(ex.Message)
                Throw ex
 
            Finally
                dc.Close()
 
            End Try
        End If

Open in new window

0
Sheritlw
Asked:
Sheritlw
2 Solutions
 
cdaly33Commented:
You need to call dr.Read to get the first record.
Dim ds As New DataSet
 
        If DatabaseToUse() = BizObjs.DBType.Access Then
            'Access Database
            Dim dc As New OleDb.OleDbConnection(ConnectionString)
            Dim cmd As New OleDb.OleDbCommand("Select * from Company", dc)
 
 
            Try
                dc.Open()
                Dim dr As OleDbDataReader = cmd.ExecuteReader()
                If dr.HasRows Then
                    dr.Read 'read the first record
                    CompanyID = dr("CompanyID")
                    CompanyName = dr("CompanyName")
                    Add1 = dr("Address1")
                    Add2 = dr("Address2")
                    City = dr("CityName")
                    StateID = dr("StateID")
                    Postal = dr("Postal")
                    CntryID = dr("CntryID")
                    DefaultStateID = dr("DefaultStateID")
                    DefaultCntryID = dr("DefaultCntryID")
 
                End If
                dr.Close()
            Catch ex As Exception
 
                MessageBox.Show(ex.Message)
                Throw ex
 
            Finally
                dc.Close()
 
            End Try
        End If

Open in new window

0
 
Pratima PharandeCommented:
try this also

refer

http://aspnet101.com/aspnet101/tutorials.aspx?id=17
Dim ds As New DataSet
 
        If DatabaseToUse() = BizObjs.DBType.Access Then
            'Access Database
            Dim dc As New OleDb.OleDbConnection(ConnectionString)
            Dim cmd As New OleDb.OleDbCommand("Select * from Company", dc)
 
 
            Try
                dc.Open()
                Dim dr As OleDbDataReader = cmd.ExecuteReader()
                While objDR.Read()
                    CompanyID = dr("CompanyID")
                    CompanyName = dr("CompanyName")
                    Add1 = dr("Address1")
                    Add2 = dr("Address2")
                    City = dr("CityName")
                    StateID = dr("StateID")
                    Postal = dr("Postal")
                    CntryID = dr("CntryID")
                    DefaultStateID = dr("DefaultStateID")
                    DefaultCntryID = dr("DefaultCntryID")
 Exit While
                End While
                dr.Close()
            Catch ex As Exception
 
                MessageBox.Show(ex.Message)
                Throw ex
 
            Finally
                dc.Close()
 
            End Try
        End If

Open in new window

0
 
SheritlwAuthor Commented:
Thanks, both ways work.
I will split points.
Thanks again,
Sheri
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now