populating dropdownlist programatically

[toekneel]

I'm using Visual Studio 2005, VB... hope this is the right forum to post this question in.

I have multiple dropdownlists that are populated from a lookup table. In that lookup table, the user is allowed to flag an item as inactive so that it does not show up any longer in future drop down lists. The problem is that when a record is selected where the dropdownlist item is no longer active, it can't select a value from the dropdown list, throwing this error: "[dropdownlist] has a SelectedValue which is invalid because it does not exist in the list of items". In previous applications, I simply captured the fact that this was missing and then added it to the dropdownlist and then selected that value.

In this case, the dropdownlist is inside of a detailsview. I've been able to successfully build code that adds the item. Here's the essential part of that code:

(objDV is a DetailsView, DDName is the name of the dropdownlist in the DetailsView, and Value is the Item to be added to the list.)

If CType(objDV.FindControl(DDName), DropDownList).Items.FindByValue(Value) Is Nothing Then
CType(objDV.FindControl(DDName), DropDownList).Items.Add(Value)
CType(objDV.FindControl(DDName), DropDownList).SelectedValue = Value
End If

I've set this up on every single potential event (Page, DetailsView, DropDownList, DataSource for the DetailsView, DataSource for the DropDownList) and I've been able to verify that it works, checking the Item Count in the Immediate Window as I've stepped through my code but I always end up coming to the Page_Error event and the Item Count has dropped my new item before posting the error notice that I mentioned earlier. It appears that data is getting bound to the controls after I set the selected value, but I cannot seem to trap it in an event. What event is causing the dropdownlist to be reloaded, discarding my added item?

 

[jebenson]

To add a new item to a data bound control, add a new row to the datasource. For example:

'dt is a datatable, the datasource of DropDownList1
'Field1 is the DisplayMember, Field2 is the ValueMember

Dim dr As DataRow

dr = dt.NewRow

dr.Item("Field1") = "Some text"
dr.item("Field2") = 12345

dt.Rows.Add(dr)

DropDownList1.Refresh()


The new data should now appear in the dropdownlist.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[toekneel]

The datasource I'm using here is a SQL Data Source based on a query in a lookup table. I would not want the value to permanently populate that lookup table. I'm not certain how I would populate the datasource that I'm using here.

Essentially, what you've suggested is what I'm performing with the code that I've supplied, and it does work. The problem is that it adds it on for one event, but then on the dropdown_Init event, it appears to reload the dropdownlist, eliminating my selection. There are six more events that occur (DropDownList_Load, DetailsView_ItemCreated, DropDownList_DataBinding, SQLDataSource (for the dropdownlist)_Selecting, and SQLDataSource_Selected) and so far I've not succeeding in reloading the data on those.

 

[jebenson]

To do this, you need to "manually" retrieve the data and populate the list:

Dim conn As SqlConnection
Dim da As SqlDataAdapter
Dim dt As DataTable
Dim SQLStr As String

conn = New SqlConnection("Your connection string here")

SQLStr = "Select * from MyQueryName"

da = New SqlDataAdapter(SQLStr, conn)

dt = New DataTable

da.Fill(dt)

'save the datatable to a Session var
Session("dt") = dt

DropDownList1.DisplayMember = "Field1"
DropDownList1.ValueMember = "Field2"
DropDownList1.Datasource = dt

DropDownList1.DataBind()

Later, when you want to add something new to the dropdown:

Dim dt As DataTable

dt = CType(Session("dt"), DataTable)

Dim dr As DataRow

dr = dt.NewRow

dr.Item("Field1") = "Some text"
dr.item("Field2") = 12345

dt.Rows.Add(dr)

dt.AcceptChanges()

DropDownList1.DisplayMember = "Field1"
DropDownList1.ValueMember = "Field2"
DropDownList1.Datasource = dt

DropDownList1.DataBind()

Now the new item will appear in the list. The new item is in the in-memory datatable, but it is not in the lookup table. It will not be saved to the lookup table unless you write code to do so.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[toekneel]

Thanks for the input.

I have just finished polishing this on my own before your posting went up, and the solution was pretty much along those lines.

The first important step was to "disconnect" the dropdownlist from any data source on the smart tag, so that "select a data source" was equal to "(None)".

The correct event, which is a crucial point here, is the DetailsView_ItemCreated event. On this point, I capture the data that needs to be available for the drop down, then I set the code up like this:

'This only needs to run if the dropdownlist is not yet populated.
If CType(Me.DetailsView.FindControl("controlname"), DropDownList).Items.Count = 0 Then
sbDropDownPopulate
(Here I run code to populate the drop down by selecting records and then applying them with
DropDownList.Items.Add(New ListItem(objRead.GetValue(0), objRead.GetValue(1)))
'back to the main code
If CType(DetailsView.FindControl("controlname"), DropDownList).Items.FindByValue(NewValue) Is Nothing Then
CType(DetailsView.FindControl("controlname"), DropDownList).Items.Add(NewValue)
CType(DetailsView.FindControl("controlname"), DropDownList).SelectedValue = NewValue
End If
End If