CommandBuilder & Update

[george baburanos]

The problem is that when I edit an insert
command generated by the cb resets the commandtext
whenever I do an update with the related DataAdapter.

<VB CODE>

sqlc = New SqlClient.SqlConnection(sConnection)
da = New SqlClient.SqlDataAdapter(sSelect, sqlc)
ds = New DataSet
cb = New SqlClient.SqlCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

'THIS LINE NOT WORKS ON UPDATE !!!!
da.InsertCommand.CommandText&=" ; SELECT * FROM General WHERE
GeneralID=SCOPE_IDENTITY()"

da.Fill(ds)


'new rows are added here


da.Update(ds)
da.Fill(ds)

</VB CODE>

If I request the da.InsertCommand.CommandText after the update,
the part I added (with scope_identity) is nowhere to be found. In
fact, if I change the InsertCommand's command text to something
invalid before the update, then the Update just ignores it (most
likely because the CommandBuilder resets the commandtext to its
original value in the process).

Any ideas to make this work?


Thank you
George

 

[William Ryan]

The SELECT statement for an Insert Command ,I don't think will ever work.
I'm 90% sure on this, but once the CommandBuilder is associated with the
DataAdapter, it's going to use the CommandBuilder's statements. Since the
CB generates logic based on the Select statement, I think the only way to
change the update logic is to change the select statement. However, if you
are rolling your own update/insert logic, then why not use it across the
board and ditch the commandbuilder?

HTH,

Bill