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
|