Solved

Error in Select Statement VB 2008

Posted on 2009-03-31
3
186 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

16 Experts available now in Live!

Get 1:1 Help Now