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

x
?
Solved

How to Bind Data to a ComboBox from MS Access DB to VB Form

Posted on 2006-05-15
4
Medium Priority
?
705 Views
Last Modified: 2012-05-05
Hey whats up experts!!

I have a small problem. I want to bind a combobox with data from my MS access file. All I want to do is get a particular Field name and populate
the combobox with its attributes within the combobox selection. So if I have to pick let say Names I want the combo box to read off the database and collect
all the names within that particular field name and populate itself within the combo box so like that when the user decides to use the combo box they get
to see all the users in it.

I so far Im having trouble getting this to work....I though of using a OledbDataReader to see if that would be possible , but so far I'm unsuccessful at finishing the task.

I was wondering if it is indeed the way to do this to begin with...Here is my sample code and please let me know if I went the wrong way of the road...which im sure I did lol

///////check this out guys/////////

 Dim myConnection As New OleDb.OleDbConnection(" Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\db2.mdb; ")
        Dim DirectReader As OleDb.OleDbDataReader

        myConnection.Open()
        Dim SearchCmd As New OleDb.OleDbCommand("Select FName From [Names] ", myConnection)


       
        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = SearchCmd


        Try

            Adapter.Fill(DataSet)
            DirectReader = SearchCmd.ExecuteReader()


            'To bind the Dataset to the DataGrid :)
            ComboBoxBinding.DataSource = DataSet

           
          While DirectReader.Read
                ComboBoxBinding.SelectedText = DirectReader("Fname").ToString
            End While


        Catch ex As OleDbException

            MsgBox(ex.Message)
        Finally


        End Try

        myConnection.Close()

////////////////////////////////

Although I feel that I'm getting close to the answer when I run this I get an Crazy output for my combo Box.
This is what I get in my ComboBox display " vvAASystem.Data.DataViewManagerListItemTypeDescriptor " Now the (vvAA) is actually the LAST primay key set of the FName field in the table [Names] ...so meaning I know my sloppy code was getting close to its objective....but not close enough lol


Thankyou in advance guys for the help and please explain well to this noob if you are willing to give me a helping hand.




0
Comment
Question by:masterat03
  • 2
  • 2
4 Comments
 

Author Comment

by:masterat03
ID: 16684612
So um...anyone willing to help?!?
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 16686349
You're mixing up a number of different approaches here.  

If you want to get data from a database, you would use either a DataReader or a DataAdapter but not both.  

If you use a DataReader, you could "fill" a combobox with the values that it retrieves, but you couldn't "bind" the combobox to that data (unless you used the DataReader to fill an array or datatable and bound the combobox to that).

