Question Populating combobox

[thirteentwenty]

I know this has been asked many times before, and I've read _most_ of the posts here (and elsewhere) but still haven't been able to figure this out.

OK so here goes (be gentle I was put into VB.NET against my will)

I have to connect to a data base (done code to follow) and populate several comboboxes (drilldown to boot), now I've done this once before but the method that I was using just didn't work as well as I thought it would (every time i added a combobox the load time for the form got longer and longer) so I decided to start from scratch...

Here's what I have so far, in a module i have this to open my database. The items that are commented out because they haven't been used yet.

I placed this in a module because I'll have to reference the database across several forms (if there is a better way please let me know), and I'll be gathering stuff from different tables to populate different items on the same form...

basically I'm looking to create something (with the help of the good folks here) that I can reuse throughout the program...

    Dim con As OleDb.OleDbConnection
    Dim query As String
    Public Function openDB()

        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb"
        con = New OleDb.OleDbConnection(connectionString)

        'Dim ds As New DataSet
        'Dim da As OleDb.OleDbDataAdapter
        'Dim dt As DataTable
        'Dim dr As DataRow
        'da = New OleDb.OleDbDataAdapter(query, con)

        con.Open()

    End Function

To fill the combobox in the other version that I had (I can admit it, a straight copy and paste job)

For Each rDataRow In rDataTable.Rows
            cmb_inv_reporter.Items.Add(rDataRow.Item("Name"))
        Next

how do i proceed?

Back story... I once built a web site for myself boss says build program... I say I can try... here I am one hair away from over my head...

Any help is appreciated...

And on a side note (read: question)
I understand why there are so many different ways to connect to a database (for the different types) but why is it that there are so many ways to get the same thing done? and why hasn't any one put a step by step together (an extremely basic thing that is) if there was one I couldn't find it... anyways...

thanks in advance to whom ever helps out...

 

[rajhansh]

You can simply bind the control to a dataset.

See the code below:

'these are the global variables

    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdapter As OleDbDataAdapter
    Private objDataTable As DataTable

'you can use below code in any event (like form load)

        'Initialize the Connection object
        objConnection = New OleDbConnection(strConnectionString)

        'Initialize the Command object
        objCommand = New OleDbCommand("SELECT ID, FirstName " & _
            "FROM Employee", objConnection)

        'Initialize the DataAdapter object and set the SelectCommand property
        objDataAdapter = New OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand

        'Initialize the DataTable object
        objDataTable = New DataTable

        'Populate the DataTable
        objDataAdapter.Fill(objDataTable)

        'Bind the DataTable to the ComboBox
        ComboBox1.DataSource = objDataTable
        ComboBox1.DisplayMember = "FirstName"
        ComboBox1.ValueMember = "ID"

        'Clean up
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub
End Class

----------------

Let me know if you need any more help.

 

[thirteentwenty]

Thank you very much rajhansh...

I'll be playing around with this code to see if I can make heads or tales of it... I have a feeling that I'm in over my head with this one, but you know, this is a place that I kind of like to be...

and by the way, the clean up portion of your code seems like it would be common sense... but it's the first time that I've seen it, is this something that people just take for granted or something that people just don't do anymore?

anyways thanks a million for the assist... I'll post here again if I run into any major issues.

 

[thirteentwenty]

Thank you once again rajhansh...

This is how I ended up doing it, now I'm sure that there are better ways, and this wasn't _exactly_ what I was looking for... but it works, and it works well (read: way better than what I had done before)...

Hopefully this thread can help other folks that are _extremely_ new to VB.NET get started with data access stuff in regards to comboboxs...

It's got some generic comments in it as I cant tell you _exactly_ what each line does, heck i cant even tell you what "Dim" means lol, but I kind of know whats going on...

This was compiled on Visual Studio 2005

What I did was create a Module called gbl (for global) can called the function to open and populate the comboboxes on load. I'll most likely put reference this in a splash screen as it takes a few seconds to load, and I think the load times will get longer as the database grows

Option Strict Off
Option Explicit On
Module gbl
    Dim con As OleDb.OleDbConnection
    Dim query As String
    Dim com As OleDb.OleDbCommand
    Dim da As OleDb.OleDbDataAdapter
    Dim dt As DataTable

    Public Function openDB()
