VSTO - fill a ComboBox with a connection string

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

In VB.net I want to populate a ComboBox on a Windows form from a connection string.
The below code works for a DataGridView but not for a ComboBox
Sub FillDGV_Sales()
        Try


            Dim MYDOC_DIR As String = Environ("userprofile") & "\my documents"
            Dim oFullPath As String = MYDOC_DIR & "\MagicBox.accdb"
            '"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=App_Data\GC.accdb;"
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & oFullPath & ";"
            Dim sSQL As String
            'sSQL = "SELECT Suppliers.SupplierName, Sum(DLookUp('Amount','Transactions','[Transaction Type] = " & SQLConvert("'Invoice'") & "')) AS Invoiced, Sum(DLookUp('Amount','Transactions',[Transaction Type]' = " & SQLConvert("'Payment'") & "')) AS Paid, [Invoiced]-[Paid] AS Balance " & _
            '"FROM Suppliers INNER JOIN Transactions ON Suppliers.SupplierID = Transactions.SupplierID" & _
            '"GROUP BY Suppliers.SupplierName"
            sSQL = "SELECT Suppliers.SupplierName, [Invoiced]-[Paid] AS Balance, Sum(DLookUp('Amount','Transactions','[Transaction Type] = ''Invoice''')) AS Invoiced, Sum(DLookUp('Amount','Transactions','[Transaction Type] = ''Payment''')) AS Paid " & _
            "FROM Suppliers INNER JOIN Transactions ON Suppliers.SupplierID = Transactions.SupplierID " & _
            "GROUP BY Suppliers.SupplierName"


            '" & SQLConvert(sSchool) & "'"
            Dim connection As New OleDbConnection(connectionString)
            Dim dataadapter As New OleDbDataAdapter(sSQL, connection)
            Dim ds As New DataSet()
            connection.Open()
            dataadapter.Fill(ds, "Suppliers_table")
            connection.Close()
            DataGridView_Supplier.DataSource = ds
            DataGridView_Supplier.DataMember = "Suppliers_table"

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
What code did you try? Did you try changing following

            DataGridView_Supplier.DataSource = ds
            DataGridView_Supplier.DataMember = "Suppliers_table"

to


Combobox1.DataSource = ds.Tables(0)
Combobox1.DisplayMember = "ColumnName"
Combobox1.ValueMember = "ColumnName"
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Did that and got the message, cannot bind to the new display member. Parameter name: newDisplayMember
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Sorry. Hadn't changed column name to SupplierName. It worked!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Great.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial