Welcome! and rules
Joel on Software
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?
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.
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?
Thursday, April 1, 2004
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.
Wednesday, April 7, 2004
Fog Creek Home