Binding to Databases

Using Visual Basic®, you can bind the Spread ActiveX controls to databases. When you bind the fpSpread control to a database, it automatically displays the data from the database in columns, where each field is assigned to a column and each row represents a record. You can customize how the data from the database is displayed in the spreadsheet, as well as how the fpSpread control interacts with a data control. If you are working with a large database, you can use virtual mode to save time and system resources.

Bind using ADO | Display Selected Fields | Custom Headers | Size Columns | Assign Cell Types

Bind the fpSpread Control using ADO

Spread provides an ADO version of its ActiveX control which you can use to take advantage of ActiveX Data Objects (ADO) offered in Visual Basic 6.0 and later and other development environments that support ADO. The following example binds Spread to a recordset Set rs = New ADODB.Recordset rs.Open "Select * from Employees", adoconn1, adOpenStatic, adLockOptimistic 'Bind Spread to the recordset Set fpSpread1.DataSource = rs

Display Selected Fields

If you bind the spreadsheet using the default settings, the spreadsheet automatically displays all the fields in the database sequentially in the spreadsheet. If you prefer, the spreadsheet can display selected fields in designated columns. The following example sets column 2 to display the ProductName field and column 2 to display the Title field. fpSpread1.Col = 2 fpSpread1.DataField = "ProductName"

Custom Headers
If you use the default data-binding settings, the column headers display the database field names. However, you can change the column headers to display your own text. The following example adds custom header text to the column header. fpSpread1.DAutoHeadings = False fpSpread1.SetText 2, 0, "MyHeader 2"

Size Columns

If you use the default data-binding settings, the spreadsheet automatically sizes columns based on the length of the largest string in the corresponding bound database field. If you prefer, you can customize how the spreadsheet determines the column width when it sizes the columns automatically or you can manually size the columns. The following example sizes the column to the length of the longest string in the field fpSpread1.DAutoSizeCols = DAutoSizeColsMax

Assign Cell Types

By default, the spreadsheet automatically assigns the cell type for each column's cells based on the field type of the corresponding bound database field. You can override the assigned cell type, or you can turn off the automatic assignment and assign the cell types manually. The following example overrides the assigned cell type and changes the text cell in column one to a static cell to prevent the editing of the data. This code is placed within the fpSpread1_DataColConfig Event. 'Make the specified col static (read only) fpSpread1.Col = 1 fpSpread1.celltype = CellTypeStaticText

Binding to Databases