Thread: im trying to fill my combo box with my database.. HELP.

I searched google and youtube for tutorials they made with their codes but mine NOT..
Please tell me hot to do it with my code or tell me what is wrong..
it is the Imports MySql.Data.MySqlClient ? Because I didnt saw this code from their tutorials.


Imports MySql.Data.MySqlClient

Public class main

Private Sub cboemprecDept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboemprecDept.Click
cboDept_load()
End Sub



Private Sub cboDept_load()
Dim strsql As String
Dim sqlCommand As New MySqlCommand


If sConnection.State = ConnectionState.Closed Then
sConnection.ConnectionString = "SERVER = localhost; USERID = root; PASSWORD = 1111111; DATABASE = ftenn;"
sConnection.Open()
End If
Dim reader As MySqlDataReader = sqlCommand.ExecuteReader
strsql = "SELECT * FROM dept_tab"
With sqlCommand
.CommandText = strsql
.Connection = sConnection
.ExecuteNonQuery()
End With



While reader.Read
With cboemprecDept.Items.Add(reader("dept_id"))
End With
End While

sConnection.Close()
End Sub
End Class

First things first, you don't call ExecuteNonQuery to retrieve data. "non-query" means "not a SELECT statement". You ARE executing a query. Call ExecuteReader to create a data reader. Create a DataTable and call its Load method to load the data from the data reader into the DataTable. You can then bind that DataTable to the ComboBox.

Using connection As New MySqlConnection("connection string here"),

      command As New MySqlCommand("SQL query here", connection)

    connection.Open()



    Using reader = command.ExecuteReader()

        Dim table As New DataTable



        table.Load(reader)



        With myComboBox

            .DisplayMember = "Name"

            .ValueMember = "ID"

            .DataSource = table

        End With

    End Using

End Using

Module SqlFunctions



Public Function filldatatable(ByVal sql As String) As DataTable

        Cursor.Current = Cursors.WaitCursor

        'Mysql Dimms

        Dim cs As String

        cs = "Your Connection String"

        Dim con As New MySqlConnection(cs)

        Dim ds As New DataTable

        Dim da As New MySqlDataAdapter









        Try

            Try





                con.Open()

                da = New MySqlDataAdapter(sql, con)

                da.Fill(ds)

                filldatatable = ds

                con.Close()

                Cursor.Current = Cursors.Default

                Return filldatatable

            Catch ex As Exception

                MessageBox.Show("Unable to communicate with sql server", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                MsgBox(ex.ToString)

            End Try

        Catch myerror As MySqlException

            MessageBox.Show("Error Updating Databse : " & myerror.Message)





        End Try

        Cursor.Current = Cursors.Default





    End Function

End Module

Then in your form you add a data set then you fill the data se as so:

        Dim Sql As String





        Sql = "your query"

        Dim nametbl As DataTable = sqlFunctions.filldatatable(Sql)

        For Each row As DataRow In nametbl.Rows()

            Dim newrow As DataRow

            newrow = DataSet.Tables().NewRow

            newrow.Item(0) = row.Item(0)

            DataSet.Tables().Rows.Add(newrow)

        Next

        cbServers.DataSource = nametbl

        cbServers.ValueMember = "name"

        cbServers.SelectedIndex = 0

cbServers is the combobox