Changes not showing after ImportRow 1

[BFarley]

I'm selecting data from SQL Server and attempting to insert it into an Excel workbook, by using ImportRow to copy data between two datatables (

http://support.microsoft.com/kb/305346).

Both datatables are created ... and I'm able to append data from one datatable to the other. However, the changes don't appear in the Excel workbook.

            Using sqlConn As New SqlConnection(strConnSql)
                ' Connect to SQL database
                sqlConn.Open()

                Dim cmd As New SqlCommand()
                cmd.Connection = sqlConn

                ' Query AP 
                Dim strSqlAp As String = "SELECT * FROM SomeTable"

                'Create the DataAdapter.
                Dim da As SqlDataAdapter = New SqlDataAdapter(strSqlAp, sqlConn)
                'Fill the DataSet with data.
                da.Fill(ds, "AP")


                dtAp = ds.Tables("AP")
                intApRows = dtAp.Rows.Count

                Dim strOledbConnAp As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & strFileNameApProc & ";" & _
                   "Extended Properties=""Excel 8.0;HDR=YES"""

                Dim conn1 As New System.Data.OleDb.OleDbConnection(strOledbConnAp)
                conn1.Open()

                Dim da2 As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [AP$]", conn1)

                da2.Fill(ds2, "AP")

                dtApRemote = ds2.Tables("AP")

                MsgBox(dtApRemote.Rows.Count)

                For i As Integer = 0 To intApRows - 1
                    dtApRemote.ImportRow(dtAp.Rows(i))
                Next

                da2.Update(dtApRemote)
                MsgBox(dtApRemote.Rows.Count)

                conn1.Close()
            End Using

The first dtApRemote.Rows.Count returns 0, and the second returns 150. So records are being added, it appears.

I'm sure this is something small in the syntax, just can't find the right answer yet.

Thanks in advance, as always.

Bryant Farley

"The Dude Abides

 

[jebenson]

To save the added rows back to Excel you will need to set up the DataAdapter's InsertCommand with proper SQL, parameters, etc., before you call da2.Update.




I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[BFarley]

I created an InsertCommand, but still no luck. Perhaps I formed it incorrectly?

It's in between the 'BEGIN NEW CODE' and 'END NEW CODE' comments below ...

            Using sqlConn As New SqlConnection(strConnSql)
                ' Connect to SQL database
                sqlConn.Open()

                Dim cmd As New SqlCommand()
                cmd.Connection = sqlConn

                ' Query AP 
                Dim strSqlAp As String = "SELECT * FROM SOMETABLE"

                'Create the DataAdapter.
                Dim da As SqlDataAdapter = New SqlDataAdapter(strSqlAp, sqlConn)
                'Fill the DataSet with data.
                da.Fill(ds, "AP")


                dtAp = ds.Tables("AP")
                intApRows = dtAp.Rows.Count

                Dim strOledbConnAp As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & strFileNameApProc & ";" & _
                   "Extended Properties=""Excel 8.0;HDR=YES"""

                Dim conn1 As New System.Data.OleDb.OleDbConnection(strOledbConnAp)
                conn1.Open()

                Dim da2 As OleDbDataAdapter = New OleDbDataAdapter("SELECT GLAcctNo, GL_ACCT_DESCRIPTION, VOUCHER, INV_NO, VENDNO, VENDOR_NAME, [DATE], AMT FROM [AP$]", conn1)

                ' BEGIN NEW CODE 
                Dim cmdInsert As OleDbCommand = New OleDbCommand("INSERT INTO [AP$] (GLAcctNo, GL_ACCT_DESCRIPTION, VOUCHER, INV_NO, VENDNO, VENDOR_NAME, [DATE], AMT) " & _
                                     "VALUES (@GLAcctNo, @GL_ACCT_DESCRIPTION, @VOUCHER, @INV_NO, @VENDNO, @VENDOR_NAME, @APDATE, @AMT)", conn1)

                cmdInsert.Parameters.Add("@GLAcctNo", OleDbType.Char, 10, "GLAcctNo")
                cmdInsert.Parameters.Add("@GL_ACCT_DESCRIPTION", OleDbType.VarChar, 30, "GL_ACCT_DESCRIPTION")
                cmdInsert.Parameters.Add("@VOUCHER", OleDbType.Decimal, 0, "VOUCHER")
                cmdInsert.Parameters.Add("@INV_NO", OleDbType.Char, 8, "INV_NO")
                cmdInsert.Parameters.Add("@VENDNO", OleDbType.Char, 6, "VENDNO")
                cmdInsert.Parameters.Add("@VENDOR_NAME", OleDbType.Char, 30, "VENDOR_NAME")
                cmdInsert.Parameters.Add("@APDATE", OleDbType.Decimal, 0, "[DATE]")
                cmdInsert.Parameters.Add("@AMT", OleDbType.Decimal, 0, "AMT")

                da2.InsertCommand = cmdInsert
                ' END NEW CODE 

                da2.Fill(ds2, "AP")

                dtApRemote = ds2.Tables("AP")

                MsgBox(dtApRemote.Rows.Count)

                For i As Integer = 0 To intApRows - 1
                    dtApRemote.ImportRow(dtAp.Rows(i))
                Next

                dtApRemote.AcceptChanges()
                da2.Update(dtApRemote)
                MsgBox(dtApRemote.Rows.Count)

                conn1.Close()
            End Using

