Figures
© 2004 Microsoft Corporation. All rights reserved.
Figure 4 Setting up the DataAdapter
'SqlDataAdapter1
Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange( _
  New System.Data.Common.DataTableMapping() _
{New System.Data.Common.DataTableMapping("Table", _
  "Categories", New System.Data.Common.DataColumnMapping() _
  {New System.Data.Common.DataColumnMapping("CategoryID", _
    "CategoryID"), _
  New System.Data.Common.DataColumnMapping("CategoryName", _
    "CategoryName"), _
  New System.Data.Common.DataColumnMapping("Description", _
    "Description"), _
  New System.Data.Common.DataColumnMapping("Picture", "Picture")})})
Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
Figure 5 Creating the Insert Command
'SqlInsertCommand1
Me.SqlInsertCommand1.CommandText = "CategoryNewInsertCommand"
Me.SqlInsertCommand1.CommandType = _
  System.Data.CommandType.StoredProcedure
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add( _
  New System.Data.SqlClient.SqlParameter( _
  "@RETURN_VALUE", System.Data.SqlDbType.Int, 4, _
  System.Data.ParameterDirection.ReturnValue, False, _
  CType(0, Byte), CType(0, Byte), "", _
  System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(_
  New System.Data.SqlClient.SqlParameter("@CategoryName", _
  System.Data.SqlDbType.NVarChar, 15, _
  System.Data.ParameterDirection.Input, False, CType(0, Byte), _
  CType(0, Byte), "CategoryName", _
  System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(_
  New System.Data.SqlClient.SqlParameter("@Description", _
  System.Data.SqlDbType.NText, 16, _
  System.Data.ParameterDirection.Input, True, CType(0, Byte), _
  CType(0, Byte), "Description", _
  System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(_
  New System.Data.SqlClient.SqlParameter("@Picture", _
  System.Data.SqlDbType.Binary, 16, _
  System.Data.ParameterDirection.Input, True, CType(0, Byte), _
  CType(0, Byte), "Picture", _
  System.Data.DataRowVersion.Current, Nothing))
Figure 6 Try/Catch Loop
Private Sub cmdUpdate_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Dim oRow As DataRow
        Dim oTable As DataTable
        Try
            SqlDataAdapter1.Update(oDS, "Categories")
        Catch exeDB As System.Data.DBConcurrencyException
            txtStatus.Text = "A concurrency error occurred in " _
            & "row with the error indicator"
            oTable = oDS.Tables("Categories")
            For Each oRow In oTable.Rows
                If oRow.HasErrors Then
                    txtStatus.Text &= _
                "An error occurred during updating: " _
                & oRow.RowError
                End If
            Next
        Catch exc As Exception
            MsgBox("Error " & exc.Message & "  " _
            & exc.GetBaseException.ToString)
        End Try
    End Sub
Figure 7 Trapping Errors
Dim myConnection As New _
    SqlConnection( _
    "Data Source=localhost;Initial Catalog=Northwind;" _
    & "Integrated Security=SSPI;")
    myConnection.Open()

    ' Start a transaction.
    Dim myTrans As SqlTransaction = _
        myConnection.BeginTransaction()
    ' Assign a transaction object
    Dim myCommand As New SqlCommand()
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    Try
        myCommand.CommandText = _
          "Insert into Region " _
          & "(RegionID, RegionDescription) VALUES "  _
          & "(100, 'Description')"
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = _
          "Insert into Region " _
          & "(RegionID, RegionDescription) VALUES " _
          & "(101, 'Description')"
        myCommand.ExecuteNonQuery()
        myTrans.Commit()
        MsgBox("Both records are written to database.")
    Catch exc As Exception
        myTrans.Rollback()
        MsgBox(exc.Message & " - Neither record was written")
    Finally
        myConnection.Close()
    End Try
Page view tracker