Lesson 4: Insert, update, and delete data

Lesson 5: Build and deploy your application

UltraLite.NET User's Guide
Tutorial: Visual Studio Application

Lesson 4: Insert, update, and delete data

This lesson shows you how to modify your database. It uses dynamic SQL to modify the data. You can also use a table-based API.

For more information, see Data access in UltraLite.

The first step in this section is to create a supporting method to maintain the list box. Once that is complete, you can add code to modify data.

Add code to maintain the listbox

Right click on the form and select View Code. Add the following method to update and populate the listbox; it will be used by other methods in your form.

The following code is for a C# application:

private void RefreshLB()
{
   try 
   {
      ResultSet DisplayAll;
      long NumRows;
      ListBoxNames.Items.Clear();
      PrepStmt = Conn.PrepareStatement( 
          "SELECT id, name FROM Names");
      DisplayAll = PrepStmt.ExecuteQuery();
      DisplayAll.MoveBeforeFirst();
      NumRows = DisplayAll.RowCount;
      ids = new int[ NumRows ];

      while (DisplayAll.MoveNext())
      {   
        lbNames.Items.Add(
        new LBItem(DisplayAll.GetString(2), 
                   DisplayAll.GetInt(1))
        );
       ids[ DisplayAll.GetInt(1) - 1 ] = 
            DisplayAll.GetInt(1);
      }

      DisplayAll.Close();
      PrepStmt.Close();
         }
         catch( SQLException err )
         {
      MessageBox.Show( 
         "Exception: " + err.Message +
         " sqlcode=" + err.ErrorCode);
         }
         catch ( System.Exception t )
         {
      MessageBox.Show( 
         "Exception: " + t.Message );
         }
      }

For a Visual Basic application, use this code:

Private Sub RefreshLB()
   Try
      Dim DisplayAll As iAnywhere.UltraLite.ResultSet
      Dim NumRows As Long

      lbNames.Items.Clear()
      PrepStmt = Conn.PrepareStatement( _
          "SELECT id, name FROM Names")
      DisplayAll = PrepStmt.ExecuteQuery
      DisplayAll.MoveBeforeFirst()
      NumRows = DisplayAll.RowCount

      While (DisplayAll.MoveNext)
         ListBoxNames.Items.Add( _
             New LBItem(DisplayAll.GetString(2), _
             DisplayAll.GetInt(1)) )
             Ids[ DisplayAll.GetInt(1) - 1 ] = 
            DisplayAll.GetInt(1)
      End While

      DisplayAll.Close()
      PrepStmt.Close()
   Catch
      MessageBox.Show(
        "Exception: " + Err.Description 
      )
   End Try
End Sub

This code carries out the following tasks:

Clears the listbox.
Instantiates a PreparedStatement and assigns it a SELECT query that returns the Names table in the database.
Instantiates an integer array with length equal to the number of rows in the Names table.
Populates the listbox with the names stored in the database, returned by the PreparedStatement, and populates the integer array with the ids returned by the PreparedStatement.
Closes the resultset and prepared statement.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code. For more information on the error code, you can look it up in the Adaptive Server Anywhere Error Messages book that is part of this documentation set.

Add the following method to create a new listbox item class to store the name and id.

The following code is for a C# application:

public class LBItem
{
   public String ItemName;
   public int ItemID;

   public LBItem(String name, int id)
   {
      ItemName = name;
      ItemID = id;
   }

   public override string ToString()
   {
      return ItemName;
   }
}

For a Visual Basic application, add the following code after your Form1 class:

Public Class LBItem
   Public ItemName As String
   Public Itemid As Integer

   Public Sub New(ByVal name As String, ByVal id As Integer)
      ItemName = name
      Itemid = id
   End Sub

   Public Overrides Function ToString() As String
      ToString = ItemName
   End Function
End Class

Add the following method to the Form1 class to get the ID of a selected listbox item.

The following code is for a C# application:

private int GetSelectedID()
{
   int curSel;
   LBItem item;
   curSel = lbNames.SelectedIndex;
   if (curSel < 0)
   {
      return -1;
   }
   else
   {
      item = (LBItem)lbNames.Items[ curSel ];
      return item.ItemID;
   }
}

For a Visual Basic application, add the following code just before the end of your Form1 class:

Private Function GetSelectedid() As Integer
   Dim curSel As Integer
   Dim item As LBItem

   curSel = lbNames.SelectedIndex
   If curSel < 0 Then
      GetSelectedid = -1
      Exit Function
   End If

   item = lbNames.Items(curSel)
   GetSelectedid = item.Itemid
End Function

This code carries out the following tasks:

Gets the SelectedIndex property of the listbox.
If the SelectedIndex does not correspond to a selection, returns -1.
If the SelectedIndex does correspond to a selection, returns the ID for that item.

Build the project.

You should be able to build the solution with no errors.

Add data modification methods

Double click on the Insert button to create a btnInsert_Click method. If developing a C# application, add the following code to the method:

try
{
   long RowsInserted;

   PrepStmt = Conn.PrepareStatement(
      "INSERT INTO Names(name) VALUES (?)");
   PrepStmt.SetStringParameter(1, txtName.Text);
   RowsInserted = PrepStmt.ExecuteStatement();
   PrepStmt.Close();
   RefreshLB();
}
catch( SQLException err )
{
   MessageBox.Show("Exception: " + err.Message +
      " sqlcode=" + err.ErrorCode);
}
catch ( System.Exception t )
{
   MessageBox.Show( "Exception: " + t.Message);
}

For a Visual Basic application, use the following code:

Try
   Dim RowsInserted As Long

   PrepStmt = Conn.PrepareStatement( _
       "INSERT INTO Names(name) VALUES (?)")
   PrepStmt.SetStringParameter(1, txtName.Text)
   RowsInserted = PrepStmt.ExecuteStatement()
   PrepStmt.Close()
   RefreshLB()
   Catch
      MessageBox.Show("Exception: " + Err.Description)
   End Try

This code carries out the following tasks:

Instantiates a PreparedStatement and assigns it an INSERT statement that inserts the value in the textbox into the database.
Executes the statement.
Closes the statement.
Refreshes the listbox.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code. For more information on the error code, you can look it up in the Adaptive Server Anywhere Error Messages book that is part of this documentation set.

Double click on the Update button to create a btnUpdate_Click method. If developing a C# application, add the following code to the method:

try
{
   long RowsUpdated;
   int updtid = ids[ lbNames.SelectedIndex ];

   PrepStmt = Conn.PrepareStatement( 
      "UPDATE Names SET name = ? WHERE id = ?" );
   PrepStmt.SetStringParameter(1, txtName.Text);
   PrepStmt.SetIntParameter(2, updtid);
   RowsUpdated = PrepStmt.ExecuteStatement();
   PrepStmt.Close();
   RefreshLB();
}
catch( SQLException err )
{   
   MessageBox.Show( 
      "Exception: " + err.Message +
      " sqlcode=" + err.ErrorCode);
}
catch ( System.Exception t )
{
   MessageBox.Show( "Exception: " + t.Message);
}

For a Visual Basic application, use the following code:

Try
   Dim RowsUpdated As Long
   Dim updtid As Integer = ids(lbNames.SelectedIndex)

   PrepStmt = Conn.PrepareStatement( _
      "UPDATE Names SET name = ? WHERE id = ?")
   PrepStmt.SetStringParameter(1, txtName.Text)
   PrepStmt.SetIntParameter(2, updtid)
   RowsUpdated = PrepStmt.ExecuteStatement()
   PrepStmt.Close()
   RefreshLB()
Catch
   MessageBox.Show("Exception: " + Err.Description)
End Try

This code carries out the following tasks:

Instantiates a PreparedStatement and assigns it an UPDATE statement that inserts the value in the textbox into the database based on the associated id (saved in the integer array ids).
Executes the statement.
Refreshes the listbox.
Closes the prepared statement.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code. For more information on the error code, you can look it up in the Adaptive Server Anywhere Error Messages book that is part of this documentation set.

Double click on the Delete button to create a btnDelete_Click method. If developing a C# application, add the following code to the method:

try
{
   int delid = ids[lbNames.SelectedIndex];
   long RowsDeleted;

   PrepStmt = Conn.PrepareStatement(
      "DELETE From Names WHERE id = ?" );
   PrepStmt.SetIntParameter(1, delid);
   RowsDeleted = PrepStmt.ExecuteStatement();
   PrepStmt.Close();
   RefreshLB();
}
catch( SQLException err )
{   
   MessageBox.Show("Exception: " + err.Message +
      " sqlcode=" + err.ErrorCode);
}
catch ( System.Exception t )
{
   MessageBox.Show( "Exception: " + t.Message);
}

For a Visual Basic application, use the following code:

Try
   Dim delid As Integer = ids(lbNames.SelectedIndex)
   Dim RowsDeleted As Long

   PrepStmt = Conn.PrepareStatement( _
      "DELETE From Names WHERE id = ?")
   PrepStmt.SetIntParameter(1, delid)
   RowsDeleted = PrepStmt.ExecuteStatement()
   PrepStmt.Close()
   RefreshLB()
Catch
   MessageBox.Show("Exception: " + err.description)
End Try

This code carries out the following tasks:

Instantiates a PreparedStatement and assigns it a DELETE statement that deletes the selected row from the database, based on the associated id from the integer array ids.
Executes the statement.
Refreshes the listbox.
Closes the prepared statement.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code. For more information on the error code, you can look it up in the Adaptive Server Anywhere Error Messages book that is part of this documentation set.