CODEWITHSUNDEEP
databasevb.netcombobox
user1585769
user1585769

Reputation: 23

VB.NET 2010 - Populating a combobox from multiple columns in an access database

I have recently had a question answered on how to fill a combobox from an access database, however my latest problem is a little trickier for me.

I need to populate my next combobox from TWO columns from a table, ie:

Reasoncode - Reason

so i want my combo box to look as follows -

A - Accounts

S - Sales

ST - System Tidy

However the reason code and the reason description are stored in two seperate columns in my database and they cannot be joined together. Is this possible in VB.Net, to pull multiple columns into one combobox?

My current code to populate the box (courtesy of LarsTech)

Protected Overrides Sub OnLoad(e As EventArgs)
    Dim ds1 As New DataSet()
    Dim ConnnectionString1 As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Admin\Desktop\CancellationsAndMovements\CancellationsandMovements\cancmov.mdb"

    Dim db1 As String = "SELECT reasoncode FROM Reason"

    Using cn1 As New OleDbConnection(ConnnectionString1)
        Using da As New OleDbDataAdapter(db1, cn1)
            da.Fill(ds1, "reasoncode")
        End Using
    End Using

    With drpMoveReason
        .DisplayMember = "reasoncode"
        .DataSource = ds1.Tables("reasoncode")
        .SelectedIndex = 0
    End With

    MyBase.OnLoad(e)

End Sub

The extra column i want to pull is called "reasondesc"

If it is possible to fill this way any help would be greatly appreciated, also appreciated would be aby help in how i would write back ONLY the reason code when i write back to a different database.

Thank you in advance.

Upvotes: 1

Views: 13385

Answers (2)

Mike_OBrien
Mike_OBrien

Reputation: 1423

You can do this either when you get the information from the database as David Osborne suggested or you can create a temporary datatable that would have an ID column(your reasoncode) and the concatenated string as the display column(name it description or whatever). Once you have the temporary tables columns all set up you'd just need to cycle through all the records you are getting from the database and manually add the rows to the table.

In either case you would want to set the .ValueMember on the combo box to the reasoncode column in the table and the .DisplayMember to the concatenated reasoncode and description. Then when you want to save back to the database just grab the .SelectedValue from the combo box and it will be the currently selected reasoncode.

Upvotes: 1

David Osborne
David Osborne

Reputation: 6781

A few options here. Why not try concatenating the two in the query: select reasoncode & "-" & reasondesc from...

You could develop it further by: Select reasoncode, reasoncode + " - " + reasondesc as description from...

And then use reasoncode as the valuemember, enabling the write back to the table, and the description as the displaymember.

Upvotes: 0

Related Questions