Solved

Get the row number from an access 97 database

Posted on 2004-04-06
3
434 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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB 6.0 printer how to align 6 62
Add a task in Outlook from access 11 39
Spell Check in VB6 13 112
Added a column screws up code 5 57
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

773 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