Dropdown lists can be created using the AxpDataGrid.AddEditLookup method or via the AxpDataGrid.LookupDataSet property.

Creating and filling a dropdown list with values from another database table

The AddEditLookup method

Public Sub AddEditLookup( _
   ByVal FieldName As String, _
   ByVal LookupTable As String, _
   ByVal LinkField As String, _
   ByVal DisplayField As String, _
   ByVal OrderBy As String, _
   ByVal AllowNulls As Boolean _
)

Parameters

FieldName

Database Field (Column) Name

LookupTable

Database Table Name for the table containing the look-up values

LinkField

Database Field in the Lookup table matching the FieldName

DisplayField

Database Field to be displayed in the Drop-down list. Can be the same as LinkField.

OrderBy

If supplied the drop-down list will be sorted on this Database field (in the Lookup table)

AllowNulls

If True, AxpDataGrid will update the Table field with a NULL value if the user selects a blank entry in the Drop-down.
The database relationship and validation rules must be set accordingly obviously.

Example

Replace the input field Products.SupplierID with the Lookup value Suppliers.CompanyName

AxpDataGrid1.AddEditLookup("SupplierID", "Suppliers", "SupplierID", "CompanyName", "CompanyName", True)

FieldName="SupplierID" is the Column Name for the dropdown list in the Edit Form. It is typically a Foreign Key to the LookupTable.LinkField in the database.

LookupTable="Suppliers" is the database table where the dropdown values should be selected from.

LinkField="SupplierID" is the Primary Key of the LookupTable.

DisplayField="CompanyName" is the value to display in the dropdown list.

OrderBy="CompanyName" is an optional argument, and if set the dropdown list will be ordered by this column

AllowNulls=False creates a dropdown list without a blank entry.

Remarks

The relationship between the parameters and the SQL command generated by the grid is as follows:

SELECT LinkField, DisplayField FROM LookupTable ORDER BY OrderBy

It is thus possible to include a WHERE CLAUSE the parameter LookTable.
Say we want to exclude Suppliers from Sweden in the dropdown list.

Using the Nortwind database we can then include a WHERE CLAUSE:

AxpDataGrid1.AddEditLookup("SupplierID", "Suppliers WHERE Country<>'Sweden'", "SupplierID", "CompanyName", "CompanyName", True)

Creating and filling a dropdown list with custom made values

The lookups created using AddEditLookup is inserted into AxpDataGrid.LookupDataSet. This is .NET DataSet with one DataTable per lookup. The DataSet.Tables(i).TableName is set equal to the FieldName parameter value in the example above (i.e. the DataSet contain one DataTable with the TableName "SupplierID").

DataTables can be added to the AxpDataGrid.LookupDataSet manually, in code.

Each DataTable in the LookupDataSet must have two DataColumns, the first Column must contain the dropdown list values and the second column must contain the dropdown list display values.

It is then obvious that a dropdown can be filled with values from any data source, or it could even be hard coded.

In this example we create a DataTable named "SupplierID", create two DataColumns named LinkField and DisplayField and fill the DataTable with some hard coded values.

Dim dt As New DataTable("SupplierID")
dt.Columns.Add("LinkField") ' The Value field for the lookup must be the first column, i.e. Column 0. The name of the column does not matter
dt.Columns.Add("DisplayField") ' The Display field for the lookup must be in column 1
dt.Rows.Add(New String() {"", ""})
dt.Rows.Add(New String() {"RJ", "Rio de Janeiro"})
dt.Rows.Add(New String() {"SP", "Spain"})
AxpDataGrid1.LookupDataSet.Tables.Add(dt)

See Also

AxpDataGrid Tutorial

---