Binding the values in a dataset to a dropdown list

I think you might be grasping this wrong.

The dropdown combo has a simple setting that allows you to "set" WHAT values from the datasource will be used from the data table you "feed" the drop down list.

So, you can have this markup:

        <asp:DropDownList ID="DropDownList1" runat="server" 
            Height="26px" Width="207px"
            DataValueField="ID"
            DataTextField="HotelName"
            >
        </asp:DropDownList>

So, you can set the two columns used - they are NOT for feeding data to the dropodown.

You can also set the above two columns in code - but LITTLE need exists to do that.

eg:

        DropDownList1.DataValueField = "ID";
        DropDownList1.DataTextField = "HotelName";

ONCE you set the above, you are now free to query the database, load up say a datatalbe, and then assign that "table" to the Dropdown list.

You do it this way:

        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT ID,HotelName, City FROM tblHotels ORDER BY HotelName";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
                DropDownList1.DataSource = rstData;
                DropDownList1.DataBind();
                // add one blank row selection.
                DropDownList1.Items.Insert(0, new ListItem("- Select.. -", "0"));
            }
        }

So, note how the data table has 3 columns, but WHICH of the 3 do you want to use fo for the dropdown? You have two columns - typical the "ID" or "PK" value, and then the 2nd column is a text description.

Now, I am using the SqlProvider (for sql server). You have to replace SqlCommand with the OracleSQLcommand and also the connection. But the data table, and code that fills the dropdown list reamins the same as per above - regardless of what data provider you are using.

So those two settings (DataValueField, DataTextField) are NOT to be feed data, but are ONLY to set which columns to use from the data table. My example had 3 columns, but there could be 20 columns in that table - so those two settings determine which two columns to use. And often you might have a simple drop down to select a color or some such - and thus you ONLY need one column. In that case, set both Value/Text field to the one same column.

I was able to fill the dataset as follows at the page load

`private void filldepartment()
        {
            UserClass obj = new UserClass();
            DataSet ds2 = new DataSet();
            ds2.Merge(obj.departments());
            ddlDepartment.DataSource = ds2.Tables[0];
            ddlDepartment.DataTextField = "DEPARTMENT_NAME";
            ddlDepartment.DataValueField = "DEPARTMENT_ID";
            ddlDepartment.DataBind();
        }`

and then find the values as follows

ddlDepartment.DataSource = ds;
ddlDepartment.DataBind();

Adjusted version of your code, removing unnecessary operations:

    private void FillDepartmentDropDown()
    {
        UserClass obj = new UserClass();
        var dt = obj.GetDepartments();
        ddlDepartment.DataTextField = "DEPARTMENT_NAME";
        ddlDepartment.DataValueField = "DEPARTMENT_ID";
        ddlDepartment.DataSource = dt;
        ddlDepartment.DataBind();
    }

And then GetDepartments might look like:

public DataTable GetDepartments(){
  using var da = new OracleDataAdapter(
    "SELECT department_id, department_name FROM departments ORDER BY NLSSORT(department_name, 'NLS_SORT=GENERIC_M')", 
    _connstr
  );
  var dt = new DataTable();
  da.Fill(dt);
}