Improve company productivity with a Business Account.Sign Up

x
?
Solved

Get the row number from an access 97 database

Posted on 2004-04-06
3
Medium Priority
?
452 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:Frank Bryant
  • 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:Frank Bryant
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 1500 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

588 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