Data retrieval: SELECT

Data manipulation: INSERT, UPDATE and DELETE

UltraLite ActiveX User's Guide
Understanding UltraLite ActiveX Development
Accessing and manipulating data using Dynamic SQL

Data retrieval: SELECT

When you execute a SELECT statement, the ULPreparedStatement.ExecuteQuery method returns a ULResultSet object.

The ULResultSet class contains methods for navigting within a result set. The values are then accessed using the ULResultSet.Value property.

For more information about ULResultSet objects, see ULResultSet class.

In the following code fragment, the results of a SELECT query are accessed through a ULResultSet. When first assigned, the ULResultSet is positioned before the first row. The ULResultSet.MoveFirst method is then called to navigate to the first record in the result set.

For more information about navigating a result set, see Navigation with Dynamic SQL.

' eMbedded Visual Basic
Dim MyResultSet as ULResultSet
Dim x as ULPreparedStatement
Set x = Connection.PrepareStatement("SELECT ID, Name FROM customer")
Set MyResultSet = x.ExecuteQuery
MyResultSet.MoveFirst

// JScript
var MyResultSet;
var x;
x = Connection.PrepareStatement("SELECT ID, Name FROM customer");
MyResultSet = x.ExecuteQuery();
MyResultSet.MoveFirst();

The code fragment below demonstrates how to use the Value property to obtain the column values for the current row. The Value property uses the following syntax: MyResultSetName.Value(Index) where Index is the ordinal position of the column name in your SELECT statement.

This example uses the Value property access both Integer and String values. UltraLite ActiveX uses a variant data type to achieve this flexibility.

' eMbedded Visual Basic
If MyResultSet.RowCount = 0 Then
  lblID.Text = ""
  txtName.Text = ""
Else
  lblID.Caption = MyResultSet.Value(1)
  txtName.Text = MyResultSet.Value(2)
End If

// JScript
If ( MyResultSet.RowCount == 0 ) {
  lblID.Text = "";
  txtName.Text = "";
} Else {
  lblID.Caption = MyResultSet.Value(1);
  lblID.Text = MyResultSet.Value(2);
}

The following procedure uses a SELECT statement to retrieve information from the database. The results of the query are assigned to a ULResultSet object.

To perform a SELECT statement

Declare a ULPreparedStatement object.

' eMbedded Visual Basic
Dim PS As ULPreparedStatement

// JScript
var PS;

Assign a prepared statement to your ULPreparedStatement object. In the following code fragment, TableName and ColumnName are the names of a table and column.

' eMbedded Visual Basic
Set PS = Connection.PrepareStatement("SELECT ColumnName FROM TableName")

// JScript
PS = Connection.PrepareStatement("SELECT ColumnName FROM TableName")

Execute the statement.

In the eMbedded Visual Basic code below, a listbox captures the result of the SELECT query.

' eMbedded Visual Basic
Dim y As ULResultSet
Set y = PS.ExecuteQuery
While y.MoveNext
  listbox1.AddItem y.Value(1)
Wend

In the JScript code below, an string captures the result of the SELECT query as an HTML table.

// JScript
var y;
var resultTable;
y = PS.ExecuteQuery();
var ncols = y.Schema.ColumnCount;
var fld, line, nrows;
resultTable = "<html><table cellpadding=0 cellspacing=0><tr>";
resultTable = resultTable + "<th>" + "ColumnName" + "</th>";
resultTable = resultTable + "</tr><tr>";
nrows = 0;
while ( y.MoveNext() ) {
  line = "<tr>";
    nrows++;
  if ( y.IsNull(1) ) {
        fld = "(null)";
      } else {
        fld = y.Value(1);
      }
      line = line + "<td>" + fld + "</td>";
    resultTable = resultTable + line + "</tr>";
}
resultTable = resultTable + "</table><B>#Rows=" + nrows + "</B></html>";