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

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"

Edited 3 Years Ago by Reverend Jim

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

Edited 3 Years Ago by SQLpower

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

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

Edited 3 Years Ago by SQLpower

        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           

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

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)

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...

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!

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.

Edited 3 Years Ago by SQLpower

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.

Comments
Safer that way.
This article has been dead for over six months. Start a new discussion instead.