Loading combobox from database in C#

Question

I am creating an application, where I can add a customer's first name, last name, email, the date, service type (pc repair), the technician PC brand, pc type, type of OS, and the problem with the computer. I am able to insert data into the MySQL database using phpMyAdmin.

However, I a stuck on this part. I am trying to view the service order that was just created. I would like to load the combobox by the last name of the customer, and once I click on the customer's name, it populates all the fields that were mentioned above and the service number that it was inserted into the database. I am having issues loading the combobox and texfields.

Any ideas are appreciated! If a combobox is a bad idea and there is a better way, please let me know! I tried this code, but SQLDataAdapter is not working for me. I somehow can't find an example that I can relate too.

private void cbViewServices_SelectedIndexChanged(object sender, EventArgs e)
{
  if (cbViewServices.SelectedIndex >- 1)
  {
    string lastName = cbViewServices.SelectedValue.ToString();                
    MySqlConnection conn = new MySqlConnection("server=localhost;uid=******;password=**********;database=dboserviceinfo;");
    conn.Open();

    SqlDataAdapter da = new SqlDataAdapter("select distinct LastName from tserviceinfo where LastName='" + lastName + "'", conn);

    DataSet ds = new DataSet();
    da.Fill(ds); conn.Close();
  }
}

Answer 1

bind your dataset in ComboBox DataSource

this.comboBox1.DataSource = ds;
this.comboBox1.DisplayMember = "LastName";
this.comboBox1.ValueMember = "Id";
this.comboBox1.SelectedIndex = -1;
this.comboBox1.AutoCompleteMode = AutoCompleteMode.Append;
this.comboBox1.AutoCompleteSource = AutoCompleteSource.ListItems;

Answer 2

We can also use while loop. When completing the database connection after the SQLDatareader we can use while loop.

"userRead " is SQLData reader

  while (userRead.Read())
   {
        cboxReportNo.Items.Add(userRead[1].ToString());
   }

Answer 3

//USING
        using System;
        using System.Drawing;
        using System.Windows.Forms;
        using System.Data.SqlClient;
        using System.Data;

namespace YourNamespace
{
//Initialization
        string connetionString = null;
        SqlConnection cnn;
        SqlCommand cmdDataBase;
        SqlDataReader reader;
        DataTable dt;

public frmName()
        {
            //
            // The InitializeComponent() call is required for Windows Forms designer support.
            //
            InitializeComponent();
            //
            // TODO: Add constructor code after the InitializeComponent() call.
            //
            FillComboNameOfCombo();
        }

void FillcmbNameOfCombo()
{
    string sqlQuery = "SELECT * FROM DATABASENAME.[dbo].[TABLENAME];";
            connetionString = "Data Source=YourPathToServer;Initial Catalog=DATABASE_NAME;User ID=id;Password=pass";
                    cnn = new SqlConnection(connetionString); 
                    cmdDataBase = new SqlCommand(sqlQuery, cnn);
            try { 
                    cnn.Open(); 

                    reader = cmdDataBase.ExecuteReader();
                    dt = new DataTable();

                    dt.Columns.Add("ID", typeof(string));
                    dt.Columns.Add("COLUMN_NAME", typeof(string));
                    dt.Load(reader);
                    cnn.Close();

                    cmbGender.DataSource = dt;
                    cmbGender.ValueMember = "ID";
                    cmbGender.DisplayMember = "COLUMN_NAME";

                    dt = null;
                    cnn = null;
                    cmdDataBase = null;
                    connetionString = null;
                    reader = null;
                }
            catch (Exception ex) { 
                    MessageBox.Show("Can not open connection ! " + ex.ToString());
                }
}
}

Answer 4

I do not recommend using the 'Lastname' as a parameter to load your details since that field most likely isn't unique. Unless that is the case in your program.

This sample does the following:

1. Load customer ID (or lastname in your case) to a combobox.
2. Handle the combobox's change event and pass it as a parameter to a method that will use that to load the details.
3. Load the customer details using the passed parameter.

A couple of guidelines:

1. Enclose disposable objects in a 'using' statement so it will be disposed properly.
2. Do not use string concatenation to create your SQL statements. Use SQL parameters instead, that way you'll avoid SQL injection and make your code clearer.

3. Take a look at MySQL .NET connector provider documentation for best practices.

   //Load customer ID to a combobox
   private void LoadCustomersId()
   {
       var connectionString = "connection string goes here";
       using (var connection = new MySqlConnection(connectionString))
       {
           connection.Open();
           var query = "SELECT Id FROM Customers";
           using (var command = new MySqlCommand(query, connection))
           {
               using (var reader = command.ExecuteReader())
               {
                   //Iterate through the rows and add it to the combobox's items
                   while (reader.Read())
                   {
                       CustomerIdComboBox.Items.Add(reader.GetString("Id"));    
                   }
               }    
           }
       }
   }
   
   //Load customer details using the ID
   private void LoadCustomerDetailsById(int id)
   {
       var connectionString = "connection string goes here";
       using (var connection = new MySqlConnection(connectionString))
       {
           connection.Open();
           var query = "SELECT Id, Firstname, Lastname FROM Customer WHERE Id = @customerId";
           using (var command = new MySqlCommand(query, connection))
           {
               //Always use SQL parameters to avoid SQL injection and it automatically escapes characters
               command.Parameters.AddWithValue("@customerId", id);
               using (var reader = command.ExecuteReader())
               {
                   //No customer found by supplied ID
                   if (!reader.HasRows)
                       return;
   
                   CustomerIdTextBox.Text = reader.GetInt32("Id").ToString();
                   FirstnameTextBox.Text = reader.GetString("Firstname");
                   LastnameTextBox.Text = reader.GetString("Lastname");
               }
           }
       }
   }
   
   //Pass the selected ID in the combobox to the customer details loader method 
   private void CustomerIdComboBox_SelectedIndexChanged(object s, EventArgs e)
   {
       var customerId = Convert.ToInt32(CustomerIdComboBox.Text);
       LoadCustomerDetailsById(customerId);
   }
   

I'm not entirely sure if this is what your looking for but most of the guidelines still applies.

Hope this helps!

Answer 5

SqlDataAdapter is used to communicate with SQL Server rather than MySQL.

Try the following:

MySqlDataAdapter da = new MySqlDataAdapter("select distinct LastName from tserviceinfo where LastName='" + lastName + "'", conn);

Answer 6

Try something like this to bind data to the combo box:

public void ListCat()
{
    DataTable linkcat = new DataTable("linkcat");
    using (SqlConnection sqlConn = new SqlConnection(@"Connection stuff;"))
    {
        using (SqlDataAdapter da = new SqlDataAdapter("SELECT LastName FROM list WHERE LastName <> 'NULL'", sqlConn))
        {
            da.Fill(linkcat);
        }
    }
    foreach (DataRow da in linkcat.Rows)
    {
        comboBox1.Items.Add(da[0].ToString());
    }
}

Taken from my own question.