Columns are initially defined using the setColumnExpressions method. To assign a column property to a column use the setColumnProperty method referencing the column name specified in setColumnExpressions
This property defines the name of the column in the table specified in the fromPart. columnExpression is the only required column property and is assigned using the setColumnExpressions method that allows all the columns to be defined in a single method call
Defines the text label displayed against the column. Defaults to the database column name if not specified. Column labels can be assigned with the setColumnLabels method or the setColumnProperty method
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnExpressions("CustomerID","EmployeeID","OrderDate","ShipVia");
setColumnLabels("Customer","Employee","Order Date","Shipped Via");
setColumnProperty("CustomerID","lookup","select CustomerID, CompanyName from Customers");
setColumnProperty("EmployeeID","lookup","select EmployeeID, LastName & ', ' & FirstName from Employees");
setColumnProperty("ShipVia","lookup","select ShipperID, CompanyName from Shippers");
initialize()
}
}
</script>
For a form to be editable or to act as a parent to a child control it is necessary for at least one column to be designated as a PrimaryKey. For multi-part primary keys every column that constitutes the key must have the PrimaryKey property set. In many cases DbNetEdit is able to automatically detect that a column is a PrimaryKey and will set this property automatically.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Customers"
setColumnExpressions("CustomerID","CompanyName");
setColumnProperty("CompanyName","label","Company Name");
setColumnProperty("CustomerID","primaryKey", true;
initialize()
}
}
</script>
Enables tracking of the date and the user that changes made to the column. Possible values are none, summary and detail. In summary mode only the date/user of the last change to the column is recorded, in detail mode all changes to the record are recorded including the previous value of the column and can be viewed in the Audit History dialog.
Indicates that the column value is auto-incrementing and therefore read-only. In the case of some columns such as SQL Server Identity columns this property is set automatically.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Employees"
setColumnExpressions(
"EmployeeID",
"LastName",
"FirstName"
)
setColumnProperty("EmployeeID","autoincrement", true)
initialize()
}
}
</script>
Indicates that a column is not displayed in the form. Used for columns that are required to be present but not visible.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Suppliers"
setColumnExpressions("CompanyName","Address & ', ' & City as addr","Address","City","Phone & '/' & Fax as phone_fax","Phone","Fax");
setColumnLabels("CompanyName","Address","Address","City","Phone/Fax","Phone","Fax");
setColumnProperty("addr","edit",false);
setColumnProperty("address","display",false);
setColumnProperty("city","display",false);
setColumnProperty("phone_fax","edit",false);
setColumnProperty("phone","display",false);
setColumnProperty("fax","display",false);
initialize()
}
}
</script>
Indicates that the column should be used in the browse list. The browse list is only enabled if at least one column has the browse property set to true.
The culture property allows the default culture derived from the browser language to be overridden for a particular column. For example if a column represents a dollar amount you can force the value to be formatted as a dollar amount irrespective of the default culture.
<script>
jQuery(document).ready( init )
function init()
{
var ordersEdit = new DbNetEdit("orders");
with (ordersEdit)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnProperties("Freight", {format:"c", culture:"en-US", toolTip:"Freight charge"});
initialize()
}
}
</script>
The editControlType property allows you to manually specify the type of control used to edit the database value. A default value is assigned based on the type and size of the database column. For example, an editControlType of Html will make the editor an HTML Editor rather than the default Text editor for a text column.
Edit Control Types
Auto |
Control type is automatically assigned based on the underlying database column. Default. |
TextBox |
Single-line text box |
TextBoxLookup |
Text box with a lookup button that opens a list selection dialog. Requires the Lookup property to be assigned. |
TextBoxSearchLookup |
Text box with a lookup button that opens a list selection dialog that searches data defined by the Lookup property to be assigned. The search token will be used to search against all columns in the Lookup SQL apart from the first column. |
CheckBox |
Forces a field to act as a boolean type |
Html |
Edits HTML content with WYSIWYG HTML editor. The default editor has a minimal feature set for speed of loading. If a more fully featured HTML editor is required you can enabled the TinyMCE editor by simply including the TinyMCE libraries in your application. The TinyMCE HTML editor can be customised using the onBeforeTinyMceInit event. |
DropDownList |
Drop-down list of values specified by the Lookup property. |
RadioButtonList |
Radio button list of values specified by the Lookup property. |
ListBox |
Multi-line list of values specified by the Lookup property. |
Label |
Displays data in a read-only label |
TextArea |
Multi-line text box |
Password |
Password field where field contents are obscured |
AutoCompleteLookup |
Lookup items are suggested in response to typing the first few characters of the required value after searching values specified by the Lookup property. The search token will be used to search against all columns in the Lookup SQL apart from the first column. This lookup is ideal when there are large number of lookup items. |
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Products"
setColumnExpressions("ProductID","ProductName","CategoryID");
setColumnProperty("CategoryID","editControlType","RadioButtonList");
setColumnProperty("CategoryID","lookup","select categoryid, categoryname from categories");
setColumnProperty("CategoryID","required",true);
setColumnProperty("CategoryID","editControlProperties",{RepeatDirection : "Horizontal", RepeatColumns:2});
initialize()
}
}
</script>
editControlProperties (object)
The editControlProperties property can be used to assign values to the .Net controls used to create the editable controls. For example the following example sets the RadioButtonList server-control RepeatDirection and RepeatColumns properties
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Products"
setColumnExpressions("ProductID","ProductName","CategoryID");
setColumnProperty("CategoryID","editControlType","RadioButtonList");
setColumnProperty("CategoryID","editControlProperties",{RepeatDirection : "Horizontal", RepeatColumns:2});
initialize()
}
}
</script>
Indicates that the column is acting as a foreign key in a child grid (either linked or nested). A ForeignKey column is paired with a PrimaryKey column in the parent control in order to establish the relationship between the 2 controls.
<script>
jQuery(document).ready( init )
var ordersEdit = new DbNetEdit("orders");
var orderLinesEdit = new DbNetEdit("orderLines");
with (orderLinesEdit)
{
connectionString = "SamplesDatabase"
fromPart = "[Order Details]"
setColumnExpressions("OrderID","ProductID","UnitPrice","Quantity","Discount");
setColumnProperty("OrderID", "foreignKey", true);
}
with (ordersEdit)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnExpressions("CustomerID", "OrderID","OrderDate","RequiredDate","ShippedDate","Freight");
addLinkedControl(orderLinesEdit);
initialize()
}
}
</script>
Specifies a format mask for a date-time or numeric value. Values are the standard .NET date-time and numeric formatting strings. Use the EditFormat property to specify a distinct format string for the editable value. By default, formatting uses the culture indicated by the browser language (or server culture). You can specify the culture for formatting a particular column using the culture property.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnExpressions("OrderID","OrderDate","RequiredDate","ShippedDate","Freight");
setColumnProperties("OrderDate", {format:"D", toolTip:"Date ordered"});
setColumnProperties("RequiredDate", {format:"d", toolTip:"Date required"});
setColumnProperties("ShippedDate", {format:"MMM yyyy", editFormat : "d", toolTip:"Date shipped"});
setColumnProperties("Freight", {format:"c", toolTip:"Freight charge"});
initialize()
}
}
</script>
Supplies an initial default value for a column when adding a new record
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Employees"
...
setColumnProperties("TitleOfCourtesy","initialValue","Mr";
...
}
}
</script>
Specifies that the column cannot be edited when a record is being added.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","BirthDate","HireDate")
setColumnProperty("FirstName","UpdateReadOnly",true)
setColumnProperty("LastName","UpdateReadOnly",true)
setColumnProperty("FirstName","Required",true)
setColumnProperty("LastName","Required",true)
setColumnProperty("BirthDate","ReadOnly",true)
setColumnProperty("HireDate","InsertReadOnly",true)
initialize()
}
}
</script>
Specifies that the column should behave like a boolean (bit) column (accepting only true/false). Some databases do not have a dedicated boolean type and with this property you can make a different data type such as an integer act like a boolean database type.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "Oracle"
fromPart = "Products"
setColumnExpressions("ProductName","UnitPrice","UnitsInStock", "Discontinued");
setColumnProperty("Discontinued","isBoolean", true);
initialize()
}
}
</script>
The lookup property is used to convert a value to/from its descriptive value typically held in a separate database table. The lookup property has 2functions:
| 1. | Provide a lookup selection when editing the column |
| 2. | Provide a lookup selection when searching on the column |
The lookup property can be specified as either an SQL statement or as a Javascript array. When specified as an SQL Statement the first column is the foreign key column that is matched against the column value and the second value is the descriptive value. When specified as a JavaScript array it should be as an array of 2 element string arrays.
Lookup as a SQL statement
The first column in the SQL statement is the value assigned to the edit field. The second column is the descriptive value displayed to the user. When a lookup is searchable ( i.e editControlType is AutoCompleteLookup or TextBoxSearchLookup) the second column (and any subsequent columns) will be searched against using the supplied user token. If only a single column is specified the column is used as the value and descriptive text.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase";
setColumnExpressions("CustomerID","CompanyName","Region");
setColumnProperty("Region","lookup","select state_code, state_description from us_states")
setColumnProperty("Region","label","State")
initialize()
}
}
</script>
Lookup as a Javascript array
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase";
setColumnExpressions("CustomerID","CompanyName","Region");
setColumnProperty("Region","lookup","[['AK','Arkensas'],['CA','California'],['ID','Idaho'],['MT','Massachusetts'],['NM','New Mexico'],['OR','Oregon'],['WA','Washington'],['WY','Wyoming']]")
setColumnProperty("Region","label","State")
initialize()
}
}
</script>
The way a lookup is implemented in the Edit Dialog can be controlled by setting the editControlType property to one of the following values DropDownList (default), TextBoxLookup, AutoCompleteLookup, TextBoxSearchLookup, ListBox or RadioButtonList
The lookupSearchMode property is used to indicate if the search (via the search dialog) should be made directly against the column value or indirectly against the descriptive text as defined by the Lookup property. Possible values are SearchText or SearchValue.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnExpressions("OrderID","CustomerID","OrderDate","Freight")
setColumnProperty(["OrderID","OrderDate","Freight"],"search",false)
setColumnProperty("CustomerID",{label : "Customer", lookupSearchMode : "SearchText", lookup : "select CustomerID, CompanyName from Customers" })
initialize()
}
var dbnetedit2 = new DbNetEdit("dbnetedit2");
with (dbnetedit2)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnExpressions("OrderID","CustomerID","OrderDate","Freight")
setColumnProperty(["OrderID","OrderDate","Freight"],"search",false)
setColumnProperty("CustomerID",{label : "Customer", lookupSearchMode : "SearchValue", lookup : "select CustomerID, CompanyName from Customers" })
initialize()
}
}
</script>
Specifies the maximum height of an image associated with a column when displayed as a thumbnail in the grid. The width of the image is scaled in proportion to the height.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabaseVistaDB"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","Photo");
setColumnProperty("Photo",{uploadExtFilter:"png,jpg,gif", maxThumbnailHeight:100});
setColumnProperty("EmployeeID","primaryKey", true);
initialize()
}
}
</script>
Specifies text for the edit input placeholder
Indicates if a field is read-only in both insert and update mode. The column is displayed in the edit dialog but the value cannot be altered.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","BirthDate","HireDate")
setColumnProperty("FirstName","UpdateReadOnly",true)
setColumnProperty("LastName","UpdateReadOnly",true)
setColumnProperty("FirstName","Required",true)
setColumnProperty("LastName","Required",true)
setColumnProperty("BirthDate","ReadOnly",true)
setColumnProperty("HireDate","InsertReadOnly",true)
initialize()
}
}
</script>
When set to true then a value must be entered when editing the value
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","BirthDate","HireDate")
setColumnProperty("FirstName","Required",true)
setColumnProperty("LastName","Required",true)
initialize()
}
}
</script>
Indicates if a column is searchable (via the search dialog). Default is true.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Orders"
setColumnExpressions("OrderID","CustomerID","OrderDate","Freight")
setColumnProperty(["OrderID","OrderDate","Freight"],"search",false)
setColumnProperty("CustomerID",{label : "Customer", lookupSearchMode : "SearchText", lookup : "select CustomerID, CompanyName from Customers" })
initialize()
}
}
</script>
Specifies a lookup for the column in the search dialog when different from the Lookup property.
Specifies the name of an Oracle sequence or Firebird generator that is used to generate the primary key value.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "Oracle"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","Lastname")
setColumnProperty("EmployeeID","primaryKey", true);
setColumnProperty("EmployeeID","sequenceName", "Employee_Sequence");
initialize()
}
}
</script>
Indicates that a column is eligible to be searched against with the Simple Search dialog.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "customers"
setColumnExpressions("CompanyName","ContactName","Address","City","Phone","Fax");
setColumnProperty("Address","simpleSearch", false);
setColumnProperty("Phone","simpleSearch", false);
setColumnProperty("Fax","simpleSearch", false);
searchDialogMode = "simple"
initialize()
}
}
</script>
Indicates that a spell checking should be enabled for this column in the edit dialog. To enable for all column use the edit spellCheck property.
Specifies CSS style attributes that are applied to the column when displayed in the grid.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Products"
setColumnExpressions("ProductName","UnitPrice","UnitsInStock");
setColumnProperty("UnitPrice","style","background-color:gold");
setColumnProperty("UnitsInStock","editStyle","background-color:gold;font-weight:bold");
initialize()
}
}
</script>
Specifies a tooltip for the edit control that will be displayed when the mouse hovers over the control
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "[Order Details]"
setColumnExpressions("OrderID","ProductID","UnitPrice","Quantity","Discount");
setColumnProperties("ProductID", {label:"Product",lookup:"select productid, productname from products"});
setColumnProperties("UnitPrice", {format:"c",toolTip:"Enter the net price (without tax)"});
setColumnProperties("Quantity", {toolTip:"Enter quantity currently in stock"});
setColumnProperties("Discount", {format:"p",toolTip:"Enter discount as a percentage"});
initialize()
}
}
</script>
If set to true the column value will be checked for uniqueness when edited. Can be set on more than one column in which case the combination of column values must be unique. Any column that has the unique property set will automatically have the required property set to true.
Specifies that the column cannot be edited when a record is being amended.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabase"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","BirthDate","HireDate")
setColumnProperty("FirstName","UpdateReadOnly",true)
setColumnProperty("LastName","UpdateReadOnly",true)
setColumnProperty("FirstName","Required",true)
setColumnProperty("LastName","Required",true)
setColumnProperty("BirthDate","ReadOnly",true)
setColumnProperty("HireDate","InsertReadOnly",true)
initialize()
}
}
</script>
Specifies a comma separated inclusive list of valid file extensions that can be selected for uploading.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabaseVistaDB"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","Photo");
setColumnProperty("Photo",{uploadExtFilter:"png,jpg,gif", maxThumbnailHeight:100});
setColumnProperty("EmployeeID","primaryKey", true);
initialize()
}
}
</script>
Specifies the maximum size in KB of a file that can be uploaded.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabaseVistaDB"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","Photo");
setColumnProperty("Photo",{uploadExtFilter:"png,jpg,gif", uploadMaxFileSize:500});
setColumnProperty("EmployeeID","primaryKey", true);
initialize()
}
}
</script>
uploadOverwrite (boolean)
Indicates if an existing file can be overwritten when uploading a new file.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabaseVistaDB"
fromPart = "Employees"
setColumnExpressions("EmployeeID","FirstName","LastName","Photo");
setColumnProperty("Photo",{uploadExtFilter:"png,jpg,gif", uploadOverwrite:true});
setColumnProperty("EmployeeID","primaryKey", true);
initialize()
}
}
</script>
If a column is storing the path to an uploaded file then the uploadRootFolder must be specified to indicate where on the server the uploaded files should be saved.
<script>
jQuery(document).ready( init )
function init()
{
var dbnetedit1 = new DbNetEdit("dbnetedit1");
with (dbnetedit1)
{
connectionString = "SamplesDatabaseVistaDB"
fromPart = "Documents"
setColumnExpressions("DocumentID","DocumentDescription","FilePath")
setColumnProperty("FilePath",{uploadRootFolder:"~/samples/DbNetGrid/Upload Files",uploadExtFilter:"txt, doc, xls, pdf, ppt, jpg, gif"})
setColumnProperty("FilePath","uploadOverwrite",true)
initialize()
}
}
</script>
Specifies the width of a column edit control