Fill Combobox from Database

Fill Combobox from database

You will have to completely re-write your code. DisplayMember and ValueMember point to columnNames! Furthermore you should really use a using block - so the connection gets disposed (and closed) after query execution.

Instead of using a dataReader to access the values I choosed a dataTable and bound it as dataSource onto the comboBox.

using (SqlConnection conn = new SqlConnection(@"Data Source=SHARKAWY;Initial Catalog=Booking;Persist Security Info=True;User ID=sa;Password=123456"))
{
    try
    {
        string query = "select FleetName, FleetID from fleets";
        SqlDataAdapter da = new SqlDataAdapter(query, conn);
        conn.Open();
        DataSet ds = new DataSet();
        da.Fill(ds, "Fleet");
        cmbTripName.DisplayMember =  "FleetName";
        cmbTripName.ValueMember = "FleetID";
        cmbTripName.DataSource = ds.Tables["Fleet"];
    }
    catch (Exception ex)
    {
        // write exception info to log or anything else
        MessageBox.Show("Error occured!");
    }               
}

Using a dataTable may be a little bit slower than a dataReader but I do not have to create my own class. If you really have to/want to make use of a DataReader you may choose @Nattrass approach. In any case you should write a using block!

EDIT

If you want to get the current Value of the combobox try this

private void cmbTripName_SelectedIndexChanged(object sender, EventArgs e)
{
    if (cmbTripName.SelectedItem != null)
    {
        DataRowView drv = cmbTripName.SelectedItem as DataRowView;

        Debug.WriteLine("Item: " + drv.Row["FleetName"].ToString());
        Debug.WriteLine("Value: " + drv.Row["FleetID"].ToString());
        Debug.WriteLine("Value: " + cmbTripName.SelectedValue.ToString());
    }
}

Fill combobox with database, starting with blank item

So you want to insert a blank item in the combo box at index = 0, correct?

//rest of your code
comboBox1.DataSource = dt;    
comboBox1.Items.Insert(0, new ListItem(" ", "-1")); //After filling the DataSource, insert an item in the Combobox at Index 0

What I have done here is to insert an item after the datasource is filled with the data from the DB. What happening in your code seems obvious to me. For more info on this, have a quick read. The article shows in Windows Forms but you'll get the idea in case you are on asp.net

Adding and Removing Items from a Windows Forms ComboBox, ListBox, or CheckedListBox Control

Last time I worked on these controls was almost a decade ago. I'm typing a sample here in SO, taking a reference from my old Source Control repo.

Common Variables

DataRow rw;
public DataSet ds = new DataSet();
public SqlDataReader dr;
public SqlCommand cmd = new SqlCommand();
public SqlDataAdapter adp = new SqlDataAdapter();

Using DataTable

//If DataSet contains the table already, remove it first
if (ds.Tables.Contains(tbl)) 
      ds.Tables.Remove(tbl);

//Open connection here. Better with using
  cmd.CommandText = "YOUR_SQL_QUERY_GOES_HERE";
  adp.SelectCommand = cmd;
  adp.Fill(ds, tbl);
//close the connection here

rw = ds.Tables[tbl].NewRow();        //Add a new row
rw[0] = "-1";                        //Set it's value
rw[1] = "Itemtext";                  //Set it's text
ds.Tables[tbl].Rows.InsertAt(rw, 0); //Insert this row in the DataTable(DT) at Index 0
comboBox1.DataSource = ds.Tables[tbl]; //Assign the DT in the DataSource of the combobox
comboBox1.DataTextField = "Default Text";
comboBox1.DataValueField = "Default Value";
comboBox1.DataBind();

Using DataReader

comboBox1.Items.Clear(); //Clear the dropdown first
//Open the connection, set SqlCommandType and Text
using (SqlDataReader reader = sqlcmd.ExecuteReader())
  {
     comboBox1.DataSource = reader; //Assign DataReader to the DataSource of the Combobox
     comboBox1.DataValueField = "usr_entrada";
     comboBox1.DataTextField = "no_servicio";
     comboBox1.DataBind();
     //Here you can insert a new item at Index 0 of the Combobox.
     //For your case, keep the "Default Text" blank
     comboBox1.Items.Insert(0, new ListItem("--Default Text--", "-1"));
  }
//close the connection

Please note that the code is not optimized for production use and is only to give you more ideas! So do not penalize the answer because of the code quality. I have written it directly in SO and there might be some syntax errors!

Populating the combobox from database

There are a couple of things to address here. First of all, you are never actually telling your ComboBox where to find the data it's meant to display.

This is done using the comboBoxx.setItems() method. This is easy to do within the controller's initialize() method.

Also, you stated that you set the onAction property of the ComboBox to your fillComboBox2() method. This is not correct. Doing so will cause the fillComboBox2() method to be called every time you click on the dropdown for the ComboBox.

Instead, you should fill the ComboBox when loading the scene. So, remove the onAction definition from your FXML document.

Lastly, it would be a good idea to change that method entirely. In my updated code below, you'll see that I've changed it to a private method that returns a List<String>. We can use that List to populate the ComboBox.

Now, when the scene is being loaded, the comboBoxx.setItems() method is called, and the List<String> from the getData() method is used to populate it.

The code below also has some comments to help explain the flow.

THE CODE

