[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

*****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
0
tanjastar
Asked:
tanjastar
  • 7
  • 5
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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