[SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 17346 Views - Last Post: 29 November 2013 - 04:48 AM Rate Topic: -----

#1 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

[SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 15 September 2010 - 05:48 PM

Okay, so I'm trying to use the sql command (SELECT Names FROM Persons;), and wanting to add this to a combobox.

But after some research, I noticed it wasn't so easy as I thought it was. I've got it working using a DataGridViewer, but I want it have it in a combobox.

My code so far(with DataGridViewer)

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SQL = "SELECT Navn FROM havasit;"

        Try
            myCommand.Connection = myConnection
            myCommand.CommandText = SQL

            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(myData)

            DataGridView1.DataSource = myData
            DataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

        Catch myerror As MySqlException
            MsgBox("Error: " & myerror.Message)
        End Try
    End Sub


I read that the only way to do this would be with a adapter.

But i cant find any research that I can understand, which can help me, to convert this combobox items.

Any help at all whould be very appreciated. :)

This post has been edited by Hypermx: 16 September 2010 - 07:16 AM


Is This A Good Question/Topic? 0
  • +

Replies To: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 16 September 2010 - 05:58 AM

Here's an example of populating a combobox. This is set up for MS SQL but you should be able to adjust for your code fairly easily.

        Dim con As New SqlConnection
        Dim constr As String = "your connection string goes here"
        Try
            con.ConnectionString = constr
            con.Open()
        Catch ex As SqlException
            MsgBox(ex.Message)
        End Try

        Dim sStmt As String = "SELECT EmpID, EmpName FROM Employee"
        Dim cmd As New SqlCommand(sStmt, con)

        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As New DataTable("Employee")
        da.Fill(dt)

        If dt.Rows.Count > 0 Then
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "EmpName" 'What is displayed
            ComboBox1.ValueMember = "EmpID" 'The ID of the row
        End If


Of course you don't have to set the ValueMember but if you wanted to set an ID field you will have to include that in your Query

The ValueMember, if used, can be retrieved using Combobox1.SelectedValue

This post has been edited by CharlieMay: 16 September 2010 - 05:59 AM

Was This Post Helpful? 0
  • +
  • -

#3 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 16 September 2010 - 07:13 AM

Thanks for the answer CharlieMay.

My problem is solved, I didn't use your code, But i will next time.

I solved it by adding all the SQL data into a DataGridView, and then using foreach to get all the names:

        SQL = "SELECT Navn FROM kunder;"

        Try
            myCommand.Connection = myConnection
            myCommand.CommandText = SQL

            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(myData)

            DataGridView1.DataSource = myData
            DataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

            For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                    kundeCombo.Items.Add(row.Cells(0).Value.ToString)
                End If
            Next
        Catch myerror As MySqlException
            MsgBox("Error: " & myerror.Message)
        End Try

Was This Post Helpful? 0
  • +
  • -

#4 TMoose2496  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 29-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 05:47 AM

View PostCharlieMay, on 16 September 2010 - 05:58 AM, said:

Here's an example of populating a combobox. This is set up for MS SQL but you should be able to adjust for your code fairly easily.

        Dim con As New SqlConnection
        Dim constr As String = "your connection string goes here"
        Try
            con.ConnectionString = constr
            con.Open()
        Catch ex As SqlException
            MsgBox(ex.Message)
        End Try

        Dim sStmt As String = "SELECT EmpID, EmpName FROM Employee"
        Dim cmd As New SqlCommand(sStmt, con)

        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As New DataTable("Employee")
        da.Fill(dt)

        If dt.Rows.Count > 0 Then
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "EmpName" 'What is displayed
            ComboBox1.ValueMember = "EmpID" 'The ID of the row
        End If


Of course you don't have to set the ValueMember but if you wanted to set an ID field you will have to include that in your Query

The ValueMember, if used, can be retrieved using Combobox1.SelectedValue

I used the same process as you to load the data into comboboxes in visual basic. Is there a way to have the combovox sort multiple inserts as one in a combo box? For example I am trying to create an inventory program where we organize drills, endmills, taps, etc. When I drop the combobox down is there a way to prevent it from listing drills 100 times?
Was This Post Helpful? 0
  • +
  • -

#5 Linesofcode  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 62
  • Joined: 23-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 06:15 AM

If you are going to populate multiple comboboxs, you should code a function to do it more easily.
Example:
public void fill_combobox(string query, ComboBox cb, SqlConnection cn)
{
	SqlCommand command = new SqlCommand(query, cn);
	if (cn.State == ConnectionState.Closed) { cn.Open(); }
	SqlDataReader dR = command.ExecuteReader();
	while (dR.Read())
	{
		if (dR[0] != null)
		{
			if (!cb.Items.Contains(dR[0].ToString())) // Preventing duplicated items
			{
				cb.Items.Add(dR[0].ToString());
			}
		}
	}
}
// To use it:
fill_combobox("SELECT name " +
              "FROM employees", comboBox1, conn);


This post has been edited by Linesofcode: 29 May 2013 - 06:15 AM

Was This Post Helpful? 0
  • +
  • -

#6 TMoose2496  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 29-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 09:08 AM

View PostLinesofcode, on 29 May 2013 - 06:15 AM, said:

If you are going to populate multiple comboboxs, you should code a function to do it more easily.
Example:
public void fill_combobox(string query, ComboBox cb, SqlConnection cn)
{
	SqlCommand command = new SqlCommand(query, cn);
	if (cn.State == ConnectionState.Closed) { cn.Open(); }
	SqlDataReader dR = command.ExecuteReader();
	while (dR.Read())
	{
		if (dR[0] != null)
		{
			if (!cb.Items.Contains(dR[0].ToString())) // Preventing duplicated items
			{
				cb.Items.Add(dR[0].ToString());
			}
		}
	}
}
// To use it:
fill_combobox("SELECT name " +
              "FROM employees", comboBox1, conn);


Sorry. I forgot to add I'm programming in visual basic 2010 express so this is not helpful at all. Not to mention I'm kind of a noob at this.
Was This Post Helpful? 0
  • +
  • -

#7 Linesofcode  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 62
  • Joined: 23-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 09:14 AM

View PostTMoose2496, on 29 May 2013 - 09:08 AM, said:

Sorry. I forgot to add I'm programming in visual basic 2010 express so this is not helpful at all. Not to mention I'm kind of a noob at this.

You don't need to sorry. It was my fault. I forgot to translate it to Visual Basic, it is in C#.
Public Sub fill_combobox(query As String, cb As ComboBox, cn As SqlConnection)
	Dim command As New SqlCommand(query, cn)
	If cn.State = ConnectionState.Closed Then
		cn.Open()
	End If
	Dim dR As SqlDataReader = command.ExecuteReader()
	While dR.Read()
		If dR(0) IsNot Nothing Then
			If Not cb.Items.Contains(dR(0).ToString()) Then
				' Preventing duplicated items
				cb.Items.Add(dR(0).ToString())
			End If
		End If
	End While
End Sub
' You can call the method then:
fill_combobox("SELECT name FROM employees", comboBox1, conn)

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3490
  • View blog
  • Posts: 11,902
  • Joined: 12-December 12

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 09:26 AM

@Linesofcode If you are populating the combobox from scratch then it is better to only retrieve distinct items:

"SELECT DISTINCT name FROM employees"

Was This Post Helpful? 0
  • +
  • -

#9 Linesofcode  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 62
  • Joined: 23-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 09:37 AM

Yep, you're right. The DISTINCT function from SQL would work too..if your query is not a big deal, like the example.
Was This Post Helpful? 0
  • +
  • -

#10 TMoose2496  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 29-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 29 May 2013 - 11:22 AM

View PostLinesofcode, on 29 May 2013 - 09:14 AM, said:

View PostTMoose2496, on 29 May 2013 - 09:08 AM, said:

Sorry. I forgot to add I'm programming in visual basic 2010 express so this is not helpful at all. Not to mention I'm kind of a noob at this.

You don't need to sorry. It was my fault. I forgot to translate it to Visual Basic, it is in C#.
Public Sub fill_combobox(query As String, cb As ComboBox, cn As SqlConnection)
	Dim command As New SqlCommand(query, cn)
	If cn.State = ConnectionState.Closed Then
		cn.Open()
	End If
	Dim dR As SqlDataReader = command.ExecuteReader()
	While dR.Read()
		If dR(0) IsNot Nothing Then
			If Not cb.Items.Contains(dR(0).ToString()) Then
				' Preventing duplicated items
				cb.Items.Add(dR(0).ToString())
			End If
		End If
	End While
End Sub
' You can call the method then:
fill_combobox("SELECT name FROM employees", comboBox1, conn)

Sorry I got everything but the last line you have in that code and I don't understand where I'm supposed to put that code at.
Was This Post Helpful? 0
  • +
  • -

#11 Linesofcode  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 62
  • Joined: 23-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 30 May 2013 - 01:44 AM

You can put the code wherever you want. Mainly, it's used in the Load() event of the form, so when it initializes, automatically fills the combobox.
Was This Post Helpful? 0
  • +
  • -

#12 TMoose2496  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 29-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 30 May 2013 - 09:18 AM

Ok so I'm starting to understand and I'm sorry if I'm catching on slow but I do have one more question. That last line of code... the
 fill_combobox("SELECT name FROM employees", combobox1, conn) 
When I enter the conn at the end it wants to enter either connstr or conn:=... I don't know if I'm doing something wrong or what but if you want I can send you a full copy of my code for this form so you can revise it?
Was This Post Helpful? 0
  • +
  • -

#13 Linesofcode  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 62
  • Joined: 23-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 30 May 2013 - 09:22 AM

The "conn" it's a SqlConnection variable. According to your first post, you wrote this:
myCommand.Connection = myConnection

So, you'll need to change to:
fill_combobox("SELECT name FROM employees", combobox1, myConnection)

Was This Post Helpful? 0
  • +
  • -

#14 TMoose2496  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 29-May 13

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 30 May 2013 - 10:04 AM

View PostCharlieMay, on 16 September 2010 - 05:58 AM, said:

Here's an example of populating a combobox. This is set up for MS SQL but you should be able to adjust for your code fairly easily.

        Dim con As New SqlConnection
        Dim constr As String = "your connection string goes here"
        Try
            con.ConnectionString = constr
            con.Open()
        Catch ex As SqlException
            MsgBox(ex.Message)
        End Try

        Dim sStmt As String = "SELECT EmpID, EmpName FROM Employee"
        Dim cmd As New SqlCommand(sStmt, con)

        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As New DataTable("Employee")
        da.Fill(dt)

        If dt.Rows.Count > 0 Then
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "EmpName" 'What is displayed
            ComboBox1.ValueMember = "EmpID" 'The ID of the row
        End If


Of course you don't have to set the ValueMember but if you wanted to set an ID field you will have to include that in your Query

The ValueMember, if used, can be retrieved using Combobox1.SelectedValue

No. I was only using this to fill my combo boxes. If I mislead you to believe I was using the same code as the original guy to post here I apologize. This worked but it loaded the comboboxes to where if I have more than one item with the same name in my mysql database it adds both in there. For example if I have two drills in my mysql database, the first combobox labeled tool type would bring up drills twice. I want it to bring it up once but still be able to select different diameters of drills.
Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: [SOLVED]MySQL (SELECT .. FROM ..;) to ComboBox

Posted 30 May 2013 - 10:13 AM

Did this suggestion not work?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2