CommandBuilder InsertCommand Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
Click to expand...

Just set DataAdapter.AcceptChangesDuringFill = false before doing Fill - all
rows will be marked as New.
 
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
 
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
 
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
Markus McGee said:
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

Markus McGee said:
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
Click to expand...
Click to expand...
 
Back
Top