asked on

VSTO - populate ComboBox with connection string to Access table

Hi

I am trying to populate a ComboBox in a 2010 VSTO project using the VB.net code below.
I used the same sort of code for a DataGridView but I am getting the error
'DataMember' is not a member of 'System.Windows.Forms.ComboBox'.
in the line Me.ComboBox_Supplier.DataMember = "Suppliers_table2"

Sub Fill_ComboBox_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 FROM Suppliers"
            '" & 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_table2")
            connection.Close()
            Me.ComboBox_Supplier.DataSource = ds
            Me.ComboBox_Supplier.DataMember = "Suppliers_table2"

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

    End Sub

Open in new window

Pratima

try this

Me.ComboBox_Supplier.DataSource = ds
Me.ComboBox_Supplier.DisplayMember = "Suppliers_table2"
Me.ComboBox_Supplier.ValueMember = "Suppliers_table2

Sub Fill_ComboBox_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 FROM Suppliers"
            '" & 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_table2")
            connection.Close()
            Me.ComboBox_Supplier.DataSource = ds
            Me.ComboBox_Supplier.DisplayMember = "Suppliers_table2"
 Me.ComboBox_Supplier.ValueMember = "Suppliers_table2
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Open in new window

Nasir Razzaq

Either change this

Me.ComboBox_Supplier.DataSource = ds

to

Me.ComboBox_Supplier.DataSource = ds.Tables(0)

or use a datatable instead of a dataset as you are loading a single table.

Murray Brown

ASKER

Hi
Still not working

Nasir Razzaq

>Still not working
Giving an error or not showing any records?

Add this after Fill

msgbox ds.Tables(0).Rows.Count

and how many rows have been loaded in ds.

Murray Brown

ASKER

Hi. I got 240 so there clearly is data in there

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

thanks. Sorry about late reply