Bryant Farley

"The Dude Abides

 

[jebenson]

This was not in your original post:

dtApRemote.AcceptChanges()
da2.Update(dtApRemote)

Calling AcceptChanges does just that...it marks all rows in the table as unchanged, so when you call Update is "sees" that there are no changed rows, so it does nothing.

Remove the AcceptChanges and just call Update.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[BFarley]

Ah, good catch! I was tinkering with code and left that in by accident.

But it still won't cooperate after I remove it.

I have code immediately after this which opens the Excel workbook into which the data were inserted, and it's still blank.

Could it be something with the Insert command?

Bryant Farley

"The Dude Abides

 

[BFarley]

Additional link for some info I'm using:

http://support.microsoft.com/default.aspx?scid=kb;

[LN]%3BQ316934

(See "Use the InsertCommand object to add new records")

I hate when something fails silently -- I'd rather it be loud and obvious

Bryant Farley

"The Dude Abides

 

[jebenson]

OK, I know what the issue is.

Calling ImportRow preserves the existing RowState of the inported row. So since all of your imported rows have a RowState of Unchanged, all of the rows in the dtApRemote datatable will also show as Unchanged, so nothing will be done during the Update.

Offhand I'm not sure of a workaround other than calling NewRow on dtApRemote and adding each row that way, which will set the RowState to Added. I will keep thinking on another solution.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[BFarley]

Works like a charm, my friend -- you were absolutely correct!

Would this work ... create an UpdateCommand, and set a column's value to itself in each row (triggering RowState to change)? Of course that's touching each row multiple times, which isn't terribly efficient.

I've read that ImportRow is faster on larger data sets compared to NewRow, so that's why I've been focusing on that route.

Bryant Farley

"The Dude Abides

 

[jebenson]

Here's the solution, and you can continue to use ImportRow.

Before you fill your dataset with the data to be imported, set the dataadapter's AcceptChangesDuringFill property to False:

da2.AcceptChangesDuringFill = False

da2.Fill(ds2, "AP")

dtApRemote = ds2.Tables("AP")

Now when the fill occurs, all of the rows in ds2.Tables("AP") will have a DataRowState of Added, which will carry over to dtApRemote with ImportRow. The insert into Excel should then work.

Give it a try and let me know how it works out.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[BFarley]

Doesn't seem to work on da2. dtApRemote receives the data, but the Excel workbook doesn't show it.

But it does work if I apply AcceptChangesDuringFill to da (source data).

And fast!

I appreciate all your help!

Bryant Farley

"The Dude Abides