Populate combobox with a class - data from a sql query

Question

Iam frustrated to accomplish a simply population of a combobox, below I have added one new item to the combobox everything seems to be fine.

Question 1 : But how could I get there the information's from the sql query, without having to add it all manually. [ I suppose by simply adding Items.Add line to the while loop ], but here is another thing - The start data is a database record previewer, So it is Simple Name Simple Surname [/] with a dropdown menu with all customers,

Question 2. The data I get from the mysql result is id,name,surname how to point it as the current displayed name/surname and for later purposes - like a update get the selected id of another customer from the dropdown? I don't need the insert command or code just need the information how can I get the id from a selection. If something is unclear don't hesitate to ask.

'Select the first item ( the selection would be a ID of the customer which isn't the index at all)
ComboBoxEdit.SelectedIndex = 0

Form

Dim properties As DevExpress.XtraEditors.Repository.RepositoryItemComboBox = _
            ComboBoxEdit.Properties
properties.Items.Add(New Customers(1, "Ta", "t").ToString)
'Select the first item ( the selection would be a ID of the customer which isn't the index at all)
ComboBoxEdit.SelectedIndex = 0

Getting customers into Class Customers ( I guess its that way I need to do it )

Public Function init_customers()
        ' Create a list of strings.
        Dim sql As String
        Dim myReader As MySqlDataReader

        con.Open()
        sql = "select * from customers"
        'bind the connection and query
        With cmd
            .Connection = con
            .CommandText = sql
        End With
        myReader = cmd.ExecuteReader()
        While myReader.Read()
            list.Add(New Customers(myReader.GetInt64(0), myReader.GetString(1), myReader.GetString(2)))
        End While
        con.Close()
        'Return list
    End Function

The class customers

Public Class Customers

    Public Sub New(ByVal id As Integer, ByVal name As String, ByVal surname As String)
        Me.ID = id
        Me.Imie = name
        Me.Nazwisko = surname

    End Sub
#Region "Get/Set"
    Public Property ID() As Integer
        Get
            Return Me._id
        End Get
        Set(ByVal value As Integer)
            Me._id = value
        End Set
    End Property
    Public Property Imie() As String
        Get
            Return Me._imie
        End Get
        Set(ByVal value As String)
            Me._imie = value
        End Set
    End Property
    Public Property Nazwisko() As String
        Get
            Return Me._nazwisko
        End Get
        Set(ByVal value As String)
            Me._nazwisko = value
        End Set
    End Property
    Public ReadOnly Property Surname() As Decimal
        Get
            Return Me._nazwisko
        End Get
    End Property
    Public Overrides Function ToString() As String
        Return _imie + " " + _nazwisko
    End Function
#End Region
    Private _id As Integer
    Private _imie As String
    Private _nazwisko As String

End Class

=========== Edit 2 =====================

Ok my dropdown is populated

As I said this is a record preview form so how can I get now the default selection of the combobox. The thing is I pass there a string

Form1.GridView1.GetRowCellValue(Form1.GridView1.FocusedRowHandle, "Wystawione_na").ToString()

This code returns me SimpleName SimpleSurname - as a one string Same method is applied to combobox display.

How can I get now the Id of the item, it has to somehow compared and returning a id so it could be set cmbx.SelectedIndex = 0 as the id of customer selection

Answer 1

I take a simpler route, not sure if it's the most efficient though:

Dim Conn As New SqlConnection
Conn.ConnectionString = sYourConnectionString
Conn.Open()

Dim da As New SqlDataAdapter("select * from customers", Conn)
Dim ds As New DataSet
da.Fill(ds, sSql)

cmbxCustomers.DataSource = ds.Tables(0)
cmbxCustomers.ValueMember = "ID" 'or whatever column you want

Of course, I normally use a wrapper class to do almost all of the above code, but the last two lines apply to part of your question.

As far as retrieving that data later based on the ID selected: well you can simply use a DataSet (or DataTable, my preference) class member variable so the data is stored the from the initial load and iterate through the table looking for the row that matches the ID you're wanting information from.