SUMMARY

The smartDBforms.NET framework recognizes database fields which participate in a foreign key relation and automatically creates drop-down list user interface. In some cases you may need to have a lookup drop-down list for a field that does not participate in a foreign key relation. This can be achieved by manually creating the drop-down list and binding it to a separate data source.

MORE INFORMATION

In order to create a custom drop-down list lookup field for a field that does not participate in a foreign key relation do the following:

1. Create a new SmartDataSource control that will select the record set for the lookup field. Edit only the Select statement. You need only two columns - one will be the value column and the other will be the caption column. For example: SELECT CategoryID, CategoryName FROM Categories
2. After you have created and configured the initial SmartDBView form find the SmartDBControl that is responsible for the database field and delete it.
3. Drag-and-drop a DropDownList control from the toolbox.
4. From the DropDownList smart-tag panel select "Choose Data Source..."
5. In the configuration dialog select the lookup data source and configure which column will be used for value and which for display.
6. From the DropDownList smart-tag panel select "Edit DataBindings..."
7. Select the "Show all properties" check box.
8. For the SelectedValue property in the "Bound to" drop down select the column that holds the value in the main data source.
9. Make sure the "Two-way databinding" is checked.
10. For the Enabled property set the following "Custom binding" value - "Container.IsEditOrInsertMode". This will make sure that the drop-down list is only editable in Edit or Insert mode but not in ReadOnly mode.

In addition if the lookup database field allows NULL values the lookup data source should return one more database row which has NULL value in the ID column. For SQL Server this can be achieved by using the UNION command.
For example:SELECT CategoryID, CategoryName FROM Categories UNION SELECT NULL AS Expr1, '[NOT SET]' AS Expr2.
In this way the returned dataset will have one more row which serve as the NULL row.

Here is a short example:

	...
	<!-- The main data source which is being edited -->
    <smartDataSource:SmartDataSource ID="SmartDataSource1" runat="server">
	...
    </smartDataSource:SmartDataSource>

	<!-- The lookup data source which is used to fill in the drop-down lookup list 
		It selects only the CategoryID and CategoryName 
		colums from the lookup table Categories-->
	<smartDataSource:SmartDataSource ID="LookupDataSource" runat="server" 
		ConnectionString="<%$ ConnectionStrings:LocalSql.Northwind %>" 
		ProviderName="<%$ ConnectionStrings:LocalSql.Northwind.ProviderName %>" 
		SelectCommand="SELECT CategoryID, CategoryName FROM Categories 
			UNION SELECT NULL AS Expr1, '[NOT SET]' AS Expr2">
		<!-- UNION is used to add additional row to the dataset. The row serves
		as the default NULL row. -->
	</smartDataSource:SmartDataSource>
	...
	
    <!-- The database form  -->
    <smartDBforms:SmartDBView ID="SmartDBView1" runat="server" 
	DataSourceID="SmartDataSource1" DefaultMode="ReadOnly">
	<Content>
	...				
	
	<b>Category ID:</b>
	<!-- The lookup drop-down list. It uses the lookup data source to create its items.
	The SelectedValue is two-way bound to the lookup data base field - CategoryID-->
	<asp:DropDownList ID="CategoryDropDown" runat="server" 
		DataSourceID="LookupDataSource" DataTextField="CategoryName" 
		DataValueField="CategoryID" Enabled="<%# Container.IsEditOrInsertMode %>" 
		SelectedValue='<%# Bind("CategoryID") %>'>
	</asp:DropDownList>
	
	...
	</Content>
    </smartDBforms:SmartDBView>
	...