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?
 
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
0
 
bchoorCommented:
Do you have a PK defined for your SoldTemp table? Sometimes, commandbuilder messes up if there is no primary key

HTH
~BC
0
 
Marc333Author Commented:
The primary key is an autonumber in the mdb file.  
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
0
 
Marc333Author Commented:
No change...
0
 
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.  
0
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.

All Courses

From novice to tech pro — start learning today.