• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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

0
Murray Brown
Asked:
Murray Brown
  • 3
  • 3
1 Solution
 
Pratima PharandeCommented:
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

0
 
CodeCruiserCommented:
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.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi
Still not working
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
CodeCruiserCommented:
>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.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. I got 240 so there clearly is data in there
0
 
CodeCruiserCommented:
And are you using below code?

Me.ComboBox_Supplier.DisplayMember = "ColumnName"
Me.ComboBox_Supplier.ValueMember = "ColumnName"
Me.ComboBox_Supplier.DataSource = ds.Tables(0)

?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks. Sorry about late reply
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now