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
chow01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rpkhareCommented:
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];
0
MezillinuCommented:
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

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

http://bytes.com/forum/thread425291.html
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

chow01Author Commented:
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

0
rpkhareCommented:
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

0
chow01Author Commented:
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
0
rpkhareCommented:
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

0
chow01Author Commented:
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

0
rpkhareCommented:
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

0
chow01Author Commented:
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.
0
rpkhareCommented:
You can pick the selected value using:

string SelectedValue = ComboBox1.Text
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.