' Open the database
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TEST.mdb"
        con = New OleDb.OleDbConnection(connectionString)

        con.Open()

        'populates Reporters
        com = New OleDb.OleDbCommand("SELECT * FROM Reporters ORDER BY Name", con)
        da = New OleDb.OleDbDataAdapter
        da.SelectCommand = com
        dt = New DataTable
        da.Fill(dt)

        frm_invoices.cmb_inv_reporter.DataSource = dt
        frm_invoices.cmb_inv_reporter.DisplayMember = "Name"
        frm_invoices.cmb_inv_reporter.ValueMember = "ID"

        da.Dispose()
        da = Nothing
        com.Dispose()
        com = Nothing

        'populates Client Information
        com = New OleDb.OleDbCommand("SELECT * FROM Clients ORDER BY clients.[Client Name]", con)
        da = New OleDb.OleDbDataAdapter
        da.SelectCommand = com
        dt = New DataTable
        da.Fill(dt)

        frm_invoices.cmb_inv_client.DataSource = dt
        frm_invoices.cmb_inv_client.DisplayMember = "Client Name"
        frm_invoices.cmb_inv_client.ValueMember = "ID"

        frm_invoices.cmb_inv_cid.DataSource = dt
        frm_invoices.cmb_inv_cid.DisplayMember = "ID"
        frm_invoices.cmb_inv_cid.ValueMember = "ID"

        frm_invoices.cmb_inv_firmID.DataSource = dt
        frm_invoices.cmb_inv_firmID.DisplayMember = "FirmID"
        frm_invoices.cmb_inv_firmID.ValueMember = "ID"

        da.Dispose()
        da = Nothing
        com.Dispose()
        com = Nothing

        'populates Firms
        com = New OleDb.OleDbCommand("SELECT * FROM Firms ORDER BY [Firm Name]", con)
        da = New OleDb.OleDbDataAdapter
        da.SelectCommand = com
        dt = New DataTable
        da.Fill(dt)

        frm_invoices.cmb_inv_firm.DataSource = dt
        frm_invoices.cmb_inv_firm.DisplayMember = "Firm Name"
        frm_invoices.cmb_inv_firm.ValueMember = "ID"

        da.Dispose()
        da = Nothing
        com.Dispose()
        com = Nothing

        Return ""
    End Function
    Public Function DBstate()
        Select Case con.State
            Case 0
                frm_invoices.ToolStripStatusLabel1.Text = "Database Disconnected"
            Case 1
                frm_invoices.ToolStripStatusLabel1.Text = "Database Connected"
        End Select
        Return ""
    End Function
    Public Function checkDB()

        Select Case con.State
            Case 0
                MsgBox("Disconnected")
            Case 1
                MsgBox("Connected")
        End Select
        Return ""

    End Function

    Public Function closeDB()
        con.Dispose()
        con.Close()
        Return ""
    End Function
End Module

notice that I changed the variable names, I was just too lazy to re-type all the stuff that I had done...

** Fueled by crappy coffee and beef jerky I AM THE COPY AND PASTE KING!!!! **

 

[cjard]

Mmm.. I'd have said "take a read of the DW2 link in my signature, section: creating a form to search data"

Then you just make X number of parameterized queries for X number of combos, and call them in succession whenever a user picks one entry

 

[thirteentwenty]

Mmm.. I'd have said "take a read of the DW2 link in my signature, section: creating a form to search data"

Then you just make X number of parameterized queries for X number of combos, and call them in succession whenever a user picks one entry

Oddly enough, I was waiting your response specifically, as I found most of your responses referring to that link, and that is what I wanted to do. However I did take a look at the DW2 link and being such a novice I had opt for a different solution.

I'm still learning so please bare with me. =)

 

[cjard]

Heh, yeah, i do sound like a bit of a broken record with DW2.. but so often I find people following old/out of date tutorials etc..

In this case if you have many combos, I would be tempted to make things a little simpler and jsut have one DataTable/TableAdapter with a Display and Value columns, then the TA can have several related queries:

SELECT Name as Display, ID as Value FROM tblStates
SELECT Name as Display, ID as Value FROM tblProvinces WHERE StateID = @StateID
SELECT Name as Display, ID as Value FROM tblTowns WHERE ProvinceID = @ProvinceID

...

 

[thirteentwenty]

With any luck eventually I'll be at a point where I can get code like that out of my head, but alas, I'll have to rely on some outdated tutorials (yea I found that one out the hard way lol) and the good folks here to guide me through this...

Thanks again!

btw, broken records are sometimes good!

 

[cjard]

Avoid the outdated tutorials. Right now youre writing Bad Code (TM) from an OO point of view because youre basically mashing all your code into one place. A button click event handler is NOT the place to be preparing an SQL statement and running it against a DB.

You'll also be amazed at how much hard work youre making for yourself, and tbh, it isnt even helpful or teaching you good things because (while i'm all for a "Make noobs program in Notepad so they appreciate visual studio" approach) all you end up doing is pick up baaad habits because everyone else does it too.

An arguemnt I usually use to sway people is:
Do you use the forms designer to make your forms and set your properties, or do you code all the layout stuff by hand? You bet you use the forms designer.. So a database access layer designer exists that generates great, modular, fast, working, safe code in seconds.. Why do you let it rest idle, and carry on writing your SQLs by hand, in a bad, slow, unsafe, poor-OO way?
Usually the answer is "because I don't know how to use it"

..and that's what DW2 is for..

In a little over the time it takes you to write the SQL to get your prefs value, i'll have a fully working code that prepares a statement, connects the db, retrieves the value, shuts everything down gracefully and returns me the value in type safe form.. I'll use it in one line too, making my code neater and more modular and reusable. What's to lose? Check it out.. No harm in using a wizard if it does a better job in seconds than most humans can do in a hour or more