*****Fast answer needed****** Fill listbox from multiple table query AccessDB

I am at my wits end.  I am trying to pull data from multiple tables in an access database and fill one dataset in VB.net.  I then want to fill a listview with that data

Please tell me what I am doing wrong.  I get an error when the code gets to the TestAdapter.Fill(TestDS, "Result") line.  The error is: IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
I read on another website that this method only works with SQL sever, but their has to be a way to do this with Access.  

Dim SelectCmd As String = "SELECT Author.Last, Article.Title, Article.Year" _
                                        & " FROM Article, Publication, Author, Contributor WHERE " _
                                & " (Article.PubAbbrev=Publication.Abbreviation) And " _
                                & " (Contributor.CoArtID=Article.ArticleID) And " _
                                & " (Contributor.Position=1) And (Contributor.CoAuthID=Author!AuthorID) " _
                                & "ORDER BY Author.Last;"

        'map the adapter
        TestAdapter = New OleDbDataAdapter(SelectCmd, TestConn)

        'map and fill the dataset
        TestDS = New DataSet("DataSet11")
        TestAdapter.Fill(TestDS, "Result")

        'populate the listbox
        'clear the listbox
        lstvArt.Items.Clear()

        'populate the listbox
        Dim count As Integer = 0
        Dim sdata As String 'need this for initializing the listitems
        While count <= TestDS.Tables("Result").Rows.Count - 1
            sdata = TestDS.Tables("Result").Rows(count)(1)
            Dim listItem As New ListViewItem(sdata)
            listItem.SubItems.Add(TestDS.Tables("Result").Rows(count)(2))
            listItem.SubItems.Add(TestDS.Tables("Author").Rows(count)(3))
            lstvArt.Items.Add(listItem)
            count = count + 1
        End While
tanjastarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RonaldBiemansCommented:
shouldn't this

Contributor.CoAuthID=Author!AuthorID)

be

Contributor.CoAuthID=Author.AuthorID)
0
RonaldBiemansCommented:
and remove the ;

ORDER BY Author.Last;"

to

ORDER BY Author.Last"



0
tanjastarAuthor Commented:
This query was developed in access for testing purposes.  If their are errors in the syntax then I will fix them and see if it helps.  
The query string is actually going to be developed in a different module depending on what a user selects in the search critera.  I would like to be able to run a query no matter what it is, assuming that it is passed in the right syntax.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RonaldBiemansCommented:
Hi tanjastar,

since the error occurs here

TestAdapter.Fill(TestDS, "Result")

there can be only three things wrong

in the select string is incorrect
the connection string is incorrect
or the selection doesn't match the table in DataSet11
0
RonaldBiemansCommented:
have you tried putting a try catch around it maybe it will give a more precise error

try
   TestAdapter.Fill(TestDS, "Result")
catch ex as exception
   msgbox (ex.message)
end try
0
tanjastarAuthor Commented:
I already have a catch around the TestAdapter.fill(TestDS, "Result")  that is how I got the error described above.

RonaldBiemans,
the third thing you described:  or the selection doesn't match the table in DataSet11.
How is this possible?  The table in DataSet11 is defined by the selection.

I ready somewhere that you can not use a multi-table oledbDatabaseAdapter to fill a dataset.  I heard that you need to use an SQLDatabaseAdapter.  Is this true?

I don't have access to the program right now, but I will do the changes that you suggested to the query and let you know if that works.
0
RonaldBiemansCommented:
Hi tanjastar,

What you are trying should be possible with a oledbdataadapter, what is not possible in a oledbdataadapter is to return multiple dataset which you aren't doing.

so I definitly think there is a syntax error in your select string, so try the suggestion I made in the first 2 comments and let me know.
0
tanjastarAuthor Commented:
I tried your changes and I still get the same error.  Do you have any other ideas?  Maybe you could send me some code that you know works that I could modify to my situation.

Could you give me some code that populates a listbox on an unbound form from multiple tables in an access database.  
0
tanjastarAuthor Commented:
I meant to say listview.  I need some code that populates a listview on an unbound form from multiple tables in an access database
0
RonaldBiemansCommented:
Ok I tested this and it works

    Dim TestAdapter As OleDb.OleDbDataAdapter
    Dim TestDS As DataSet

    Private Sub Form23_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim selectcmd As String = "SELECT [author].[last], [article].[Title], [article].[Year] FROM (contributor INNER JOIN (publication INNER JOIN article ON [publication].[abbreviation]=[article].[pubabbrev]) ON [contributor].[coartID]=[article].[articleid]) INNER JOIN author ON [contributor].[coauthid]=[author].[authorid] WHERE((([contributor].[position]) = 1)) ORDER BY [author].[last]"
        TestAdapter = New OleDb.OleDbDataAdapter(selectcmd, OleDbConnection1)
        TestDS = New DataSet("Dataset11")
        Try
        TestAdapter.Fill(TestDS, "result")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Dim count As Integer = 0
        Dim sdata As String
        For x As Integer = 0 To TestDS.Tables("result").Rows.Count - 1
            Dim listitem As New ListViewItem(CStr(TestDS.Tables("result").Rows(x)(0)))
            listitem.SubItems.Add(TestDS.Tables("result").Rows(x)(1))
            listitem.SubItems.Add(TestDS.Tables("result").Rows(x)(2))
            ListView1.Items.Add(listitem)
        Next
    End Sub

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanjastarAuthor Commented:
Thank you so much.  It worked.  So it was just the way the SQL was formatted.  What do the brackets do []?  When looking up SQL statements I didn't see the brackets anywhere?

Could I have your email address, you have been so helpful?

0
RonaldBiemansCommented:
The brackets are optional, I always use them just in case some of the names in my database are reserverd words.

Why do you want my email address ?

By the way if the answer was correct could you then close this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.