If you want to fill (rather than binding) a combobox with data you would do so with ComboBox.Items.Add(item) with each item as you brought it from the database.  ComboBox.SelectedText sets (or gets) what is displayed in the editable text portion of the combobox: it does not affect the list of things which the combobox displays when dropped down.  As your program loops through the records that the datareader is fetching it replaces what it did last time, which is why all that is showing is the last value (the additional gobbledegook is a different issue, which needn't concern us here).

The difference between "filling" and "binding", as I'm using the terms here, is that, with "filling" the items for the drop-down list are put into the combobox on a one off basis and they are then independent of any data source.  But with "binding" a link is established between the combobox and a datasource so that (a) if the data in the datasource changes the list in the combobox automatically reflects that change and (b) the link is to a record in the data source.  This means that a different user-selection in the combobox shifts the pointer in the datasource to the whole of the record containing that value: so other elements in that record are accessible.  In your scenario, for instance, if each FName had a ID value with it, you could - when a user selected a particular FName in your combobox - find out directly from the bound data source what the ID was.

Given that your Select statement is only collecting FName, then perhaps you are not really interested in "binding"?  In which case, your code could be on these lines

Dim myConnection As New OleDb.OleDbConnection(" Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\db2.mdb; ")
        Dim DirectReader As OleDb.OleDbDataReader

        myConnection.Open()
        Dim SearchCmd As New OleDb.OleDbCommand("Select FName From [Names] ", myConnection)


        Try

            DirectReader = SearchCmd.ExecuteReader()

           
          While DirectReader.Read
                ComboBoxBinding.Items.Add(DirectReader("Fname").ToString)
            End While


        Catch ex As OleDbException

            MsgBox(ex.Message)
        Finally


        End Try

        myConnection.Close()


But if you do want to "bind", you should probably use a DataAdapter, it fills a DataTable not a DataSet.  If, when you call .Fill(DataSet) that DataSet does not have any (or the relevant) DataTable in it, the call will create the necessary DataTable within it.  And any binding then has to be to the DataTable.  If (as looks to be the case here), the table created by the fill will be the only one, and as tables are added to a DataSet from a zero-based index, that table would be DataSet.Tables(0).  Your code then would be on these lines

Dim myConnection As New OleDb.OleDbConnection(" Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\db2.mdb; ")

        Dim SearchCmd As New OleDb.OleDbCommand("Select FName From [Names] ", myConnection)


       
        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = SearchCmd


        Try

            Adapter.Fill(DataSet)


            'To bind the Dataset to the DataGrid :)
            ComboBoxBinding.DataSource = DataSet.Tables(0)
            ComboBoxBinding.DisplayMember = "FName" 'this is the column in the datatable from which the drop down list would be filled
            'ComboBoxBinding.ValueMember = "ID" 'see my comment above about how "binding" allows you to get other associated values from a selected record

           
        Catch ex As OleDbException

            MsgBox(ex.Message)
        Finally


        End Try

With a DataAdapter (unlike a DataReader) you do not need to open and close the connection: it does it itself.

I've not checked the above code.  I've just more or less copied and pasted appropriate parts of what you already had.  As you said, you had it nearly right.  If, having decided what you want to do and which approach you want to use to do it, the detail of the code gives any problems, come back posting the code you are then using and we'll check it out.

Roger
0
 

Author Comment

by:masterat03
ID: 16691914
Wow thanks for the info on the subject it made it much clearer for me. As for me I will go with the binding code because that is more suitable for my objective. The reason I was using both is because I thought for some crazy reason you needed the datareader to read off the database and then try to fill it with the adapter.


Adapter.Fill(DataSet)


            'To bind the Dataset to the DataGrid :)
            ComboBoxBinding.DataSource = DataSet.Tables(0)
            ComboBoxBinding.DisplayMember = "FName" 'this is the column in the datatable from which the drop down list would be filled
            'ComboBoxBinding.ValueMember = "ID" 'see my comment above about how "binding" allows you to get other associated values from a selected record

           
        Catch ex As OleDbException

/////////

In this code statement what you are try to tell me is that if for example Fname had a proceeding field that has attributes to it which would be let say Lname then it would also display it or no?..

So example in this case
ComboBoxBinding.DataSource = DataSet.Tables(0)
            ComboBoxBinding.DisplayMember = "FName" * I get the point of the Display member because this is the one I want to fill.
            'ComboBoxBinding.ValueMember = "LName" '  * The Value member does this mean the proceeding Field name after FName or is this a way to say
Ok Fname is (John) and his ID number is  (15) which ID would be in the place of  Valuemember .


///////////////

I tried to do the binding and it works as well...although not part of this whole topic but it has its relevants I wanted to know Example

When I bind the data to the combobox...does VB have a function to have the same code run again let say every 5 minutes...The reason I though about this is because If let say the database is updated about 1hour ago...my users won't be able to know if it did get updated unless they close and open the program again....
So i was just wondering if VB has a special function or perhaps a way on how to refresh data within a certain time interval.


Thankyou Sancler :) for the nice explanation...believe me I put that in my personal notepad notes to go back on and review .



 
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16692766
On the value member point, it can be any field which is part of the same record and which is brought over from the database by your select statement.  At the moment, you couldn't use it, because your select statement only brings over one field - FName.  Or you could use it, but you would have to say

   ComboBoxBinding.ValueMember = "FName"

that is, make it the same as the DisplayMember, because that is the only field in your datatable.  But if your Select statement said "SELECT FName, LName, ID FROM [Names]" you could set the DisplayMember as any one of those and the ValueMember as any one of them.  The order in wwhich the fields appear in the database table, or the datatable, is not important.  

I used ID as an example because the way in which this is most often used is so that the user can see (via the DisplayMember) some user-friendly data (like a name) but the program can easily pick up from that some machine-friendly data (like an identification code).

As to the other question, it sounds like you want to use a Timer.  Have a look in the help files for information about it.

Roger
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline
Suggested Courses

872 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