Solved

Get the row number from an access 97 database

Posted on 2004-04-06
3
432 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
0
Comment
Question by:d2beetle
  • 2
3 Comments
 
LVL 12

Expert Comment

by:dfiala13
ID: 10771678
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
 

Author Comment

by:d2beetle
ID: 10771958
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
 
LVL 12

Accepted Solution

by:
dfiala13 earned 500 total points
ID: 10774909
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now