We help IT Professionals succeed at work.

Refreshing Data from Microsoft Acess in ADO.Net

kiranboi
kiranboi asked
on
236 Views
Last Modified: 2010-04-23
Hi all,
I'm using the following code to connect to an Access database in my app:

        myConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & AccessDataPath  & 0 "Central.mdb;"
        myConnection.ConnectionString = myConnectionString
        myConnection.Open()
        myUsersRecSet = New OleDb.OleDbDataAdapter("Select * From Users", myConnection)
        myUsersCommandBuilder = New OleDb.OleDbCommandBuilder(myUsersRecSet)
        myUsersCommandBuilder.QuotePrefix = "["
        myUsersCommandBuilder.QuoteSuffix = "]"
        myUsersRecSet.Fill(myUsersDataTable)
     
If I then create a new record all works fine however, my new record will also include an Acess auto generated number. If I try to find my new record and retrieve the auto generated number I get an error becuase according to my datatable its value is null. How can I quickly and easily refresh the Access data within my app so that it can see any changes that have been made outside my application?
Thanks
Comment
Watch Question

CERTIFIED EXPERT

Commented:
It looks to me like all you really need is the autonumber value. I got code for this from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp

It seems rather convoluted for such an essential function, but here's how I made it work for my needs (VB 2003, FWIW):

    Friend WithEvents odbconnData As System.Data.OleDb.OleDbConnection
    Friend WithEvents odbdaHeader As System.Data.OleDb.OleDbDataAdapter
    ' Define the Connection & DataAdapter....
    AddHandler odbdaHeader.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf HeaderUpdated)

   Public idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", odbconnData)
   Public gintLastID as Integer

    Private Sub HeaderUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
        ' Include a variable and a command to retrieve the identity value from the Access database.

        If args.StatementType = StatementType.Insert Then
            ' Retrieve the identity value and pass it back
            gintLastID = CInt(idCMD.ExecuteScalar())
        End If
    End Sub

And the actual code doing the update...

                Dim rowCurState As DataRowState = DataRowState.Unchanged
                If dsCurrent.Header.Count <> 0 Then
                    rowCurState = dsCurrent.Header(0).RowState
                    result = DA.odbdaHeader.Update(dsCurrent)
                    If rowCurState = DataRowState.Added Then
                        Dim drHeader As dsData.HeaderRow = dsDataA.Header.FindByID(dsCurrent.Header(0).ID)
                        ' Set the parent right, and the children follow
                        dsCurrent.Header(0).ID = gintLastID
                    End If

Author

Commented:
Ive tried using
myUsersRecSet.Fill(myUsersDataTable)
to repopulate the datatable with the updated data in Access including the newly created auto number but it isnt working. Any idea why?
CERTIFIED EXPERT

Commented:
"Isn't working" is a bit vague. It's easier to provide help when you provide details.

Have you tried setting breakpoints in your code in both the RowUpdated event handler and your update code to see if the Scalar command to get the value is working? What's in the autonumber field, both in your program (before and after you update the database) and in the database itself? Is the record in the Access database at all? It shouldn't be necessary to do a .Fill with this code in place.

I should mention that I'm using this code to get an autonumber value which is the primary key for the row. If your autonumber is not the primary key, I don't know if the code will work correctly.

If these ideas don't give you enough to work out what's going on, it would probably be good for you to post your code (both the RowUpdated event handler and your database updating code), so we can make sure my sample was grafted in properly, with appropriate variable references and the like.

I should have included at the beginning of my "actual code doing the update" the following lines, just to make sure you're getting the right information to put back to the database:

        Dim dsCurrent As dsData
        dsCurrent = CType(dsDataA.GetChanges, dsData)

and at the end of the update process:

         dsDataA.AcceptChanges()

Author

Commented:
The code I am using to connect to my Access database is shown in my original question. Below is the code I am using to create a new record in Access then try to find the new blank record and retrieve the Access generated Autonumber(which is the table's primary key)

 Dim myNewBlankRow As Data.DataRow = myUsersDataTable.NewRow
            myNewBlankRow("InvoiceTo") = ""
            myUsersDataTable.Rows.Add(myNewBlankRow)
            myUsersRecSet.Update(myUsersDataTable)
            For i As Integer = 0 To myUsersDataTable.Rows.Count - 1
                If myUsersDataTable.Rows(i)("InvoiceTo") = "" Then
                    myOrderNo = myUsersDataTable.Rows(i)("OrderNumber")
                    Exit For
                End If
            Next

It is finding the new record I have created but when I try and pull out the OrderNumber (which is the auto-number) is gives me an error saying it is DBNull. Even though it is not null in Access.

I hope this helps explain the problem I am having a little clearer
CERTIFIED EXPERT

Commented:
This code doesn't show any use of the RowUpdated event handler that I described in my first message. Did you try to merge the code I showed into your code?

In addition, if you're getting a DBNull error, it suggests that the definition of your internal DataTable doesn't include an autonumber reference. Therefore, you'll need to put a temporary value into the OrderNumber field when you create it, so that you can find the row to go back and put the Access-generated value in, unless there's another unique key that you can use to search on. In situations where I have to have a temporary key value, I keep a separate counter, initialized at a very high value that the autonumber generator will never reach (100,000,000 or something like that). So, for instance:

            Dim myNewBlankRow As Data.DataRow = myUsersDataTable.NewRow
            myNewBlankRow("OrderNumber") = intCounter
            intCounter += 1
            myNewBlankRow("InvoiceTo") = ""
            myUsersDataTable.Rows.Add(myNewBlankRow)
            myUsersRecSet.Update(myUsersDataTable)
            ' Following line calls on value generated by RowUpdated event
            myNewBlankRow("OrderNumber") = gintLastID

Then you need to defined the RowUpdated event handler:

   ' This goes at the beginning of your form - either in the New or the Form_Load
   AddHandler myUsersRecSet.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OrderUpdated)

   ' At the beginning of your form, or wherever you define your data connection.
   ' Replace "odbconnData" in next line with the name of your OleDBDataConnection.
   Public idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", odbconnData)
   Public gintLastID as Integer

    Private Sub OrderUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
        ' Include a variable and a command to retrieve the identity value from the Access database.

        If args.StatementType = StatementType.Insert Then
            ' Retrieve the identity value and pass it back
            gintLastID = CInt(idCMD.ExecuteScalar())
        End If
    End Sub

Does this make more sense?
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
sorry but that is way over my head. ive just closed the connection and reopened it to force a refresh of the data. ill accept your solution anyway because im sure your way would have worked if i had the experience to implement it
CERTIFIED EXPERT

Commented:
Well, yes, that will work, but means a lot of extra data accessing. If your database is small and you're doing only a limited amount of adding, it may not be a big deal.

As someone who is still very much a learner in VB, I can tell you that one of the ways I've been learning has been to dissect the answers I get here (I've asked as well as answered a lot of questions over the last year). To be sure, sometime's it's not worth the effort, and finding a way that works, even if it's not the most elegant, is good enough to keep you moving. But sometimes it's worth taking a little more time to learn more about how the language works to assist with both current and future needs. Best wishes in figuring out when each approach is best for you.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.