Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

SqlCommandBuilder Problems

I am using the much vaunted "SqlCommandBuilder" to automatically generate the delete / insert / update commands for some dynamic database table maintenance.

After setting the data adapter's selectcommand commandtext and connection objects, I create a new SqlCommandBuilder object thusly:

        Dim cbAnyCommand As SqlCommandBuilder = New SqlCommandBuilder(daAnyTable)

Looking at the GetInsertCommand.CommandText (and Delete and Update) I see that these are being set correctly.  Now, I try to us the data adapter to send the changes in my data table back to the SQL Server data base:

        Dim nUpdate As Integer = daAnyTable.Update(dsReftables.Tables("AnyTable"))

Then the wheels fall off.  I get the following exception when that Update command runs:

[InvalidOperationException: ExecuteReader: CommandText property has not been initialized]
  System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +1662
  System.Data.Common.DbDataAdapter.Update(DataTable dataTable) +138
  EconAdmin.reference.dgRefTables_TableUpdate() in G:\Internetdev\EconAdmin\reference.aspx.vb:257

Now, firstly I am not quite sure why an ExecuteReader is being executed, but all the command text properties seem to be set.

Has anyone encountered similar problems?

Thanks.

Ken Ray
Thursday, March 25, 2004

While I hate to answer my own question, but since no one else has, here is what I have found.

SqlCommandBuilder won't work if you have an "identity" column in the table you want updated.  Which is what I have - an internal "primary key" that "identity" is the iedal purpose to ensure uniqueness of keys.  I would suspect the use of identity is quite common in data base design.

So, SqlCommand Builder -  a nice try Microsoft, but now give me something useful in the real world.  Yet another black mark against VS.Net.

Ken Ray
Monday, March 29, 2004

That's not true. Are you sure you are selecting your identity field with the query you're assigning to SelectCommand?

shawn
Thursday, April 01, 2004

Shaun:

I was pretty sure I was - anyway, I came up with a solution building the insert / update / delete commands on the fly, and rather than trying to to a select as part of the update to get the identity value, I cleared and refilled the dataset from the database after the update.

Yes, probably inefficient coding, but the tables that are being maintained are small, and this is a seldom used function.

Thanks anyway

Ken Ray
Wednesday, April 07, 2004

*  Recent Topics

*  Fog Creek Home