Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Get the row number from an access 97 database

I need to get the row number from an access 97 database based on the primary key "Product ID". How can I do this?

I have only one record in my database currently, because I am testing my update function and I have hard coded row 0. It works, but now I need to populate the database and still be able to update individual records without creating duplicates.

Below is the update code that I used.

Private Sub btnSaveInventory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveInventory.Click

    Dim InsertRow, OnHandQty, tqs, tqr As Integer
    Dim tsc, tup As Double
    Dim ttd As Date
    'RowNumber is the database row to be selected for update
    RowNumber = 0 'This variable has been set to public

    Try

        If CInt(DataSet11.Tables(0).Rows(RowNumber).Item("Quantity")) = 0 Then
            OnHandQty = CInt(txtQtyRec.Text)
        Else
            OnHandQty = CInt(DataSet11.Tables(0).Rows(RowNumber).Item("Quantity")) + CInt(txtQtyRec.Text)
        End If

        If txtQtySold.Text = "" Then
            tqs = 0
        Else
            tqs = CInt(txtQtySold.Text)
        End If

        ttd = CDate(txtTransactionDate.Text)
        If txtQtyRec.Text = "" Then
            tqr = 0
       Else
            tqr = CInt(txtQtyRec.Text)
       End If

        tup = CDbl(txtUnitPrice.Text)
        txtTotalSalesCost.Text = CStr(tup * tqs)
        tsc = CDbl(txtTotalSalesCost.Text)
        txtSalesID.Text = "InvUpd"

        DataSet11.Tables(0).Rows(RowNumber).Item("ProductID") = txtProductID.Text
        DataSet11.Tables(0).Rows(RowNumber).Item("SalesID") = txtSalesID.Text
        DataSet11.Tables(0).Rows(RowNumber).Item("Quantity") = OnHandQty
        DataSet11.Tables(0).Rows(RowNumber).Item("Price") = tsc
        DataSet11.Tables(0).Rows(RowNumber).Item("DateSold") = ttd
        DataSet11.Tables(0).Rows(RowNumber).Item("QtySold") = tqs
        DataSet11.Tables(0).Rows(RowNumber).Item("QtyRecvd") = tqr
        DataSet11.Tables(0).Rows(RowNumber).Item("DateRecvd") = ttd

        InsertRow = OleDbDataAdapter1.Update(DataSet11)

    Catch ex As Exception
        'Catch Exception Errors
        MsgBox(ex.Message + Chr(13) + Chr(10) + Chr(10) + "File not found.")
    End Try

    MessageBox.Show("Number of rows Updated:" & InsertRow)
    'Set focus to the text box
    btnExit.Focus()

    txtTransactionDate.Text = CStr(Today())
    txtSalesID.Enabled() = False
    txtCustomerName.Enabled() = False
    txtProductID.Enabled() = False
    txtQtySold.Enabled() = False
    txtUnitPrice.Enabled() = False
    txtQtyRec.Enabled() = False
    btnSaveInventory.Enabled() = False
    btnInventoryList.Enabled() = False
    btnSaveSale.Enabled() = False
    btnRetrieveSales.Enabled() = False
    btnSalesTransaction.Enabled() = True
    btnInventoryTransaction.Enabled() = True
End Sub
0
Frank Bryant
Asked:
Frank Bryant
  • 2
1 Solution
 
dfiala13Commented:
You don't need to find the row number.  Find the row itself using a filter, and just work with it...

Dim drs as DataRow()
drs = DataSet11.Tables(0).Select("ProductID = " & id)

this will return an array with one row in it, get a reference to it...

Dim dr as DataRow = drs(0)

   Try

        If CInt(dr("Quantity")) = 0 Then
            OnHandQty = CInt(txtQtyRec.Text)
        Else
            OnHandQty = CInt(dr("Quantity")) + CInt(txtQtyRec.Text)
        End If

        If txtQtySold.Text = "" Then
            tqs = 0
        Else
            tqs = CInt(txtQtySold.Text)
        End If

        ttd = CDate(txtTransactionDate.Text)
        If txtQtyRec.Text = "" Then
            tqr = 0
       Else
            tqr = CInt(txtQtyRec.Text)
       End If

        tup = CDbl(txtUnitPrice.Text)
        txtTotalSalesCost.Text = CStr(tup * tqs)
        tsc = CDbl(txtTotalSalesCost.Text)
        txtSalesID.Text = "InvUpd"

        dr("ProductID") = txtProductID.Text
        dr("SalesID") = txtSalesID.Text
        dr("Quantity") = OnHandQty
        dr("Price") = tsc
        dr("DateSold") = ttd
        dr("QtySold") = tqs
        dr("QtyRecvd") = tqr
        dr("DateRecvd") = ttd

        InsertRow = OleDbDataAdapter1.Update(DataSet11)

    Catch ex As Exception
        'Catch Exception Errors
        MsgBox(ex.Message + Chr(13) + Chr(10) + Chr(10) + "File not found.")
    End Try

    MessageBox.Show("Number of rows Updated:" & InsertRow)
    'Set focus to the text box
    btnExit.Focus()

    txtTransactionDate.Text = CStr(Today())
    txtSalesID.Enabled() = False
    txtCustomerName.Enabled() = False
    txtProductID.Enabled() = False
    txtQtySold.Enabled() = False
    txtUnitPrice.Enabled() = False
    txtQtyRec.Enabled() = False
    btnSaveInventory.Enabled() = False
    btnInventoryList.Enabled() = False
    btnSaveSale.Enabled() = False
    btnRetrieveSales.Enabled() = False
    btnSalesTransaction.Enabled() = True
    btnInventoryTransaction.Enabled() = True
End Sub
0
 
Frank BryantJOATAuthor Commented:
dfiala13,

Thanks again, I did a copy and paste; however I had two errors to overcome using your suggestion.

1) drs = DataSet11.Tables(0).Select("ProductID = " & id) gave an error indicating that "id" was not declared; is id a string or integer? I tried both; results were inconclusive due to error number two.

2) I get the following error when I click the Update Button:
An unhandled exception of type 'System.NullReferenceException' occurred in SalesList.exe
Additional information: Object reference not set to an instance of an object.
It highlights line "Dim dr As DataRow = drs(0)".

So I moved your statements that were above the TRY to beneath it and then the CATCH reported this:
Object Reference Not Set to an Instance of an Object. File Not Found.

Any Ideas?
0
 
dfiala13Commented:
1) yes, you need to put in the name of your variable for setting the the key to, Id was just my guess.

2) related to the first error, if you never set a value you won't return rows.

Danger of cutting and pasting. ;)

Is btnSaveInventory on every row of your grid?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now