vb.net setting data source to combobox

Question

i want to set a data source to my combobox when i run there s no error but it keeps showing zeros in the combobox

Dim cnx As New MySqlConnection("datasource=localhost;database=bdgeststock;userid=root;password=")
        Dim cmd As MySqlCommand = cnx.CreateCommand
        Dim da As MySqlDataAdapter
        Dim ds As New DataSet
        If ConnectionState.Open Then
            cnx.Close()
        End If
        cnx.Open()
        cmd.CommandText = "SELECT idf,(prenom + ' ' + nom) AS NAME FROM fournisseur "
        da = New MySqlDataAdapter(cmd)
        cnx.Close()
        da.Fill(ds)
        da.Dispose()
        ComboBox1.DataSource = ds.Tables(0)
        ComboBox1.ValueMember = "idf"
        ComboBox1.DisplayMember = "NAME"

Answer 1

I think the problem is in your sql, and mysql is performing some sort of numeric addition on prenom plus nom and producing 0

Try

CONCAT(prenom, ' ', nom) as name

In your sql instead. I prefer using concat in most RDBMS for concatenating strings because is is more consistent with its behaviour on NULLs - in sqlserver, using the concat operator of plus on something like 'a' + null results in NULL but in oracle 'a' || null is a - in both the CONCAT behaviour is consistent

Here's a full code with all my recommendations:

Dim cnstr = "datasource=localhost;database=bdgeststock;userid=root;password="
Dim cmd = "SELECT idf, CONCAT(prenom, ' ', nom) AS nom FROM fournisseur "
Using da As New MySqlDataAdapter(cmd, cnstr)
    Dim dt As New DataTable
    da.Fill(dt)
    ComboBox1.DataSource = dt
    ComboBox1.ValueMember = "idf"
    ComboBox1.DisplayMember = "nom"
End Using

Tips:

• you don't need to mess around with the connection: dataadapter will create/open/close it for you
• use a datatable not a dataset
• use Using
• use the constructor of MySqlDataAdapter that takes a connectionstring and a command text- shorter and nearer in this case. I only use the constructor that takes a DbConnection if I'm manually enrolling multiple commands in a transaction etc