We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Get the row number from an access 97 database

Frank Bryant
Frank Bryant asked
on
Medium Priority
466 Views
Last Modified: 2007-12-19
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
Comment
Watch Question

Commented:
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

Author

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?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.