[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5536
  • Last Modified:

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
0
chow01
Asked:
chow01
  • 6
  • 4
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now