Inserting a record is generating an error

T

Tony K

VB 2005.
I receive the error, "You cannot add or change a record because a related
record is required in table 'Purchase Orders'."

In my form, the parent record Purchase Orders is in details view. The child
record(s) is in GridView.

Table: Purchase Orders fields:
PK - PurchaseOrderID
PurchaseOrderNumber
PurchaseOrderDescription
SupplierID ->Relation Only to Suppliers Table
EmployeeID -> Relation Only to Employees Table
OrderDate
DateRequired
ShipDate
ShippingMethodID -> Relation Only to Shipping Table
FreightCharge
PurchaseOrderClosed
DateReceived

Table: Inventory Transactions fields
PK - TransactionID
TransactionDate
ProductID -> Relation Only to Products Table
PurchaseOrderID -> Relation Only to Purchase Orders Table
TransactionDescription
UnitPrice
UnitsOrdered
UnitsReceived
UnitsSold
UnitsShrinkage
Equipment -> Relation Only to Equipment Table

********ALL FIELDS EXCEPT PRIMARY KEYS ALLOW DBNULL VALUES*************

With the Purchase Orders Table empty, when I create the very first record, I
receive the error message above. If I EXIT the program and re-enter, the
parent table info is there (Purchase Orders info) but the Inventory
Transactions info is missing. If I then re-enter the Inventory Transactions
in the DataGridView, it will save.

I have now run into the same problem but NO WORK-AROUND. I have hard coded
the info into a completely different form (really sloppy I'm sure) but I get
the same results. The code is below and I have marked where the error
occurs.

Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, poID As Integer
Dim supplier As String = "", productDescription As String = ""
supplier =
CStr(Me.POGenerateDataGridView.Rows(0).Cells("SupplierName").Value)

strSQL = "SELECT SupplierID FROM Suppliers WHERE SupplierName = '" &
supplier & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
supplierID = CInt(drdTest.Item(0))
Loop

Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
Nothing, supplierID, Nothing, Date.Today(), Today.AddDays(5), Nothing,
Nothing, Nothing, False, Nothing)
Me.Purchase_OrdersTableAdapter.Update(Me.Inventory_management_databaseDataSet)
For Each row In POGenerateDataGridView.Rows
'supplier = CStr(row.Cells("SupplierName").Value)
'MessageBox.Show("Supplier = " & supplier & vbCrLf & "Row count
= " & Me.POGenerateDataGridView.Rows.Count.ToString, "Test")

If supplier <> row.Cells("SupplierName").Value.ToString Then
Exit For
End If

strSQL = "SELECT * FROM Products WHERE ProductIDNumber = '" &
row.Cells("ProductIDNumber").Value.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
productID = CInt(drdTest.Item("ProductID"))
productDescription =
CStr(drdTest.Item("ProductDescription"))
Loop

strSQL = "SELECT PurchaseOrderID FROM [Purchase Orders] WHERE
PurchaseOrderID = " & recordCounter.ToString
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CInt(drdTest.Item(0))
Loop

'*********************ERROR OCCURS AT THE INSERT STATEMENT
BELOW*************
Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, poID, productDescription, Nothing, _
Nothing, Nothing, Nothing, Nothing,
Nothing)

supplier = row.Cells("SupplierName").Value.ToString


Next


The insert statement for the Access DB is:
INSERT INTO `Inventory Transactions` (`TransactionDate`, `ProductID`,
`PurchaseOrderID`, `TransactionDescription`, `UnitPrice`, `UnitsOrdered`,
`UnitsReceived`, `UnitsSold`, `UnitsShrinkage`, `Equipment`) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?)

Thanks to all,

Tony K
 
C

Cor Ligthert[MVP]

Tony,

You are sure that there a row with that productID and a row with that
PurchaseOrderID exist?

Cor

Tony K said:
VB 2005.
I receive the error, "You cannot add or change a record because a related
record is required in table 'Purchase Orders'."

In my form, the parent record Purchase Orders is in details view. The
child record(s) is in GridView.

