VB 2010 ComboBox populate from MySQL Table

[SQLpower]

Hello guys,

I am still encountering a problem regarding my combobox. I am trying to populate it from my MySQL table but I am unable to so far....

    Dim connectionstring As String
    Dim dbCon As MySqlConnection
    Dim strQuery As String = ""
    Dim SQLCmd As MySqlCommand
    Dim DR As MySqlDataReader
    Dim mysqladapter As MySqlDataAdapter
    Dim dt As DataTable    



    Private Sub cmbName_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbName.DropDown
        Try

            'dbCon = New MySqlConnection("Server = localhost; Database=test;uid=root;Pwd=")
            dbCon = New MySqlConnection("SERVER=localhost;DATABASE=test;")

            strQuery = "SELECT Name FROM students;"
            SQLCmd = New MySqlCommand(strQuery, dbCon)
            dbCon.Open()
            DR = SQLCmd.ExecuteReader
            mysqladapter.Fill(dt)
            cmbName.DataSource = dt
            cmbName.ValueMember = "Name"

            DR.Close()
            dbCon.Close()
        Catch ex As Exception
            MsgBox("Failure to communicate!" & vbCrLf & vbCrLf & ex.Message)

        End Try
    End Sub

[Reverend Jim]

Try this

dbCon = New MySqlConnection("SERVER=localhost;DATABASE=test;")

Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand("SELECT Name FROM students", dbCon)

dbCon.Open()
da.Fill(ds)
dbCon.Close()

cmbname.DataSource = ds.Tables(0)
cmbname.DisplayMember = "Name"

or more concisely

Dim ds As New DataSet
Dim da As New SqlDataAdapter("SELECT Name FROM students","SERVER=localhost;DATABASE=test;")

da.Fill(ds)

cmbname.DataSource = ds.Tables(0)
cmbname.DisplayMember = "Name"

[SQLpower]

Doesn't work... :( I just get exception - Failure to communicate!

[SQLpower]

Fill: SelectCommand.Connection property has not been initialized the error message I am getting when I try to use fill...Any suggesitons?

[SQLpower]

Still looking for an answer ^^ The query is being empty and it's not selecting anything but the program is not breaking...

[SQLpower]

Any suggestions? :D

[rbcontra]

        dbCon.Open()

        Dim cmdname As New SqlCommand(("SELECT name FROM students"), dbCon)
        Dim daname As New SqlDataAdapter
        Dim dsname As New Data.DataSet
        daname.SelectCommand = cmdname
        cmdname.ExecuteNonQuery()
        dsname.Clear()
        daname.Fill(dsname, "students")

        dbCon.Close()

        For i As Integer = 0 To dsname.Tables(0).Rows.Count - 1
            cmbName.Items.Add(dsname.Tables(0).Rows(i).Item(0).ToString)
        Next           

[SQLpower]

Still no progress :(

[Dili1234]

You can write this coding at form loading event

        myCommand = New SqlCommand("SELECT * FROM Major ", myConnection)



        Dim da As New SqlDataAdapter
        SQLdr = myCommand.ExecuteReader()
        While SQLdr.Read()
            cmbMajorI.Items.Add(SQLdr("MajorName"))
        End While

[Begginnerdev]

You can fix your problem by simply opening the connection to communitcate.

Use Reverend Jim's Code and add the following line before da.Fill(ds)

da.SelectCommand.Connection.Open()
da.Fill(ds)

[SQLpower]

So basically my login page is working perfectly fine, but If i try to select the drop down list, it just crashes or it's empty....

I have no idea what's happening.

I tried all of your suggestions, guys and nothing. I am just going through the code again, I must've done something incredibly dumb...

[TnTinMN]

Perhaps it has something to do with the fact you are querying the DB on the combobox.Dropdown event and trying to change it's datasource in that same handler.

ComboBox perspective - I dropDown to display my content, but wait, the programmer wants to change my contents while the I'm is trying to display them. I need an asprin!

[SQLpower]

Alright, made me think, although the login page uses the same logic as the one being used on that combobox.

Now i have a class with:

  Public Sub CnStr()
        If CN.State = ConnectionState.Open Then CN.Close()
        ss = "SERVER=localhost;DATABASE=test;"
        CN.ConnectionString = ss
    End Sub


  `Public Sub fillCombo(ByVal cmbName As ComboBox, ByVal cmd As String)
        cmbName.Items.Clear()
        CnStr()
        CN.Open()
        Dim cm As New SqlCommand(cmd, CN)
        Dim rdr As SqlDataReader = cm.ExecuteReader
        While rdr.Read
            cmbName.Items.Add(rdr(0).ToString)
        End While
        rdr.Close()
        CN.Close()
    End Sub`


         Private Sub cmbName_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbName.DropDown
        c.fillCombo(cmbName, "SELECT students.Name from students")

And it just crash.

[TnTinMN]

How is that any different than before? It still changes the combobox items on the Dropdown event. I assume that the list of students does not change during your program run, so fill the items array once in either the FormLoad or FormShown event handler.