Dataset not updating

I'm having a real problem updating a dataset.  Here is the code below:

       Dim strsqlST = "SELECT * FROM SoldTemp;"
         Dim SoldstempDS1 As New DataSet()
         Dim OleDBDataAdapter5 As New OleDb.OleDbDataAdapter(strsqlST, objConnection)
         OleDBDataAdapter5.Fill(SoldstempDS1, "SoldTemp")

                Dim strsqlSQ = _
                    "SELECT * FROM(Solds) WHERE ((City) = 'Westland');"
         Dim OleDBDataAdapter4 As New OleDb.OleDbDataAdapter(strsqlSQ, objConnection)
         Dim SoldsDS1 As New DataSet()
         OleDBDataAdapter4.Fill(SoldsDS1, "Solds")
         Dim dr As DataRow
          Dim cb As New OleDbCommandBuilder()
          cb = New OleDbCommandBuilder(OleDBDataAdapter5)

                For Each dr In SoldsDS1.Tables(0).Rows
                    SoldstempDS1.Tables("SoldTemp").ImportRow(dr)
                Next
            OleDBDataAdapter5.Update(SoldstempDS1, "SoldTemp")
            SoldstempDS1.AcceptChanges()

As you can see, I've got a commandbuilder in there, but I'm not sure if its the syntax or something else.  For your reference, if I put MSGBOX(SoldsTempDS1.Tables(0).rows.Count), it gives me the correct number of added rows, but the updates never get posted.  

Any help would be greatly appreciated.  

Marc
LVL 3
Marc333Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bchoorCommented:
Do you have a PK defined for your SoldTemp table? Sometimes, commandbuilder messes up if there is no primary key

HTH
~BC
Marc333Author Commented:
The primary key is an autonumber in the mdb file.  
Fernando SotoRetiredCommented:
Hi Marc333;

Try moving this line in your code

     cb = New OleDbCommandBuilder(OleDBDataAdapter5)

To just after thins line of code

    Dim OleDBDataAdapter5 As New OleDb.OleDbDataAdapter(strsqlST, objConnection)

But before this line of code.

    OleDBDataAdapter5.Fill(SoldstempDS1, "SoldTemp")

Fernando
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Marc333Author Commented:
No change...
amyhxuCommented:
Calling ImportRow preserves the existing DataRowState, along with other values in the row. The rows you import from SoldDS1 have the "Unchanged" RowState. You can use DataTable.NewRow to add them, so that the RowState will be "Added" and the DataAdapter knows that the rows need to be inserted to database.

                Dim newRow As DataRow
                For Each dr In SoldsDS1.Tables(0).Rows
                    newRow = SoldstempDS1.Tables("SoldTemp").NewRow()
                    newRow.ItemArray = dr.ItemArray.Clone()
                    SoldstempDS1.Tables("SoldTemp").Rows.Add(newRow)
                Next

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marc333Author Commented:
amyhxu,
Sorry for the slow response but I've been out of town for the last week.  I hope to test your code in the next few days and if it looks good I'll award points.  Thanks for posting.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.