Table: Purchase Orders fields:
PK - PurchaseOrderID
PurchaseOrderNumber
PurchaseOrderDescription
SupplierID ->Relation Only to Suppliers Table
EmployeeID -> Relation Only to Employees Table
OrderDate
DateRequired
ShipDate
ShippingMethodID -> Relation Only to Shipping Table
FreightCharge
PurchaseOrderClosed
DateReceived

Table: Inventory Transactions fields
PK - TransactionID
TransactionDate
ProductID -> Relation Only to Products Table
PurchaseOrderID -> Relation Only to Purchase Orders Table
TransactionDescription
UnitPrice
UnitsOrdered
UnitsReceived
UnitsSold
UnitsShrinkage
Equipment -> Relation Only to Equipment Table

********ALL FIELDS EXCEPT PRIMARY KEYS ALLOW DBNULL VALUES*************

With the Purchase Orders Table empty, when I create the very first record,
I receive the error message above. If I EXIT the program and re-enter,
the parent table info is there (Purchase Orders info) but the Inventory
Transactions info is missing. If I then re-enter the Inventory
Transactions in the DataGridView, it will save.

I have now run into the same problem but NO WORK-AROUND. I have hard
coded the info into a completely different form (really sloppy I'm sure)
but I get the same results. The code is below and I have marked where the
error occurs.

Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, poID As Integer
Dim supplier As String = "", productDescription As String = ""
supplier =
CStr(Me.POGenerateDataGridView.Rows(0).Cells("SupplierName").Value)

strSQL = "SELECT SupplierID FROM Suppliers WHERE SupplierName = '"
& supplier & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
supplierID = CInt(drdTest.Item(0))
Loop

Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
Nothing, supplierID, Nothing, Date.Today(), Today.AddDays(5), Nothing,
Nothing, Nothing, False, Nothing)

Me.Purchase_OrdersTableAdapter.Update(Me.Inventory_management_databaseDataSet)
For Each row In POGenerateDataGridView.Rows
'supplier = CStr(row.Cells("SupplierName").Value)
'MessageBox.Show("Supplier = " & supplier & vbCrLf & "Row count
= " & Me.POGenerateDataGridView.Rows.Count.ToString, "Test")

If supplier <> row.Cells("SupplierName").Value.ToString Then
Exit For
End If

strSQL = "SELECT * FROM Products WHERE ProductIDNumber = '" &
row.Cells("ProductIDNumber").Value.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
productID = CInt(drdTest.Item("ProductID"))
productDescription =
CStr(drdTest.Item("ProductDescription"))
Loop

strSQL = "SELECT PurchaseOrderID FROM [Purchase Orders] WHERE
PurchaseOrderID = " & recordCounter.ToString
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CInt(drdTest.Item(0))
Loop

'*********************ERROR OCCURS AT THE INSERT STATEMENT
BELOW*************
Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, poID, productDescription, Nothing, _
Nothing, Nothing, Nothing, Nothing,
Nothing)

supplier = row.Cells("SupplierName").Value.ToString


Next


The insert statement for the Access DB is:
INSERT INTO `Inventory Transactions` (`TransactionDate`, `ProductID`,
`PurchaseOrderID`, `TransactionDescription`, `UnitPrice`, `UnitsOrdered`,
`UnitsReceived`, `UnitsSold`, `UnitsShrinkage`, `Equipment`) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?)

Thanks to all,

Tony K
Click to expand...
 
T

Tony K

Cor,

Yes, I am sure. I have opened the Access DB and verified the correct
info is there.


Cor Ligthert said:
Tony,

You are sure that there a row with that productID and a row with that
PurchaseOrderID exist?

Cor

Tony K said:
VB 2005.
I receive the error, "You cannot add or change a record because a related
record is required in table 'Purchase Orders'."

In my form, the parent record Purchase Orders is in details view. The
child record(s) is in GridView.

Table: Purchase Orders fields:
PK - PurchaseOrderID
PurchaseOrderNumber
PurchaseOrderDescription
SupplierID ->Relation Only to Suppliers Table
EmployeeID -> Relation Only to Employees Table
OrderDate
DateRequired
ShipDate
ShippingMethodID -> Relation Only to Shipping Table
FreightCharge
PurchaseOrderClosed
DateReceived

