[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Refreshing Data from Microsoft Acess in ADO.Net

Posted on 2007-10-04
8
Medium Priority
?
223 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
0
Comment
Question by:kiranboi
  • 5
  • 3
8 Comments
 
LVL 20

Expert Comment

by:ElrondCT
ID: 20015628
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
0
 

Author Comment

by:kiranboi
ID: 20020208
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?
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 20022065
"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()
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kiranboi
ID: 20032919
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
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 20034223
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?
0
 
LVL 20

Accepted Solution

by:
ElrondCT earned 1500 total points
ID: 20034375
Oh, you should also put

   myUsersDataTable.AcceptChanges()

at the end of your update code, so it doesn't try to repeatedly add the new records.
0
 

Author Comment

by:kiranboi
ID: 20039365
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
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 20040522
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

834 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