I guess the workaround is "don't do that." Fortunately in my case, identity
values are not precious, so I go ahead and insert the parent row in the
database, then if they cancel the update I delete the inserted row.
I came up with a moderately simple test case to reproduce the problem, It is
a console app which uses a local instance and the Northwind database:
using System;
using System.Data;
using System.Data.SqlClient;
namespace TestInsert2
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
static SqlDataAdapter daT1;
static SqlDataAdapter daT2;
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
Console.WriteLine("This program attempts to build two tables in a local
SQL Server\n" +
"instance in database Northwind. The tables are Tab1 and Tab2.\n" +
"Press control-C to abort, any other key to continue");
Console.ReadLine();
// First, create the tables for the problem.
SqlConnection conn = new SqlConnection("Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
string [] sqlBuildTables = new string []
{
"if exists (select * from dbo.sysobjects where id = object_id(N'[Tab2]')
" +
"and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Tab2]",
"if exists (select * from dbo.sysobjects where id = object_id(N'[Tab1]')
" +
"and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Tab1]",
"CREATE TABLE [Tab1] ([Tab1ID] [int] IDENTITY (1, 1) NOT NULL ," +
"[ActiveSeq] [int], " +
"[Notes] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ," +
"CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED ( [Tab1ID]) ON [PRIMARY] )
ON [PRIMARY]",
"CREATE TABLE [Tab2] ([Tab1ID] [int] NOT NULL ," +
"[Seq] [int] NOT NULL , [Notes] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ," +
"CONSTRAINT [PK_Tab2] PRIMARY KEY CLUSTERED ([Tab1ID],[Seq]) ON
[PRIMARY] ," +
"CONSTRAINT [FK_Tab2_Tab1] FOREIGN KEY ([Tab1ID]) REFERENCES [Tab1]
([Tab1ID]) ON DELETE CASCADE " +
"ON UPDATE CASCADE ) ON [PRIMARY]"
};
cmd.Connection = conn;
conn.Open();
foreach (string cmdText in sqlBuildTables)
{
cmd.CommandText = cmdText;
cmd.ExecuteNonQuery();
}
conn.Close();
SqlCommandBuilder cb;
SqlCommand insert = null;
SqlCommand update = null;
SqlCommand delete = null;
// Now, set up data adapters
daT1 = new SqlDataAdapter("Select * from Tab1", conn);
cb = new SqlCommandBuilder(daT1);
insert = cb.GetInsertCommand();
insert.CommandText += "; SELECT Tab1ID = SCOPE_IDENTITY()";
insert.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
update = cb.GetUpdateCommand();
delete = cb.GetDeleteCommand();
cb.Dispose();
daT1.InsertCommand = insert;
daT1.UpdateCommand = update;
daT1.DeleteCommand = delete;
daT2 = new SqlDataAdapter("Select * from Tab2", conn);
cb = new SqlCommandBuilder(daT2);
insert = cb.GetInsertCommand();
update = cb.GetUpdateCommand();
delete = cb.GetDeleteCommand();
cb.Dispose();
daT2.InsertCommand = insert;
daT2.UpdateCommand = update;
daT2.DeleteCommand = delete;
DataSet ds;
DataTable tab1;
DataTable tab2;
// And load up the dataset
ds = new DataSet("Tabs12");
daT1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daT1.Fill(ds, "Tab1");
daT2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daT2.Fill(ds, "Tab2");
tab1 = ds.Tables["Tab1"];
tab2 = ds.Tables["Tab2"];
ds.Relations.Add("Tab2ToTab1", tab1.Columns[0], tab2.Columns[0]);
tab1.Columns[0].AutoIncrementSeed = -1;
tab1.Columns[0].AutoIncrementStep = -1;
DataColumn dc;
dc = new DataColumn("Active", System.Type.GetType("System.Boolean"));
tab2.Columns.Add(dc);
dc.ReadOnly = true;
// THIS IS THE FUNCTIONALITY THAT TRIGGERS THE PROBLEM ******
// Aparently, it is the fact that it is a relational expression that
causes the problem
dc.Expression = String.Format("IIF(Parent({0}).ActiveSeq = Seq, True,
False)", "Tab2ToTab1");
// ***********************************************************
ds.AcceptChanges();
DataRow dr1;
DataView dv;
DataRowView drv2;
dr1 = tab1.NewRow();
dr1["Notes"] = "Tab1 notes";
dr1.EndEdit();
tab1.Rows.Add(dr1);
dv = tab2.DefaultView;
drv2 = dv.AddNew();
drv2["Tab1ID"] = dr1["Tab1ID"];
drv2["Seq"] = 1;
drv2["Notes"] = "Tab2 notes";
drv2.EndEdit();
PersistChanges(ds);
}
static void PersistChanges(DataSet ds)
{
if (ds.HasErrors)
throw new ApplicationException("DS Errors");
if (ds.HasChanges())
{
DataRow [] drs1 = ds.Tables["Tab1"].Select("", "",
DataViewRowState.Added);
daT1.Update(drs1);
DataRow [] drs2 = ds.Tables["Tab2"].Select("", "",
DataViewRowState.Added);
daT2.Update(drs2);
}
}
}
}
Brad Rogers said:
Yes, apparently if you have a calculation in a column? NO way, its going
to get this error so its normal
I didnt notice that before. workaround?
make a copy? one thats updateable with NO calculations in it and another
thats NOT updateable with calculations in it.
Id also suggest sending a note to the author of the book, they like hearing
these kinds of things.
David W. Rogers said:
No, I don't think that is a problem, Brad. This code is straight out of David
Sceppa's ADO.NET book (I'm dying to get my hands on the next version!!
Any code that handles strings needs to be able to cope with "", which is the
same object as String.Empty (i.e. in VB, the expression 'String.Empty is ""'
evaluates to True.)
In my case, I think ADO is getting confused about what kind of record
modification it has. Since I can see that everything looks ok in the
DataTable and all of the right RowVersions are in place, I am thinking this
is a coding error in DataAdapter, perhaps related to related tables, but I
have no way to be sure (short of breaking out the assembly skills.)
Mmmm. I wonder if the update that the DataAdapter is doing to save the
identity column after the insert is where the error is happening? I am using
the following technique to retrieve the identity column value:
InsertCommand.CommandText += "; SELECT " + MappingName + "ID =
SCOPE_IDENTITY()"
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
David
:
Im still learning this, so forgive the possible ignorance, is the problem
the empty "" there? dont these require null to be specified?
I sort of recall something where "" causes or can cause an invalid pointer
because there is no string content
I have three related tables, and I am getting an exception when :
****************
Dim dataRows As DataRow() = ds.Tables(MappingName).Select_("",
"",DataViewRowState.Added)
dataAdapter.Update(dataRows)
******************
The exception is:
{System.Data.VersionNotFoundException}
[System.Data.VersionNotFoundException]:
{System.Data.VersionNotFoundException}
HelpLink: Nothing
InnerException: Nothing
Message: "There is no Original data to access."
Source: "System.Data"
StackTrace: " at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)
at QuiltTrackDB.BaseSql.PersistChanges(DataSet ds, DataRowState
rowState,
SqlConnection conn, SqlTransaction tran) in
C:\Projects\QuiltTrack\QuiltTrackDB\BaseSql.vb:line 594
at QuiltTrackDB.BaseSql.PersistAllChanges(DataSet ds) in
C:\Projects\QuiltTrack\QuiltTrackDB\BaseSql.vb:line 186"
TargetSite: {System.Reflection.RuntimeMethodInfo}
I think the problem might be that the table that I am trying to insert a
record into is the subject of a relational expression column for another
table that I am also inserting to in the same transaction, so removing the
expressions before persisting is my next attempt at a workaround. but any
advice would be appreciated. (This bug was found in 1.1, but it is still
there in 2.0)
I have to say I am sorely disappointed in MS and what they have NOT
managed
to do over 5 or so years of ADO.NET - which is, to get the features they
have
long documented - expressions and filters, working correctly.
David Rogers
Fred Hutchinson Cancer Research Center
