Updating Rows Using the AseDataAdapter Object

Use the AseDataAdapter object to update rows.

1. Declare and initialize an AseConnection object:

   For C#:

   AseConnection conn = new AseConnection( c_connStr );

   For Visual Basic .NET:

   Dim conn As New AseConnection( _
      c_connStr )

2. Open the connection:

   For C#:

   conn.Open();

   For Visual Basic .NET:

   conn.Open()

3. Create a new AseDataAdapter object:

   For C#:

   AseDataAdapter adapter = new AseDataAdapter(); 
   adapter.MissingMappingAction =     
      MissingMappingAction.Passthrough; 
   adapter.MissingSchemaAction =        MissingSchemaAction.Add;

   For Visual Basic .NET:

   Dim adapter As New AseDataAdapter()
   adapter.MissingMappingAction = _
      MissingMappingAction.Passthrough
   adapter.MissingSchemaAction = _
      MissingSchemaAction.Add

4. Create an AseCommand object and define its parameters.

   The following code creates a Select and an Update command and defines the parameters for the Update command:

   For C#:

   adapter.SelectCommand = new AseCommand(
      "SELECT * FROM publishers WHERE pub_id > '9900'",    conn );
   adapter.UpdateCommand = new AseCommand(
      "UPDATE publishers SET pub_name = @pub_name, " +
      "city = @city, state = @state " +
      "WHERE pub_id = @pub_id", conn );
   adapter.UpdateCommand.UpdatedRowSource =
      UpdateRowSource.None;
   AseParameter parm = new AseParameter("@pub_id", 
      AseDbType.Char, 4);
   parm.SourceColumn = "pub_id";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.UpdateCommand.Parameters.Add( parm );
   parm = new AseParameter("@pub_name",
      AseDbType.VarChar, 40);
   parm.SourceColumn = "pub_name";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.UpdateCommand.Parameters.Add( parm );
   parm = new AseParameter("@city",
      AseDbType.VarChar, 20);
   parm.SourceColumn = "city";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.UpdateCommand.Parameters.Add( parm );
   parm = new AseParameter("@state",
      AseDbType.Char, 2);
   parm.SourceColumn = "state";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.UpdateCommand.Parameters.Add( parm );

   For Visual Basic .NET:

   adapter.SelectCommand = New AseCommand( _
     "SELECT * FROM publishers WHERE pub_id > '9900'",_
     conn )
   adapter.UpdateCommand = New AseCommand( _
     "UPDATE publishers SET pub_name = @pub_name, " + _
     "city = @city, state = @state " + _
     "WHERE pub_id = @pub_id", conn )
   adapter.UpdateCommand.UpdatedRowSource = _
     UpdateRowSource.None
   Dim parm As New AseParameter("@pub_id", _
     AseDbType.Char, 4)
   parm.SourceColumn = "pub_id"
   parm.SourceVersion = DataRowVersion.Current
   adapter.UpdateCommand.Parameters.Add( parm )
   parm = New AseParameter("@pub_name", _
     AseDbType.VarChar, 40)
   parm.SourceColumn = "pub_name"
   parm.SourceVersion = DataRowVersion.Current
   adapter.UpdateCommand.Parameters.Add( parm )
   parm = New AseParameter("@city", _
     AseDbType.VarChar, 20)
   parm.SourceColumn = "city"
   parm.SourceVersion = DataRowVersion.Current
   adapter.UpdateCommand.Parameters.Add( parm )
   parm = New AseParameter("@state", _
     AseDbType.Char, 2)
   parm.SourceColumn = "state"
   parm.SourceVersion = DataRowVersion.Current
   adapter.UpdateCommand.Parameters.Add( parm )

5. Fill the DataTable with the results of the Select statement:

   For C#:

   DataTable dataTable = new DataTable( "publishers" );
   int rowCount = adapter.Fill( dataTable );

   For Visual Basic .NET:

   Dim dataTable As New DataTable( "publishers" )
   Dim rowCount As Integer = adapter.Fill( dataTable )

6. Update the DataTable with the updated values for the rows, and apply the changes to the database:

   For C#:

   foreach ( DataRow row in dataTable.Rows ) 
   {
      row[1] = ( string ) row[1] + "_Updated";
   }
   int recordsAffected = adapter.Update( dataTable );

   For Visual Basic .NET:

   Dim row as DataRow
   For Each row in dataTable.Rows
      row(1) = row(1) + "_Updated"
   Next
   Dim recordsAffected As Integer = _
   adapter.Update( dataTable )

7. Bind the results to the grid on the window:

   For C#:

   dataTable.Clear();
   adapter.SelectCommand.CommandText =
      "SELECT * FROM publishers";
   rowCount = adapter.Fill( dataTable );
   dataGrid.DataSource = dataTable;

   For Visual Basic .NET:

   dataTable.Clear()
   adapter.SelectCommand.CommandText = _
      "SELECT * FROM publishers";
   rowCount = adapter.Fill( dataTable )
   dataGrid.DataSource = dataTable

8. Close the connection:

   For C#:

   conn.Close();

   For Visual Basic .NET:

   conn.Close()