*Scratching My Head* - I need help

  • Thread starter Thread starter Tony K
  • Start date Start date
T

Tony K

I know I have asked a lot of questions here, but this one might puzzle
others.

I have a query that gets all products whose OnHand < MinRequired and grouped
by the supplier. The query fills perfectly.
From that, I autogenerate a Purchase Order and for each supplier, I add the
associated products.

When I execute the program without breakpoints, all the products are added
to the first created purchase order even though the products do not belong
to that supplier. Then, the other PO's are created but no products are
filled for the remaining suppliers.

IF I create a breakpoint where the supplier has changed, then continue, the
appropriate PO's are created with the correct products for each supplier.

Help, I don't know what is going on.

Entire GeneratePO_Click Routine:

***BEGINNING
Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim maximumOnHand As Integer, unitsOrdered As Integer
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990 'Temporary PO #
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, price As Decimal
Dim supplier As String = "", productDescription As String = "", poID
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, "Auto
Generated PO", 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

If supplier <> row.Cells("SupplierName").Value.ToString Then
'***If Supplier is different
supplier = CStr(row.Cells("SupplierName").Value) '***If I
perform breakpoint here...IT WORKS.
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
recordCounter += 1 'Increment Temporary PO# for new PO.

Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
"Auto Generated PO", supplierID, _
Nothing, Date.Today(), Today.AddDays(5),
Nothing, Nothing, Nothing, False, Nothing)

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"))
maximumOnHand = CInt(drdTest.Item("MinimumRequired"))
price = CDec(drdTest.Item("UnitPrice"))
Loop

strSQL = "SELECT SUM(UnitsReceived) - SUM(UnitsSold) -
SUM(UnitsShrinkage) " & _
"FROM [Inventory Transactions]" & _
"WHERE (ProductID = " & productID & ")"

cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
unitsOrdered = maximumOnHand - CInt(drdTest.Item(0))
Loop
cmmInvMan.Dispose()
drdTest.Close()
strSQL = "SELECT * FROM [Purchase Orders] WHERE
PurchaseOrderNumber = '" & recordCounter.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CStr(drdTest.Item("PurchaseOrderID"))
Loop

Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, CType(poID, Integer), productDescription, price, _
unitsOrdered, Nothing, Nothing, Nothing,
Nothing)

Next

*****END

Thanks,
Tony K.
 
Probably need to use different recordsets. I had a problem with a vb6 app
that was solved my not mixing sql in the same recordset

cmmInv1
cmmInv2
cmmInv3

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
Tony K said:
I know I have asked a lot of questions here, but this one might puzzle
others.

I have a query that gets all products whose OnHand < MinRequired and
grouped by the supplier. The query fills perfectly.
From that, I autogenerate a Purchase Order and for each supplier, I add
the associated products.

When I execute the program without breakpoints, all the products are added
to the first created purchase order even though the products do not belong
to that supplier. Then, the other PO's are created but no products are
filled for the remaining suppliers.

IF I create a breakpoint where the supplier has changed, then continue,
the appropriate PO's are created with the correct products for each
supplier.

Help, I don't know what is going on.

Entire GeneratePO_Click Routine:

***BEGINNING
Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim maximumOnHand As Integer, unitsOrdered As Integer
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990 'Temporary PO #
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, price As Decimal
Dim supplier As String = "", productDescription As String = "",
poID 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, "Auto
Generated PO", 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

If supplier <> row.Cells("SupplierName").Value.ToString Then
'***If Supplier is different
supplier = CStr(row.Cells("SupplierName").Value) '***If I
perform breakpoint here...IT WORKS.
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
recordCounter += 1 'Increment Temporary PO# for new PO.


Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString, "Auto
Generated PO", supplierID, _
Nothing, Date.Today(), Today.AddDays(5),
Nothing, Nothing, Nothing, False, Nothing)

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"))
maximumOnHand = CInt(drdTest.Item("MinimumRequired"))
price = CDec(drdTest.Item("UnitPrice"))
Loop

strSQL = "SELECT SUM(UnitsReceived) - SUM(UnitsSold) -
SUM(UnitsShrinkage) " & _
"FROM [Inventory Transactions]" & _
"WHERE (ProductID = " & productID & ")"

cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
unitsOrdered = maximumOnHand - CInt(drdTest.Item(0))
Loop
cmmInvMan.Dispose()
drdTest.Close()
strSQL = "SELECT * FROM [Purchase Orders] WHERE
PurchaseOrderNumber = '" & recordCounter.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CStr(drdTest.Item("PurchaseOrderID"))
Loop

Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, CType(poID, Integer), productDescription, price, _
unitsOrdered, Nothing, Nothing,
Nothing, Nothing)

Next

*****END

Thanks,
Tony K.
Click to expand...
 
Hmmm... Alright. I'll make the changes tomorrow at work.

Thanks David.

Tony K.

David Glienna said:
Probably need to use different recordsets. I had a problem with a vb6 app
that was solved my not mixing sql in the same recordset