Table: Inventory Transactions fields
PK - TransactionID
TransactionDate
ProductID -> Relation Only to Products Table
PurchaseOrderID -> Relation Only to Purchase Orders Table
TransactionDescription
UnitPrice
UnitsOrdered
UnitsReceived
UnitsSold
UnitsShrinkage
Equipment -> Relation Only to Equipment Table

********ALL FIELDS EXCEPT PRIMARY KEYS ALLOW DBNULL VALUES*************

With the Purchase Orders Table empty, when I create the very first
record, I receive the error message above. If I EXIT the program and
re-enter, the parent table info is there (Purchase Orders info) but the
Inventory Transactions info is missing. If I then re-enter the Inventory
Transactions in the DataGridView, it will save.

I have now run into the same problem but NO WORK-AROUND. I have hard
coded the info into a completely different form (really sloppy I'm sure)
but I get the same results. The code is below and I have marked where
the error occurs.

Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, poID As Integer
Dim supplier As String = "", productDescription As String = ""
supplier =
CStr(Me.POGenerateDataGridView.Rows(0).Cells("SupplierName").Value)

strSQL = "SELECT SupplierID FROM Suppliers WHERE SupplierName = '"
& supplier & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
supplierID = CInt(drdTest.Item(0))
Loop

Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
Nothing, supplierID, Nothing, Date.Today(), Today.AddDays(5), Nothing,
Nothing, Nothing, False, Nothing)

Me.Purchase_OrdersTableAdapter.Update(Me.Inventory_management_databaseDataSet)
For Each row In POGenerateDataGridView.Rows
'supplier = CStr(row.Cells("SupplierName").Value)
'MessageBox.Show("Supplier = " & supplier & vbCrLf & "Row
count = " & Me.POGenerateDataGridView.Rows.Count.ToString, "Test")

If supplier <> row.Cells("SupplierName").Value.ToString Then
Exit For
End If

strSQL = "SELECT * FROM Products WHERE ProductIDNumber = '" &
row.Cells("ProductIDNumber").Value.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
productID = CInt(drdTest.Item("ProductID"))
productDescription =
CStr(drdTest.Item("ProductDescription"))
Loop

strSQL = "SELECT PurchaseOrderID FROM [Purchase Orders] WHERE
PurchaseOrderID = " & recordCounter.ToString
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CInt(drdTest.Item(0))
Loop

'*********************ERROR OCCURS AT THE INSERT STATEMENT
BELOW*************
Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, poID, productDescription, Nothing, _
Nothing, Nothing, Nothing, Nothing,
Nothing)

supplier = row.Cells("SupplierName").Value.ToString


Next


The insert statement for the Access DB is:
INSERT INTO `Inventory Transactions` (`TransactionDate`, `ProductID`,
`PurchaseOrderID`, `TransactionDescription`, `UnitPrice`, `UnitsOrdered`,
`UnitsReceived`, `UnitsSold`, `UnitsShrinkage`, `Equipment`) VALUES (?,
?, ?, ?, ?, ?, ?, ?, ?, ?)

Thanks to all,

Tony K
Click to expand...
Click to expand...
 
C

Cor Ligthert[MVP]

Tony,

This is always hard to answer, why are you not simple using DataSets or
something liike that, than in becomes visible what you are doing?

Cor


Tony K said:
Cor,

Yes, I am sure. I have opened the Access DB and verified the correct
info is there.


Cor Ligthert said:
Tony,

You are sure that there a row with that productID and a row with that
PurchaseOrderID exist?

Cor

Tony K said:
VB 2005.
I receive the error, "You cannot add or change a record because a
related record is required in table 'Purchase Orders'."

In my form, the parent record Purchase Orders is in details view. The
child record(s) is in GridView.

Table: Purchase Orders fields:
PK - PurchaseOrderID
PurchaseOrderNumber
PurchaseOrderDescription
SupplierID ->Relation Only to Suppliers Table
EmployeeID -> Relation Only to Employees Table
OrderDate
DateRequired
ShipDate
ShippingMethodID -> Relation Only to Shipping Table
FreightCharge
PurchaseOrderClosed
DateReceived

