CommandBuilder InsertCommand Update

[Guest]

Hi All!!!
Seasons Greetings....

I have read a lot of the post regarding CommandBuilder, InsertCommand and
Update. The code below works....

'This is a foxpro table that matches my MS SQL Table
fpDA = New OleDbDataAdapter("select * from table", GetConnection)
flat = New System.Data.DataSet()
fpDA.Fill(flat, "table")

Try

Dim cn As New Data.SqlClient.SqlConnection(ConnSqlClient)
cn.Open()

da = New Data.SqlClient.SqlDataAdapter("select * from table", cn)
cb = New Data.SqlClient.SqlCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.InsertCommand = cb.GetInsertCommand()

Dim p As Data.SqlClient.SqlParameter
Dim row As Data.DataRow
Dim col As Data.DataColumn
For Each row In flat.Tables("table").Rows
For Each p In da.InsertCommand.Parameters
p.Value = row.Item(p.SourceColumn)
Next
affectedRows += da.InsertCommand.ExecuteNonQuery
Next

Return affectedRows

Catch ex As Exception
MsgBox(ex.Source & vbCrLf & ex.Message)
End Try

There should be more documentation for the "Update" command from Microsoft.
It seems that if you just wanted to insert an unmodified table directly into
SQL server using CommandBuilder is a no go. Once again...there should be
documentation showing how to insert unmodified data (rowstate is unchanged)
into an SQL server database with out getting a headache and eye strain.

Markus McGee

 

[Miha Markic [MVP C#]]

There should be more documentation for the "Update" command from
Microsoft.
It seems that if you just wanted to insert an unmodified table directly
into
SQL server using CommandBuilder is a no go. Once again...there should be
documentation showing how to insert unmodified data (rowstate is
unchanged)
into an SQL server database with out getting a headache and eye strain.

Just set DataAdapter.AcceptChangesDuringFill = false before doing Fill - all
rows will be marked as New.

 

[W.G. Ryan eMVP]

Mark - the way an Adapter figures out what command to fire for a given row
is based solely on the RowState. If you have Rowstate of unchanged on
every row - update won't do anything.

If you want to pull this off though - you can set the
AcceptChangesDuringFill property to false - therefore causing ALL of the
rows to have a rowstate of Added after the fill. This way the insert
command will be fired against each row when you call update.

I don't think this will work with a CommandBuilder b/c they are lame - I
mean there is limited concurrency support with commandbuilders.

http://www.knowdotnet.com/articles/datasetmerge.html

 

[Guest]

You know what...

In my code I was changing the wrong AcceptChangesDuringFill statement.
Works like a charm now.

I still like my old ADO way of doing things though.

Markus McGee

 

[W.G. Ryan eMVP]

Give it some time - you'll learn to really love ADO.NET ;-)

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

You know what...

In my code I was changing the wrong AcceptChangesDuringFill statement.
Works like a charm now.

I still like my old ADO way of doing things though.

Markus McGee

Hi All!!!
Seasons Greetings....

I have read a lot of the post regarding CommandBuilder, InsertCommand and
Update. The code below works....

'This is a foxpro table that matches my MS SQL Table
fpDA = New OleDbDataAdapter("select * from table", GetConnection)
flat = New System.Data.DataSet()
fpDA.Fill(flat, "table")

Try

Dim cn As New Data.SqlClient.SqlConnection(ConnSqlClient)
cn.Open()

da = New Data.SqlClient.SqlDataAdapter("select * from table", cn)
cb = New Data.SqlClient.SqlCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.InsertCommand = cb.GetInsertCommand()

Dim p As Data.SqlClient.SqlParameter
Dim row As Data.DataRow
Dim col As Data.DataColumn
For Each row In flat.Tables("table").Rows
For Each p In da.InsertCommand.Parameters
p.Value = row.Item(p.SourceColumn)
Next
affectedRows += da.InsertCommand.ExecuteNonQuery
Next

Return affectedRows

Catch ex As Exception
MsgBox(ex.Source & vbCrLf & ex.Message)
End Try

There should be more documentation for the "Update" command from Microsoft.
It seems that if you just wanted to insert an unmodified table directly into
SQL server using CommandBuilder is a no go. Once again...there should be
documentation showing how to insert unmodified data (rowstate is unchanged)
into an SQL server database with out getting a headache and eye strain.

Markus McGee