Class

DatabaseField

Warning

This item was deprecated in version 2019r2. Please use DatabaseColumn as a replacement.

Description

Used to access the values of fields in a record in a database table.

Properties

Name	Type	Read-Only
BlobValue	String
BooleanValue	Boolean
CurrencyValue	Currency
DateTimeValue	DateTime
DateValue	Date
DoubleValue	Double
Int64Value	Int64
IntegerValue	Integer
Name	String	✓
NativeValue	String	✓
PictureValue	Picture
StringValue	String
Type	Integer	✓
Value	Variant

Methods

Name	Parameters	Returns	Shared
GetString		String
SetString	newValue As String

Property descriptions

DatabaseField.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.

DatabaseField.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.Edit
rs.Field("MyBooleanColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the boolean value of a column in a RowSet:

' rs is a RecordSet with a boolean column called "AllowEmails"
If rs.Field("AllowEmails").BooleanValue Then
  MsgBox("Emails are allowed.")
End If

Set the boolean value of a column in a RowSet:

' rs is a RecordSet with a boolean column called "AllowEmails":
rs.Edit
rs.Field("AllowEmails").BooleanValue = True
rs.Update

DatabaseField.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.Edit
rs.Field("MyCurrencyColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the currency value of a column in a RowSet:

' rs is a RecordSet with a currency column called "Amount"
Dim amount As Currency
amount = rs.Field("Amount").CurrencyValue

Set the currency value of a column in a RowSet:

' rs is a RecordSet with a currency column called "Amount":
rs.Edit
rs.Field("Amount").CurrencyValue = 123.45
rs.Update

DatabaseField.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 New DateTime
rs.DateTimeColumn("InvoiceDate").DateTime = d
rs.SaveRow

DatabaseField.DateValue

DateValue As Date

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.Edit
rs.Field("MyDateColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the date value of a column in a RowSet:

' rs is a RecordSet with a date column called "InvoiceDate"
Dim invoiceDate As Date
invoiceDate = rs.Field("InvoiceDate").DateValue

Set the date value of a column in a RowSet:

' rs is a RecordSet with a date column called "InvoiceDate":
rs.Edit
Dim d As New Date
rs.Field("InvoiceDate").Date = d
rs.Update

DatabaseField.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.Edit
rs.Field("MyDoubleColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the double value of a column in a RowSet:

' rs is a RecordSet with a double column called "InterestRate"
Dim interestRate As Double
interestRate = rs.Field("InterestRate").DoubleValue

Set the double value of a column in a RowSet:

' rs is a RecordSet with a double column called "InterestRate":
rs.Edit
rs.Field("InterestRate").DoubleValue = 3.625
rs.Update

DatabaseField.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.Edit()
rs.Field("MyColumn").Value = Nil ' sets to NULL in the database
rs.Update()

Get the Int64 value of a column in a RowSet:

' rs is a RecordSet with a Int64 column called "ID"
Dim id As Int64
id = rs.Field("ID").Int64Value

Set the Int64 value of a column in a RowSet:

' rs is a RecordSet with a Int64 column called "ID":
rs.Edit
rs.Field("ID").Int64Value = 456789
rs.Update

DatabaseField.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.Edit
rs.Field("MyIntegerColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the integer value of a column in a RowSet:

' rs is a RecordSet with an integer column called "Quantity"
Dim quantity As Integer
quantity = rs.Field("Quantity").IntegerValue

Set the integer value of a column in a RowSet:

' rs is a RecordSet with an integer column called "Quantity":
rs.Edit
rs.Field("Quantity").IntegerValue = 3
rs.Update

DatabaseField.Name

Name As String

Used to get the name of the column.

This property is read-only.

The following method populates a ListBox with a RowSet. It uses the Name and StringValue properties to obtain the fieldnames and values:

Sub PopulateListBox(dataList As Listbox, rs As RecordSet)
  If rs Is Nil Then Return

  ' set up listbox state for population
  dataList.DeleteAllRows
  dataList.Columncount = rs.Fieldcount

    ' Add the DB columns as the heades for the ListBox
    dataList.ColumnCount = rs.FieldCount
    dataList.Column(-1).WidthExpression = "100"
    For i As Integer = 0 To rs.FieldCount - 1
      dataList.Heading(i) = rs.IdxField(i + 1).Name
    Next

    ' Add the data from the table
    While Not rs.EOF
      dataList.AddRow("")

      For i As Integer = 0 To rs.FieldCount - 1
        dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i + 1).StringValue
      Next

      rs.MoveNext
    Wend
End Sub

DatabaseField.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 RecordSet with a BLOB column called "FileData"
Dim data As String
data = rs.Field("FileData").NativeValue

DatabaseField.PictureValue

PictureValue As Picture

Gets or sets the PictureValue.

For most databases you will want to use the NativeValue property instead.

For situations where you need to set a database column to NULL, you should use the Value property like this:

rs.Edit
rs.Field("MyColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the picture value of a column in a RowSet:

' rs is a RecordSet with a picture column called "ProductImage"
Dim productImage As Picture
productImage = rs.Field("ProductImage").PictureValue

Set the picture value of a column in a RowSet:

' rs is a RecordSet with a picture column called "ProductImage":
rs.Edit
rs.Field("ProductImage").PictureValue = Canvas1.Backdrop
rs.Update

Get the picture value of a column in a RowSet, converting it to JPEG:

' rs is a RecordSet with a picture column called "ProductImage"
Dim productImage As Picture
productImage = rs.Field("ProductImage").PictureValue(Picture.FormatJPEG)

DatabaseField.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.Edit
rs.Field("MyStringColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the string value of a column in a RowSet:

' rs is a RecordSet with a string column called "ProductName"
Dim productName As String
productName = rs.Field("ProductName").StringValue

Set the string value of a column in a RowSet:

' rs is a RecordSet with a string column called "ProductName":
rs.Edit
rs.Field("ProductName").StringValue = "Generic Widgets"
rs.Update

DatabaseField.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.

DatabaseField.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.Edit
rs.Field("MyColumn").Value = Nil ' sets to NULL in the database
rs.Update

Get the double value of a column in a RowSet:

' rs is a RecordSet with a double column called "InterestRate"
Dim interestRate As Double
interestRate = rs.Field("InterestRate").Value ' Converts from a Variant to a Double

Set the double value of a column in a RowSet:

' rs is a RecordSet with a double column called "InterestRate":
rs.Edit
rs.Field("InterestRate").Value = 3.625
rs.Update

Method descriptions

DatabaseField.GetString

GetString As String

Gets the value of the field as a String.

This is equivalent to using StringValue to get the value.

Get the string value of a column in a RowSet:

' rs is a RecordSet with a string column called "ProductName"
Dim productName As String
productName = rs.Field("ProductName").GetString

DatabaseField.SetString

SetString(newValue As String)

Sets the value of the field to the passed String.

This is equivalent to using StringValue to set the value.

Set the string value of a column in a RowSet:

' rs is a RecordSet with a string column called "ProductName":
rs.Edit
rs.Field("ProductName").SetString("Generic Widgets")
rs.Update

Notes

Assignments to the DateValue property store the time as well as the date, as with DatabaseRecords. When getting a value, the resulting DateTime object may contain a time as well as a date. For fields of type DateTime, 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, DateValue, 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.Field("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.Edit
rs.Field("MyColumn").Value = Nil ' sets to NULL in the database
rs.Update

Sample code

The following method populates a ListBox with a RowSet. It uses the Name and StringValue properties to obtain the fieldnames and values:

Sub PopulateListBox(dataList As Listbox, rs As RecordSet)
  If rs Is Nil Then Return

  ' set up listbox state for population
  dataList.DeleteAllRows
  dataList.Columncount = rs.Fieldcount

    ' Add the DB columns as the heades for the ListBox
    dataList.ColumnCount = rs.FieldCount
    dataList.Column(-1).WidthExpression = "100"
    For i As Integer = 0 To rs.FieldCount - 1
      dataList.Heading(i) = rs.IdxField(i + 1).Name
    Next

    ' Add the data from the table
    While Not rs.EOF
      dataList.AddRow("")

      For i As Integer = 0 To rs.FieldCount - 1
        dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i + 1).StringValue
      Next

      rs.MoveNext
    Wend
End Sub

The following code uses the Value property to set the values of fields of different data types.

Dim myDB As RealSQLDatabase
Dim rs As RecordSet
rs = myDB.SQLSelect("SELECT * FROM MyTable")
rs.Edit
rs.Field("MyName").Value = Nil ' NULL this field, does not work for all database plugins
rs.Field("MyID").Value = 23
rs.Field("MyText").Value = "Test"
rs.Update
If Not myDB.Error Then
  myDB.Commit
Else
  MsgBox("Error: " + myDB.ErrorMessage)
End If

Compatibility

All project types on all supported operating systems.

DatabaseField

Description

Properties

Methods

Property descriptions

Method descriptions

Notes

Null columns

Sample code

Compatibility

See also