Class
DatabaseColumn
Description
Used to access the values of columns in a row in a database table.
Properties
Name |
Type |
Read-Only |
Shared |
---|---|---|---|
✓ |
|||
✓ |
|||
✓ |
|||
✓ |
|||
Methods
Name |
Parameters |
Returns |
Shared |
---|---|---|---|
format As Picture.Formats, quality As Integer = Picture.QualityDefault, Assigns value As Picture |
Property descriptions
DatabaseColumn.BlobValue
BlobValue As String
Used to get and set the values of Blob (Binary Large OBject) field types.
If the field is not of this type, BlobValue will try to return the value as a String. SQLiteDatabase converts text to UTF-8 text encoding.
When using this property to assign binary data, no encoding is added.
DatabaseColumn.BooleanValue
BooleanValue As Boolean
Used to get and set the values of Boolean field types.
The values 0
and False
are treated as False and 1
and True
are treated as True. The behavior of any other values is undefined when retrieved using BooleanValue. Use StringValue to retrieve the original values in the field.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyBooleanColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the boolean value of a column in a RowSet:
' rs is a RowSet with a boolean column called "AllowEmails"
If rs.Column("AllowEmails").BooleanValue Then
MessageBox("Emails are allowed.")
End If
Set the boolean value of a column in a RowSet:
' rs is a RowSet with a boolean column called "AllowEmails":
rs.EditRow
rs.Column("AllowEmails").BooleanValue = True
rs.SaveRow
DatabaseColumn.CurrencyValue
CurrencyValue As Currency
Used to get and set the values of Currency field types.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyCurrencyColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the currency value of a column in a RowSet:
' rs is a RowSet with a currency column called "Amount"
Var amount As Currency
amount = rs.Column("Amount").CurrencyValue
Set the currency value of a column in a RowSet:
' rs is a RowSet with a currency column called "Amount":
rs.EditRow
rs.Column("Amount").CurrencyValue = 123.45
rs.SaveRow
DatabaseColumn.DateTimeValue
DateTimeValue As DateTime
Used to get and set the values of DateTime field types.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.DateTimeColumn("MyDateColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the date value of a column in a RowSet:
' rs is a RowSet with a date column called "InvoiceDate"
Var invoiceDate As DateTime
invoiceDate = rs.Column("InvoiceDate").DateTimeValue
Set the date value of a column in a RowSet:
' rs is a RowSet with a date column called "InvoiceDate":
rs.EditRow
Var d As DateTime = DateTime.Now
rs.DateTimeColumn("InvoiceDate").DateTime = d
rs.SaveRow
DatabaseColumn.DoubleValue
DoubleValue As Double
Used to get and set the values of Double field types.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyDoubleColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the double value of a column in a RowSet:
' rs is a RowSet with a double column called "InterestRate"
Var interestRate As Double
interestRate = rs.Column("InterestRate").DoubleValue
Set the double value of a column in a RowSet:
' rs is a RowSet with a double column called "InterestRate":
rs.EditRow
rs.Column("InterestRate").DoubleValue = 3.625
rs.SaveRow
DatabaseColumn.Int64Value
Int64Value As Int64
Used to get and set the values of Int64 field types.
Support for 64-bit integers is at the framework level. Database plug-ins need to be updated to support 64-bit integers.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the Int64 value of a column in a RowSet:
' rs is a RowSet with a Int64 column called "ID"
Var id As Int64
id = rs.Column("ID").Int64Value
Set the Int64 value of a column in a RowSet:
' rs is a RowSet with a Int64 column called "ID":
rs.EditRow
rs.Column("ID").Int64Value = 456789
rs.SaveRow
DatabaseColumn.IntegerValue
IntegerValue As Integer
Used to get and set the values of Integer field types.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyIntegerColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the integer value of a column in a RowSet:
' rs is a RowSet with an integer column called "Quantity"
Var quantity As Integer
quantity = rs.Column("Quantity").IntegerValue
Set the integer value of a column in a RowSet:
' rs is a RowSet with an integer column called "Quantity":
rs.EditRow
rs.Column("Quantity").IntegerValue = 3
rs.SaveRow
DatabaseColumn.Name
Name As String
Used to get the name of the column.
This property is read-only.
The following method populates a DesktopListBox with a RowSet. It uses the Name and StringValue properties to obtain the columnnames and values:
Sub PopulateListBox(dataList As DesktopListBox, rs As RowSet)
If rs Is Nil Then Return
' set up listbox state for population
dataList.RemoveAllRows
' Add the DB columns as the heades for the ListBox
dataList.ColumnCount = rs.ColumnCount
dataList.ColumnAttributesAt(-1).WidthExpression = "100"
For i As Integer = 0 To rs.ColumnCount - 1
dataList.HeaderAt(i) = rs.ColumnAt(i + 1).Name
Next
' Add the data from the table
While Not rs.AfterLastRow
dataList.AddRow("")
For i As Integer = 0 To rs.LastColumnIndex
dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i + 1).StringValue
Next
rs.MoveToNextRow
Wend
End Sub
DatabaseColumn.NativeValue
NativeValue As String
Used to get the values of fields in their native encoding. Useful for reading blobs from database fields. Use Value or StringValue to set Blob values in database fields.
This property is read-only.
Get the native value of a column in a RowSet:
' rs is a RowSet with a BLOB column called "FileData"
Var data As String
data = rs.Column("FileData").NativeValue
DatabaseColumn.PictureValue
PictureValue As Picture
Gets the Picture value for the column. To set a picture value, use the method of the same name.
This property is read-only.
DatabaseColumn.StringValue
StringValue As String
Used to get and set the values of String/Character field types.
If the field is not of this type, StringValue will try to return the value as a String. SQLiteDatabase converts text to UTF-8 text encoding.
When using this property to assign binary data, no encoding is added.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyStringColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the string value of a column in a RowSet:
' rs is a RowSet with a string column called "ProductName"
Var productName As String
productName = rs.Column("ProductName").StringValue
Set the string value of a column in a RowSet:
' rs is a Rowset with a string column called "ProductName":
rs.EditRow
rs.Column("ProductName").StringValue = "Generic Widgets"
rs.SaveRow
DatabaseColumn.Type
Type As Integer
Used to get the values of type of a column.
This property is read-only.
See RowSet.ColumnType for a list of integers and their associated types.
DatabaseColumn.Value
Value As Variant
Used to get and set the value of a field of any data type.
The type-specific properties that get and set the values are recommended over Value.
When using this property to assign binary data, no encoding is added.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the double value of a column in a RowSet:
' rs is a RowSet with a double column called "InterestRate"
Var interestRate As Double
interestRate = rs.Column("InterestRate").Value ' Converts from a Variant to a Double
Set the double value of a column in a RowSet:
' rs is a RowSet with a double column called "InterestRate":
rs.EditRow
rs.Column("InterestRate").Value = 3.625
rs.SaveRow
Method descriptions
DatabaseColumn.PictureValue
PictureValue(format As Picture.Formats, quality As Integer = Picture.QualityDefault, Assigns value As Picture)
Sets the Picture value for the column. You can get the picture value using the property of the same name.
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyIntegerColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Get the picture value of a column in a RowSet:
' rs is a RowSet with an picture column called "Photo"
Var photo As Picture
photo = rs.Column("Photo").PictureValue
Set the picture value of a column in a RowSet:
' rs is a RowSet with an picture column called "Photo" and MyPhoto is a picture:
rs.EditRow
rs.Column("Photo").PictureValue(Picture.Formats.PNG) = MyPhoto
rs.SaveRow
Notes
Assignments to the DateTimeValue property store the time as well as the date. When getting a value, the resulting DateTime object may contain a time as well as a date. For fields of type Date, the time will be 00:00:00 (midnight); and for fields of type Time, the date will be January 1, 0001. Fields of type TimeStamp contain valid data for both the date and time.
Boolean fields are strict about what they expect when using the BooleanValue function: 0
and False
are treated as False and 1
and True
are treated as True. The behavior of any other values is undefined when retrieved using BooleanValue. The StringValue property, on the other hand, should be able to retrieve the original data if it can't be identified as a Boolean.
The conversion operator, Operator Convert, has been added to StringValue, BooleanValue, DateTimeValue, IntegerValue, and DoubleValue.
Typically you should use the type-specific "Value" properties to get and set values from the database.
Null columns
The properties that return intrinsic value types (Boolean, Currency, Double, Int64, Integer, String) all return their appropriate default value if the database column is NULL.
To check if a column is NULL, use the Value property like this:
If rs.Column("MyColumn").Value Is Nil Then
' Column value is NULL
End If
For situations where you need to set a database column to NULL, you should use the Value property like this:
rs.EditRow
rs.Column("MyColumn").Value = Nil ' sets to NULL in the database
rs.SaveRow
Sample code
The following method populates a DesktopListBox with a RowSet. It uses the Name and StringValue properties to obtain the column names and values:
Sub PopulateListBox(dataList As DesktopListBox, rows As RowSet)
If rows Is Nil Then Return
' set up listbox state for population
dataList.RemoveAllRows
' Add the DB columns as the heades for the ListBox
dataList.ColumnCount = rows.ColumnCount
dataList.Column(-1).WidthExpression = "100"
For i As Integer = 0 To rows.LastColumnIndex
dataList.Heading(i) = rows.ColumnAt(i).Name
Next
' Add the data from the table
For Each row As DatabaseRow In rows
dataList.AddRow("")
For i As Integer = 0 To rows.LastColumnIndex
dataList.CellTextAt(dataList.LastAddedRowIndex, i) = row.ColumnAt(i).StringValue
Next
Next
End Sub
The following code uses the Value property to set the values of fields of different data types.
Var myDB As SQLiteDatabase
Var rows As RowSet
rows = myDB.SelectSQL("SELECT * FROM MyTable")
Try
rows.EditRow
rows.Column("MyName").Value = Nil ' NULL this field, does not work for all database plugins
rows.Column("MyID").Value = 23
rows.Column("MyText").Value = "Test"
rows.SaveRow
myDB.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
Compatibility
All project types on all supported operating systems.
See also
Object parent class; Database, DatabaseRow, RowSet classes.