Table: Inventory Transactions fields
PK - TransactionID
TransactionDate
ProductID -> Relation Only to Products Table
PurchaseOrderID -> Relation Only to Purchase Orders Table
TransactionDescription
UnitPrice
UnitsOrdered
UnitsReceived
UnitsSold
UnitsShrinkage
Equipment -> Relation Only to Equipment Table

********ALL FIELDS EXCEPT PRIMARY KEYS ALLOW DBNULL VALUES*************

With the Purchase Orders Table empty, when I create the very first
record, I receive the error message above. If I EXIT the program and
re-enter, the parent table info is there (Purchase Orders info) but the
Inventory Transactions info is missing. If I then re-enter the
Inventory Transactions in the DataGridView, it will save.

I have now run into the same problem but NO WORK-AROUND. I have hard
coded the info into a completely different form (really sloppy I'm sure)
but I get the same results. The code is below and I have marked where
the error occurs.

Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, poID As Integer
Dim supplier As String = "", productDescription As String = ""
supplier =
CStr(Me.POGenerateDataGridView.Rows(0).Cells("SupplierName").Value)

strSQL = "SELECT SupplierID FROM Suppliers WHERE SupplierName =
'" & supplier & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
supplierID = CInt(drdTest.Item(0))
Loop

Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
Nothing, supplierID, Nothing, Date.Today(), Today.AddDays(5), Nothing,
Nothing, Nothing, False, Nothing)

Me.Purchase_OrdersTableAdapter.Update(Me.Inventory_management_databaseDataSet)
For Each row In POGenerateDataGridView.Rows
'supplier = CStr(row.Cells("SupplierName").Value)
'MessageBox.Show("Supplier = " & supplier & vbCrLf & "Row
count = " & Me.POGenerateDataGridView.Rows.Count.ToString, "Test")

If supplier <> row.Cells("SupplierName").Value.ToString Then
Exit For
End If

strSQL = "SELECT * FROM Products WHERE ProductIDNumber = '" &
row.Cells("ProductIDNumber").Value.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
productID = CInt(drdTest.Item("ProductID"))
productDescription =
CStr(drdTest.Item("ProductDescription"))
Loop

strSQL = "SELECT PurchaseOrderID FROM [Purchase Orders] WHERE
PurchaseOrderID = " & recordCounter.ToString
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CInt(drdTest.Item(0))
Loop

'*********************ERROR OCCURS AT THE INSERT STATEMENT
BELOW*************
Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, poID, productDescription, Nothing, _
Nothing, Nothing, Nothing, Nothing,
Nothing)

supplier = row.Cells("SupplierName").Value.ToString


Next


The insert statement for the Access DB is:
INSERT INTO `Inventory Transactions` (`TransactionDate`, `ProductID`,
`PurchaseOrderID`, `TransactionDescription`, `UnitPrice`,
`UnitsOrdered`, `UnitsReceived`, `UnitsSold`, `UnitsShrinkage`,
`Equipment`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Thanks to all,

Tony K
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Gerrard

Tony said:
Cor,

Yes, I am sure. I have opened the Access DB and verified the
correct info is there.
Click to expand...

That just means the PurchaseOrderID you think you are using is there.

If the error occurs in this line from your original post:

then you need to put a debug statement in front of that, and print out what the
value of productID and poID really are at the time of the insert. I bet a nickel
they are not what you think they are, and that there is no purchase order record
with that poID in the database at the time of the insert.
 
T

Tony K

I found my problem Steve. I was looking for PurchaseOrderNumber not
PurchaseOrderID. I wanted the PurchaseOrderID to be returned where the
number was = to my temporary number I set earlier in the code.

Thanks,

Tony K
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

*Scratching My Head* - I need help 4
DataGridView causing problems 3
1st record added to table crashes my program 4
Subform Combo Record Source Issue 1
At a Loss - Need some code help please 3

Top