cmmInv1
cmmInv2
cmmInv3

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
Tony K said:
I know I have asked a lot of questions here, but this one might puzzle
others.

I have a query that gets all products whose OnHand < MinRequired and
grouped by the supplier. The query fills perfectly.
From that, I autogenerate a Purchase Order and for each supplier, I add
the associated products.

When I execute the program without breakpoints, all the products are
added to the first created purchase order even though the products do not
belong to that supplier. Then, the other PO's are created but no
products are filled for the remaining suppliers.

IF I create a breakpoint where the supplier has changed, then continue,
the appropriate PO's are created with the correct products for each
supplier.

Help, I don't know what is going on.

Entire GeneratePO_Click Routine:

***BEGINNING
Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim maximumOnHand As Integer, unitsOrdered As Integer
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990 'Temporary PO #
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, price As Decimal
Dim supplier As String = "", productDescription As String = "",
poID 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,
"Auto Generated PO", 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

If supplier <> row.Cells("SupplierName").Value.ToString Then
'***If Supplier is different
supplier = CStr(row.Cells("SupplierName").Value) '***If I
perform breakpoint here...IT WORKS.
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
recordCounter += 1 'Increment Temporary PO# for new PO.


Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString, "Auto
Generated PO", supplierID, _
Nothing, Date.Today(), Today.AddDays(5),
Nothing, Nothing, Nothing, False, Nothing)

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"))
maximumOnHand = CInt(drdTest.Item("MinimumRequired"))
price = CDec(drdTest.Item("UnitPrice"))
Loop

strSQL = "SELECT SUM(UnitsReceived) - SUM(UnitsSold) -
SUM(UnitsShrinkage) " & _
"FROM [Inventory Transactions]" & _
"WHERE (ProductID = " & productID & ")"

cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
unitsOrdered = maximumOnHand - CInt(drdTest.Item(0))
Loop
cmmInvMan.Dispose()
drdTest.Close()
strSQL = "SELECT * FROM [Purchase Orders] WHERE
PurchaseOrderNumber = '" & recordCounter.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CStr(drdTest.Item("PurchaseOrderID"))
Loop

Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, CType(poID, Integer), productDescription, price, _
unitsOrdered, Nothing, Nothing,
Nothing, Nothing)

Next

*****END

Thanks,
Tony K.
Click to expand...
Click to expand...
 
I don't know why setting a breakpoint causes different results.
However, if in the failure case all products are added to the first
purchase order, that suggests that maybe the SELECT to fetch the
purchase order just before the insert of the product fails to fetch
any records. You should check for that case and do something, like
throw an exception.

There are a number of odd things about your code.

You have two sets of identical code to fetch supplierID given a
supplier, one done initially for the value in the DataGridView and the
other in the loop when supplier changes. It would be better to
initially set supplier to a value to a value that can't occur, like
"", and just let the code in the loop fetch supplierID for the first
record. You would also need to get rid of the insert of the purchase
order before the loop.

You have several sequences like:
Do While drdTest.Read
supplierID = CInt(drdTest.Item(0))
Loop
Click to expand...
This sets supplierID to the value from the last record fetched. I
presume that you only expect there to be one record returned, so why
bother with the loop? Also in each case you should check to see if
you don't get any records back.

You never close the Command objects.

You build string WHERE clause values into the SQL string. Unless you
can guarantee that the values of those strings don't come from user
input you are opening yourself to SQL injection attacks. It is better
to use parameters.

I know I have asked a lot of questions here, but this one might puzzle
others.

I have a query that gets all products whose OnHand < MinRequired and grouped
by the supplier. The query fills perfectly.
From that, I autogenerate a Purchase Order and for each supplier, I add the
associated products.

When I execute the program without breakpoints, all the products are added
to the first created purchase order even though the products do not belong
to that supplier. Then, the other PO's are created but no products are
filled for the remaining suppliers.

IF I create a breakpoint where the supplier has changed, then continue, the
appropriate PO's are created with the correct products for each supplier.

Help, I don't know what is going on.

Entire GeneratePO_Click Routine:

***BEGINNING
Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim maximumOnHand As Integer, unitsOrdered As Integer
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990 'Temporary PO #
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, price As Decimal
Dim supplier As String = "", productDescription As String = "", poID
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, "Auto
Generated PO", 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

If supplier <> row.Cells("SupplierName").Value.ToString Then
'***If Supplier is different
supplier = CStr(row.Cells("SupplierName").Value) '***If I
perform breakpoint here...IT WORKS.
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
recordCounter += 1 'Increment Temporary PO# for new PO.

Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
"Auto Generated PO", supplierID, _
Nothing, Date.Today(), Today.AddDays(5),
Nothing, Nothing, Nothing, False, Nothing)

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"))
maximumOnHand = CInt(drdTest.Item("MinimumRequired"))
price = CDec(drdTest.Item("UnitPrice"))
Loop

strSQL = "SELECT SUM(UnitsReceived) - SUM(UnitsSold) -
SUM(UnitsShrinkage) " & _
"FROM [Inventory Transactions]" & _
"WHERE (ProductID = " & productID & ")"

cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
unitsOrdered = maximumOnHand - CInt(drdTest.Item(0))
Loop
cmmInvMan.Dispose()
drdTest.Close()
strSQL = "SELECT * FROM [Purchase Orders] WHERE
PurchaseOrderNumber = '" & recordCounter.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CStr(drdTest.Item("PurchaseOrderID"))
Loop

Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, CType(poID, Integer), productDescription, price, _
unitsOrdered, Nothing, Nothing, Nothing,
Nothing)

Next

*****END

Thanks,
Tony K.
Click to expand...
 
Jack,
Thank you for your advice. I will try the recommendations you have
provided. Lastnight before this message came in, I coded a MessageBox each
time a new PO is created letting the user know that a new PO was created for
"XYZ Company". Because there will usually be more than one PO created and
each PO has one Supplier, when a new supplier is detected within the For
Loop, I display a new MessageBox showing that a new PO was created for that
supplier.

Again, I don't get why this works but because there is a pause (when the
user reads the message box) each PO is created successfully.

Thank you for your help,
Tony K.


Jack Jackson said:
I don't know why setting a breakpoint causes different results.
However, if in the failure case all products are added to the first
purchase order, that suggests that maybe the SELECT to fetch the
purchase order just before the insert of the product fails to fetch
any records. You should check for that case and do something, like
throw an exception.

There are a number of odd things about your code.

You have two sets of identical code to fetch supplierID given a
supplier, one done initially for the value in the DataGridView and the
other in the loop when supplier changes. It would be better to
initially set supplier to a value to a value that can't occur, like
"", and just let the code in the loop fetch supplierID for the first
record. You would also need to get rid of the insert of the purchase
order before the loop.

You have several sequences like:
Do While drdTest.Read
supplierID = CInt(drdTest.Item(0))
Loop
Click to expand...
This sets supplierID to the value from the last record fetched. I
presume that you only expect there to be one record returned, so why
bother with the loop? Also in each case you should check to see if
you don't get any records back.

You never close the Command objects.

You build string WHERE clause values into the SQL string. Unless you
can guarantee that the values of those strings don't come from user
input you are opening yourself to SQL injection attacks. It is better
to use parameters.

I know I have asked a lot of questions here, but this one might puzzle
others.

I have a query that gets all products whose OnHand < MinRequired and
grouped
by the supplier. The query fills perfectly.
From that, I autogenerate a Purchase Order and for each supplier, I add
the
associated products.

When I execute the program without breakpoints, all the products are added
to the first created purchase order even though the products do not belong
to that supplier. Then, the other PO's are created but no products are
filled for the remaining suppliers.

IF I create a breakpoint where the supplier has changed, then continue,
the
appropriate PO's are created with the correct products for each supplier.

Help, I don't know what is going on.

Entire GeneratePO_Click Routine:

***BEGINNING
Dim row As DataGridViewRow
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim maximumOnHand As Integer, unitsOrdered As Integer
Dim drdTest As OleDbDataReader
Dim recordCounter As Integer = 999990 'Temporary PO #
Dim cnnInvMan As OleDbConnection = New
OleDbConnection(My.Settings.Inventory_management_databaseConnectionString)
cnnInvMan.Open()
Dim supplierID As Integer, productID As Integer, price As Decimal
Dim supplier As String = "", productDescription As String = "",
poID
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,
"Auto
Generated PO", 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

If supplier <> row.Cells("SupplierName").Value.ToString Then
'***If Supplier is different
supplier = CStr(row.Cells("SupplierName").Value) '***If I
perform breakpoint here...IT WORKS.
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
recordCounter += 1 'Increment Temporary PO# for new PO.


Me.Purchase_OrdersTableAdapter.Insert(recordCounter.ToString,
"Auto Generated PO", supplierID, _
Nothing, Date.Today(), Today.AddDays(5),
Nothing, Nothing, Nothing, False, Nothing)

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"))
maximumOnHand = CInt(drdTest.Item("MinimumRequired"))
price = CDec(drdTest.Item("UnitPrice"))
Loop

strSQL = "SELECT SUM(UnitsReceived) - SUM(UnitsSold) -
SUM(UnitsShrinkage) " & _
"FROM [Inventory Transactions]" & _
"WHERE (ProductID = " & productID & ")"

cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
unitsOrdered = maximumOnHand - CInt(drdTest.Item(0))
Loop
cmmInvMan.Dispose()
drdTest.Close()
strSQL = "SELECT * FROM [Purchase Orders] WHERE
PurchaseOrderNumber = '" & recordCounter.ToString & "'"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
poID = CStr(drdTest.Item("PurchaseOrderID"))
Loop

Me.Inventory_TransactionsTableAdapter.Insert(Date.Today(),
productID, CType(poID, Integer), productDescription, price, _
unitsOrdered, Nothing, Nothing,
Nothing,
Nothing)

Next

*****END

Thanks,
Tony K.
Click to expand...
Click to expand...
 
Back
Top