3 table Access Database using vb.Net Problems with UPDATE and INSERT commands

[jay B via DotNetMonster.com]

BOOKINGS has a Primary key: Number. Two Foreign keys: UCI (link to
CUSTOMERS) & Code (link to HOLIDAYS).

HOLIDAYS has a Primary Key: Code.

The Database has one-to-many Relationships with Referential Integrity and
checked cascade UPDATE and DELETE (The many relationship for both links
being on the BOOKINGS table).

I have designed a GUI using vb.NET (Visual Studio) The connection to the
Database is via Microsoft Jet 4.0 OLE DB Provider and I have got the
following Datasets to work on both the CUSTOMERS and HOLIDAYS tables:
SELECT, INSERT, DELETE, UPDATE records. On coming to the BOOKINGS table
(which appears to be an INNER JOIN) I've got the SELECT and DELETE records
to work, but I'm having major problems getting the INSERT and UPDATE to
work. View the coding below:



'UPDATE BOOKING RECORD

Private Sub btnUpdateB_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdateB.Click

If MessageBox.Show("Do you wish to update the Booking record",
"Clear", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) =
DialogResult.Yes Then
End If

Try
OleDbConnection1.Open()
Dim command As String

'set up an SQL update

command = "UPDATE BOOKINGS SET Number = '" & txtNumber.Text _
& "', " & "UCI = '" & txtUCI.Text _
& "', " & "Code = '" & txtCode.Text _
& "', " & "Date = '" & txtDate.Text _
& "', " & "Adult = '" & txtAdult.Text _
& "', " & "Child = '" & txtChild.Text _
& "', " & "Deposit = '" & txtDeposit.Text _
& "', " & "Balance = '" & txtBalance.Text _
& "', " & "Cost = '" & txtCost.Text _
& "' WHERE Number = '" & txtNumber.Text & "'"

OleDbDataAdapter4.UpdateCommand.CommandText = command
lblSQL.Text = command

'do the update
OleDbDataAdapter4.UpdateCommand.ExecuteNonQuery()
Catch exceptionObject As Exception
MessageBox.Show(exceptionObject.Message)
Finally
OleDbConnection1.Close()
End Try
End Sub



'INSERT BOOKING RECORD
Private Sub btnInsertB_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsertB.Click

If MessageBox.Show("Do you wish to insert a new Booking Record",
"Insert", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) =
DialogResult.Yes Then
End If

Try
OleDbConnection1.Open()
Dim command As String
'set up an SQL insert
command = "INSERT INTO BOOKINGS(Number, Date, Adult, Child,
Deposit, Balance, Cost )" _
& " values('" & txtNumber.Text & "', '" & txtDate.Text &
"','" & txtAdult.Text & "','" & txtChild.Text & "','" & txtDeposit.Text &
"','" & txtBalance.Text & "','" & txtCost.Text & "')"


OleDbDataAdapter4.InsertCommand.CommandText = command
lblSQL.Text = command

' do the insert
OleDbDataAdapter4.InsertCommand.ExecuteNonQuery()
Catch exceptionObject As Exception
MessageBox.Show(exceptionObject.Message)
Finally
OleDbConnection1.Close()
End Try
End Sub


'DELETE BOOKING RECORD
Private Sub btnDeleteB_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDeleteB.Click

If MessageBox.Show("Do you wish to delete Booking Record",
"Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) =
DialogResult.Yes Then
End If

Try
OleDbConnection1.Open()
Dim command As String

'set up an SQL delete
command = "DELETE FROM BOOKINGS WHERE Number = '" &
txtNumber.Text & "';"

OleDbDataAdapter4.DeleteCommand.CommandText = command
lblSQL.Text = command

'do the delete
OleDbDataAdapter4.DeleteCommand.ExecuteNonQuery()
Catch exceptionObject As Exception
MessageBox.Show(exceptionObject.Message)
Finally
OleDbConnection1.Close()
End Try
End Sub


I think I need either ALTER type commands or CASCADING type commands on
each???

Will any changes need to be made to the OleDbDataAdaptor or the
OleDbDataConnection via Visual Studio?

Can anybody help? I’m in despair (;-((

 

[Peter van der Goes]

BOOKINGS has a Primary key: Number. Two Foreign keys: UCI (link to
CUSTOMERS) & Code (link to HOLIDAYS).

HOLIDAYS has a Primary Key: Code.

The Database has one-to-many Relationships with Referential Integrity and
checked cascade UPDATE and DELETE (The many relationship for both links
being on the BOOKINGS table).

I have designed a GUI using vb.NET (Visual Studio) The connection to the
Database is via Microsoft Jet 4.0 OLE DB Provider and I have got the
following Datasets to work on both the CUSTOMERS and HOLIDAYS tables:
SELECT, INSERT, DELETE, UPDATE records. On coming to the BOOKINGS table
(which appears to be an INNER JOIN) I've got the SELECT and DELETE records
to work, but I'm having major problems getting the INSERT and UPDATE to
work. View the coding below:

Can you provide the complete text of the error messages generated when you
attempt to insert and update using this code? Thanks.