import javafx.collections.FXCollections;
import javafx.fxml.FXML;
import javafx.scene.control.ComboBox;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class FXMLController {

    // If you ComboBox is going to display Strings, you should define that datatype here
    @FXML
    private ComboBox<String> comboBoxx;

    @FXML
    private void initialize() {

        // Within this initialize method, you can initialize the data for the ComboBox. I have changed the
        // method from fillComboBox2() to getData(), which returns a List of Strings.
        // We need to set the ComboBox to use that list.
        comboBoxx.setItems(FXCollections.observableArrayList(getData()));

    }

    /**
     * Here we will define the method that builds the List used by the ComboBox
     */
    private List<String> getData() {

        String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=TestDB;integratedSecurity=true;";

        // Define the data you will be returning, in this case, a List of Strings for the ComboBox
        List<String> options = new ArrayList<>();

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);
            String query = "select artikulli from product_table";
            PreparedStatement statement = con.prepareStatement(query);

            ResultSet set = statement.executeQuery();

            while (set.next()) {
                options.add(set.getString("artikulli"));
            }

            statement.close();
            set.close();

            // Return the List
            return options;

        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}

Populate ComboBox from Database query

Rather than populating the items collection, you can bind a DataTable to the control to be used as the Datasource. Then, you can tell it which element to display and which value to submit to you when there is a selection:

Using con As New SqlConnection(sConnection)
    Using com As New SqlCommand("Select Id, Name FROM ....", con)
        con.Open()

        Dim dt As New DataTable()
        dt.Load(com.ExecuteReader)
        cbox1.Datasource = dt
        cbox.DisplayMember = "Name"
        cbox.ValueMember = "Id"
    End Using
End Using

"Name" and "Id" would be column names from the database table. The event you probably want to work with in this case would be the SelectedValueChanged and SelectedValue would hold the ID related to the item selected. This will be returned as Object so you may need to cast it back to whatever it is.

You can also bind to List(Of T) collections in the same way. In that case, the SelectedItem could be the entire object. For instance, using a List(of Employee), SelectedItem would be the object for the Employee the user selected.

C# Fill combo box from SQL DataTable

I'm not yet sure what is the exact error in your code, but if you're ok with not using DataTable, you can do it this way:

using (SqlConnection sqlConnection = new SqlConnection("connstring"))
{
    SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Doctor", sqlConnection);
    sqlConnection.Open();
    SqlDataReader sqlReader = sqlCmd.ExecuteReader();

    while (sqlReader.Read())
    {
        cbDoctor.Items.Add(sqlReader["name"].ToString());
    }

    sqlReader.Close();
}

For more information take a look at SqlDataReader reference on MSDN.

In orer to find the issue in the original code you posted, please provide information in which line you get the exception (or is it an error that prevents application from compiling?) and what is its whole message.

C# Populate a ComboBox from table in database

My advice - use .NET built-in functionality as much as possible, and don't handle data binding manually (which is what you are trying to do in your code):

1. Use ExecuteQuery to pull a DataTable from database.
2. Set DropDownName.DataSource = yourDataTable.
3. Set DropDownName.ValueMember = "id".
4. Set DropDownName.DisplayMember = "Name".

So your code would look similar to this:

public static void FillDropDownList(string Query, System.Windows.Forms.ComboBox DropDownName)
{
  DataTable dt;

  using (var cn = new SqlConnection(CONNECTION_STRING))
  {
    cn.Open();

    try
    {
      SqlCommand cmd = new SqlCommand(Query, cn);
      dt = cmd.ExecuteQuery();
    }
    catch (SqlException e)
    {
      Console.WriteLine(e.ToString());
      return;
    }
  }

  DropDownName.DataSource = dt;
  DropDownName.ValueMember = "id";
  DropDownName.DisplayMember = "Name";
}

Notice how I changed exception type to SqlException, so we are only looking for database errors. Everything else will blow up. I don't remember any situation when myConnection.Open(); would throw an exception, so your try block is not very useful. Notice in my try clause - it has ExecuteQuery inside it, which is likely to fail.

EDIT: There is no need to close connection in the finally block when using the using construct. So it can be removed - and your code becomes more compact as a result.

C# Code to populate ComboBox with database column values is not returning unique values

Change your query to this

 string query = "Select Distinct [Animal ID] As AnimalId from ExpData
 where SystemUser = '" + textBox15.Text.Trim() + "' Order By [Animal ID] ;" ;

And for the smart guys I recommend you to use a parameter instead of textBox15.Text

How to fill combobox with database values using n tier architecture in c# windows form

Instead of adding the items to the combobox directly, add them to a list. Assuming that EmployeeComboFills is your static DAL class, you can add this method to it

public static List<string> GetUserNames()
{
    string CommandText = "SELECT Name FROM User ORDER BY Name";
    using (SQLiteConnection conn = new SQLiteConnection(ecbconn()))
    using (SQLiteCommand cmd = new SQLiteCommand(CommandText, conn))
    {
        conn.Open();
        DataTable dt = new DataTable();
        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        da.Fill(dt);
        return dt.Rows
            .Cast<DataRow>()
            .Select(dr => dr["Name"].ToString())
            .ToList();
    }
}

Then you can assign it to the combobox with

CELD_employeename.DataSource = EmployeeComboFills.GetUserNames();

---

Related Topics