Retrieve a specific item from an adapter?

Question

I've got this block of code in C# code-behind that fills a combo box:

ddlRole.Items.Clear();
ddlRole.SelectedValue = null;
DataTable DDLRoles = new DataTable();
using (SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["CLTDPL"].ConnectionString))
{
    //SqlDataAdapter adapter = new SqlDataAdapter("Select [Role_ID], [Role] from [MOS_Role] where [Function_ID] = " + FunID + " ORDER BY [Role] ASC", con2);
    SqlDataAdapter adapter = new SqlDataAdapter("Select [Role_ID], [Role], [BILLABLE_LBL] from [MOS_Role] where [Function_ID] = @FunID ORDER BY [Role] ASC", con2);
    adapter.SelectCommand.Parameters.AddWithValue("@FunID", Convert.ToInt32(ddlFunction.SelectedValue));
    adapter.Fill(DDLRoles);
    ddlRole.DataSource = DDLRoles;
    ddlRole.DataTextField = "Role";
    ddlRole.DataValueField = "Role_ID";
    ddlRole.DataBind();

}
ddlRole.Items.Insert(0, new ListItem("Select your role", "0"));

Now I'm being asked to change a label based on the field [BILLABLE_LBL]. So, I need to check if that field is NULL or 0, and write an If/Else statement based on that. However, I can't figure out how to get that value.

I mean, I could make a separate call to the data and use a reader, but that can't be the most efficient way.

but you have multiple rows that has [BILLABLE_LBL] right? are you going to put that label for every item in combobox? — mehmet mecek, Dec 14 '15 at 14:48

score 1 · Accepted Answer · edited May 23 '17 at 10:27

I hope I understand the question correctly but you want to change a field according to the selected Role from the combo box and don't know how to get the [BILLABLE_LBL] value (feel free to correct me).

You can get the DataRow from your DataTable via the Select() method.

DataRow[] row = DDLRole.Select("Role_ID = '" + selectedValueFromCombobox + "'");

Now that you have your matching DataRow you can read out the desired value:

string labelText = (row[0]["BILLABLE_LBL"] != DBNull.Value && row[0]["BILLABLE_LBL"] != 0) ? (string)row[0]["BILLABLE_LBL"] : "Empty Field";

This call checks if the [BILLABLE_LBL] is Null or 0 like you wanted. If it is not Null or 0, it takes the value and saves it as string labelText. Else you can set an alternative text like "Empty Field" or just "";

I assume you have a Listener on your ComboBox to check when the value changes, or a "Commit Button" or some other method where you retrieve the selected Role value. Just add the code to your method/listener to get your desired value and set it as text for your label.

EDIT: Maybe this is more what you were looking for, since you don't need to use Select() on the datatable:

DataRowView selectedRow = (ddlRole.SelectedItem as DataRowView);

To retrieve values from the row you can just use: selectedRow["BILLABLE_LBL"] Now you can do your check for Null and 0 and you are good to go. I don't know which one is more efficient (Select or ... as DataRowView) but either way you get your value.

(Credit for the .SelectedItem as DataRowView to https://stackoverflow.com/a/9825085/5332988)

This worked perfectly. Thanks! I'm still a bit of a n00b, but thanks to this site I'm learning a lot. — Johnny Bones, Dec 14 '15 at 16:44

Retrieve a specific item from an adapter?

1 Answers1