asked on

How to populate VB.net ComboBox from MySQL database?

Hi,
I am currently working on a VB.net application which uses MySQL as the database.

For ALL 3 attachments, how to enable the ComboBox to reflect only the values from the Project-ID field in MySQL database and after selecting, I need to generate the Crystal Report for user-?

For the 2nd attachment, after choosing the Project No. from the drop-down list, I have to select which status I want, and reflected only the selected status documents in crystal report. How to link that?

For the 3rd attachment, after selecting the project no. from the ComboBox, the documents belonging to that project no. have to be listed in the KM Document No. listbox for user selection.

Please advise. Thank you.

ReportProjectNo.JPG
ReportStatus.JPG
UpdateDocStatus.JPG

rpkhare

Follow these steps:

(1) Write SQL Query
(2) Fill a DataSet with the above query.
(3) Set the ComboBox DataSource property as:

ComboBox1.DataSource = DataSet.Tables[0];

Mezillinu

Follow the following code below, you should cope...

Fill in the specified details with your own, for example where i wrote to enter your connection string, enter it there and so on...

Then in the end, specify which row to show in the combo box.

any more help, just post...

cheers

     Dim connectionString As New SqlClient.SqlConnection("Enter your connection string here")
        Dim connAdapter As New SqlClient.SqlDataAdapter("enter your query here", connectionString)
        Dim dsToBeFilled As New DataSet
        connAdapter.Fill(dsToBeFilled)
        Dim DDL As New ComboBox
 
 
        For Each row As DataRow In dsToBeFilled.Tables(0).Rows
            DDL.Items.Add(row.Item("Column To Be Viewed in drop down list").ToString())
        Next

Open in new window

rpkhare

Filling combo box using a DataReader is faster. See this link:

http://bytes.com/forum/thread425291.html

chow01

ASKER

Hi,
Below is the code I used, however no values are reflected in the combobox.
Please help.

Imports MySql.Data.MySqlClient
Imports System.Data
 
Public Class ReportProjectNo
 
    Dim conn As New MySqlConnection
    Dim myCommand As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter
    Dim dsToBeFilled As New DataSet
    Dim SQL As String
    Dim cmbbox As New ComboBox
 
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
 
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=root; password=3295; database=dms_reports; allow zero datetime=no"
 
        SQL = "SELECT * FROM dms_doc"
 
        Try
            conn.Open()
            myCommand.Connection = conn
            myCommand.CommandText = SQL
 
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(dsToBeFilled)
 
            For Each row As DataRow In dsToBeFilled.Tables(0).Rows
                cmbbox.Items.Add(row.Item("Project_id").ToString())
            Next
 
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
 
        End Try
 
    End Sub
End Class

Open in new window

rpkhare

You are writing in wrong place: ComboBox1_SelectedIndexChanged. See the correct style below. Call this routine on Form_Load().

Private Sub FillComboBox()
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=root; password=3295; database=dms_reports; allow zero datetime=no"
 
        SQL = "SELECT * FROM dms_doc"
 
        Try
            conn.Open()
            myCommand.Connection = conn
            myCommand.CommandText = SQL
 
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(dsToBeFilled)
 
            For Each row As DataRow In dsToBeFilled.Tables(0).Rows
                cmbbox.Items.Add(row.Item("Project_id").ToString())
            Next
 
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
 
        End Try
End Sub

Open in new window

chow01

ASKER

I manage to retrieve the values in the Combobox.
however how do i prevent repetition of numbers for Project_ID?
Each Project_ID [project] consists of several KM_id [documents].

MySQL-database.JPG

rpkhare

Use:

ComboBox.Items.Clear()

before calling FillComboBox().

When you are filling ComboBox with ProjectID, use this query in the FillComboBox routine:

Select Distinct ProjectID from TableName

chow01

ASKER

the query works.
is there a need to create the private sub FillComboBox()?
If so, where do i put it in my code. I'm quite confused about this.

Now that the Project_ID only appear once in the drop-down list, selecting say "111", will it only appear one KM_id or both which has the same Project_ID when i generate my report?

Imports MySql.Data.MySqlClient
Imports System.Data
 
Public Class ReportProjectNo
 
    Dim conn As New MySqlConnection
    Dim myCommand As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter
    Dim dsToBeFilled As New DataSet
    Dim SQL As String
 
 
    Private Sub ReportProjectNo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=root; password=3295; database=dms_reports; allow zero datetime=no"
 
        SQL = "SELECT Distinct Project_ID FROM dms_doc"
 
        Try
            conn.Open()
            myCommand.Connection = conn
            myCommand.CommandText = SQL
 
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(dsToBeFilled)
 
            For Each row As DataRow In dsToBeFilled.Tables(0).Rows
                ComboBox1.Items.Add(row.Item("Project_id").ToString())
            Next
 
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
 
        End Try
 
    End Sub
End Class

Open in new window

rpkhare

Below I give you my C# code on how to create a FillComboBox routine in such a way that it can fill any ComboBox with your query. Just pass the Query and ComboBox Name as parameters.

However, in your above code you need to call like this:

Private Sub ReportProjectNo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

FillComboBox();

End Sub

        public static void FillCombo(string query, System.Windows.Forms.ComboBox cmb)
        {
            CN.Open;
            MySqlCommand cmd = new MySqlCommand(query, CN);
            MySqlDataReader dr;
 
            try
            {
                cmb.Items.Clear();
                dr = cmd.ExecuteReader();
                while (dr.Read() == true)
                {
                    cmb.Items.Add(dr.GetValue(0));
                }
                dr.Close();
            }
            catch { }
            finally
            {
                cmd.Dispose();
                CN.Close();
            }            
        }

Open in new window

chow01

ASKER

Got it!
It works =)

One more question, how do I actually link the selection which I made from the ComboBox to the "Get Report" button? that is where I will be generating a crystal report showing all the documents belonging to the Project_ID selected.

ASKER CERTIFIED SOLUTION

rpkhare

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial