[today12]

i need some help trying to put data from a database into a combobox. i've tried different way, still not working.

  Public Sub SaveNames(ByRef SQLStatement As String)
        Dim cmd As MySqlCommand = New MySqlCommand

        With cmd
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()
        End With

        SQLConnection.Close()
        MsgBox("Successfully Added!")
        SQLConnection.Dispose()

    End Sub
    Public Sub Retrieve(ByRef SQLStatement As String)
        Dim cm As MySqlCommand = New MySqlCommand

        With cm
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()
        End With
        SQLConnection.Close()
    End Sub


    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Dim SQLStatement As String = "INSERT INTO users(fName,lName,email) VALUES('" & txtFName.Text & "','" & txtLName.Text & "','" & txtEmail.Text & "')"



        SaveNames(SQLStatement)
    End Sub

    Private Sub BoxRace_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BoxRace.SelectedIndexChanged
        Dim SQLStatement1 As String = " SELECT * from users(fName)"
        
        Retrieve(SQLStatement1)
    End Sub
End Class

[fixo]

The fastest way to use datareader to retrieve values
See if this helps

   Public Sub docombo()
        Dim conn As New SqlConnection("your connection string is here")
        'create the SqlCommand object and set the sql query 
        Dim cmd As New SqlCommand("SELECT * FROM users", conn)'<--or ("SELECT fname FROM users", conn)
        cmd.CommandTimeout = 60 ''<-- optional
        Dim names As New List(Of String)
        Try
            conn.Open()
            'create the SqlDataReader object to connect to our table 
            Dim rd As SqlDataReader = cmd.ExecuteReader(Commandbehavior.CloseConnection)
            While rd.Read()
                names.Add(rd("fname").ToString) '<-- retrieve just "fname" field
            End While
            rd.Close() '<--important
            conn.Close() '<--important

            Me.ComboBox1.Items.Clear()
            Me.ComboBox1.Items.AddRange(names.ToArray)
        Catch ex As System.Exception
            MessageBox.Show("Error" & vbLf & ex.Message & vbLf & "Trace: " & vbLf & ex.StackTrace)
        End Try
    End Sub
    Private Sub btnCombo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCombo.Click
        docombo()
    End Sub

[today12]

thanks alot. i had to change it up a bit to work with my program but it works great. i have multiple comboboxes. how would I make this a function so that i can call from different functions.

[fixo]

Do you mean to populate them all from
the same connection using the same datareader?
Not clearly enough for me

[today12]

sorry, i've got 5 different combobox that will retrieve from different table. to make one function that retrieves like the one you showed me but when i call from different functions, i want it to retrieve from different parts of the database for different combobox.

[fixo]

Sorry, just a hint:
You can do it using inside of one Transaction and create
so many commands/datareaders pairs inside it as you need , just change
appropriate command text for all of them as well as you do change
field names per datareader too

[today12]

i think i get what your saying. let me work on it. and i'll post what i have if i ran into trouble.

[today12]

here is what i have. when i select race and then i select religion both selected items clear out.

Public Sub Retrieve()

        Dim conn1 As New MySqlConnection("connection string")
        Dim conn As New MySqlConnection("connection string")

        Dim cmd As New MySqlCommand("SELECT * FROM RACE", conn)

        Dim names As New List(Of String)
        Dim names1 As New List(Of String)
        Try
            conn.Open()
            Dim rd As MySqlDataReader = cmd.ExecuteReader(Commandbehavior.CloseConnection)

            While rd.Read()
                names.Add(rd("raceType").ToString)

            End While
            rd.Close()
            conn.Close()
            Me.BoxRace.Items.Clear()
            Me.BoxRace.Items.AddRange(names.ToArray)


            conn1.Open()
            Dim cmd1 As New MySqlCommand("SELECT * FROM RELIGION", conn1)
            Dim rb As MySqlDataReader = cmd1.ExecuteReader(Commandbehavior.CloseConnection)

            While rb.Read()
                names1.Add(rb("religionType").ToString)
            End While

            rb.Close()
            conn1.Close()



            Me.boxReligion.Items.Clear()
            Me.boxReligion.Items.AddRange(names1.ToArray)

        Catch ex As Exception
            MessageBox.Show("Error" & vbLf & ex.Message & vbLf & " Trace: " & vbLf & ex.StackTrace)
        End Try

[fixo]

Read docs about Commandbehavior.CloseConnection signature
for many you dou use Commandbehavior.SequentialAccess inside
of the one only connection
Something like, not tested, change fields and etc in there:

 Public Sub popCombos()
        Dim trans As SqlTransaction = Nothing

        Dim conn As SqlConnection = New SqlConnection("blah..")
        conn.Open()
        Try
            trans = conn.BeginTransaction(IsolationLevel.ReadCommitted)
            Dim cmd1 As New SqlCommand(" SELECT field1 from table1", conn)
            cmd1.Transaction = trans
            Dim arr1 As New List(Of String)
            Dim rd1 As SqlDataReader = cmd1.ExecuteReader(Commandbehavior.SequentialAccess)
            While rd1.Read()
                arr1.Add(rd1("field1").ToString)
            End While
            rd1.Close()
            Dim cmd2 As New SqlCommand(" SELECT field2 from table2", conn)
            cmd2.Transaction = trans
            Dim arr2 As New List(Of String)
            Dim rd2 As SqlDataReader = cmd2.ExecuteReader(Commandbehavior.SequentialAccess)
            While rd2.Read()
                arr2.Add(rd2("field2").ToString)
            End While
            rd2.Close()
            Dim cmd3 As New SqlCommand(" SELECT field3 from table3", conn)
            cmd3.Transaction = trans
            Dim arr3 As New List(Of String)
            Dim rd3 As SqlDataReader = cmd3.ExecuteReader(Commandbehavior.SequentialAccess)
            While rd3.Read()
                arr3.Add(rd3("field3").ToString)
            End While
            rd3.Close()
          ''& ETC..
            trans.Commit()
            Me.ComboBox1.Items.Clear()
            Me.ComboBox1.Items.AddRange(arr1.ToArray)
            Me.ComboBox2.Items.Clear()
            Me.ComboBox2.Items.AddRange(arr2.ToArray)
            Me.ComboBox3.Items.Clear()
            Me.ComboBox3.Items.AddRange(arr3.ToArray)
        Catch ex As System.Exception
            If Not trans Is Nothing Then trans.Rollback()
            MessageBox.Show(ex.Message & vbLf & ex.StackTrace)
        Finally
            conn.Close()
        End Try
    End Sub

[today12]

i was about to tell you i found some other way to do it. but you got there before me. and i always figured out why it's clearing out. I reading about it now.

thanks i learned a lot.

[fixo]

Glad you've got learned something new by yourself
Cheers