Question im trying to fill my combo box with my database.. HELP.

[nhonoskywalker]

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

 

[jmcilhinney]

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

 

[Sparrow]

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