Solved

Error in Select Statement VB 2008

Posted on 2009-03-31
3
187 Views
Last Modified: 2012-05-06
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
Comment
Question by:Sheritlw
3 Comments
 
LVL 9

Accepted Solution

by:
cdaly33 earned 250 total points
ID: 24035921
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
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 250 total points
ID: 24035945
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
 

Author Comment

by:Sheritlw
ID: 24036003
Thanks, both ways work.
I will split points.
Thanks again,
Sheri
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now