Calling Any ADO.NET people !!! "Drama in the DataSet - by Mr Newbie "

[Mr Newbie]

Drama In The Dataset
by Mr Newbie ( A tale of woe and endurance
------------------------------------------------

I am desperately trying to solve an issue with a dataset so please will
someone at least take a stab at helping me ?

I have narrowed down the problem now, so hopefully it should be less of an
issue to address.

I have two tables and a relation. Master / Details. The relation is set to
maintain referential integrety. OK and the master KEY is set to use
decrementing autoincrement so we can link child and parent on the client
side.

1.) Add a single parent row ( OK )

2.) Add the children, and all of them get -1 as their foreign key ( OK )

3.) run Update on the Parent DataAdapter , single parent row created in SQL
server and retreived @@IDENTITY, (OK ?)
foreign keys are updated to the PK of the Master Row from retreived
@@IDENTITY.

4.) When we check the child rows at this stage their status has been changed
from ADDED to UNCHANGED because of the operation performed with the Parent
DataAdapter.

During stage 3.) The foreign keys are updated in the child table, and I
think the Accept Changes is being called on the child table because of the
relation, although this does not make sense.

Does anyone have any idea if what I am saying is correct and if so, why or
how can I get around it ?

Here's hoping for some help !!

:-D

 

[m.posseth]

maybe you could write some example code ( copy pastable ) that will show
the problem ( with comments )
so "we" can have a look at it

regards

Michel Posseth [MCP]

 

[Mr Newbie]

'There you go. This function is called after the master row and its
children have been added to the DataSet passed as 'expenseClaim' The
relation is created when the DataSet is instantiated from the DataSet.xsd so
this is a strongly typed dataset.

I have monitored the child row status before and after the parent adapter
has its update method called and post update the child rows foreign keys
have been updated with the @@IDENTITY of the master. But unfortunately as I
stated in my texts above, the row states are then Unchanged.


Public Function creatExpenseClaim(ByVal expenseClaim As ExpenseDataSet)
As Boolean


'********* PARENT TABLE *************
'Set Basic Objects and Variables
Dim selectString As String
Dim returnValue As Boolean = False

sqlConnection = New SqlConnection(ConnectionString)
sqlCommand = New SqlCommand(selectString, sqlConnection)
Dim sqlDataAdapter As New SqlDataAdapter(sqlCommand)
sqlDataAdapter.InsertCommand = sqlCommand

'Deal With Parent Table First
sqlCommand.CommandText = "INSERT INTO Expenses(EmployeeID, Status,
History, DateCreated, DateAuthorised, Da" & _
"teWeekEnding, Authorisor) VALUES (@EmployeeID, @Status, @History,
@DateCreated, " & _
"@DateAuthorised, @DateWeekEnding, @Authorisor); " & _
"SELECT ID, EmployeeID, Status, History, DateCreated,
DateAuthorised, DateWeekEnding, Authorisor FROM Expenses WH" & _
"ERE (ID = @@IDENTITY);"


sqlCommand.CommandType = CommandType.Text

sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EmployeeID", System.Data.SqlDbType.Int,
4, "EmployeeID"))
sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Status", System.Data.SqlDbType.VarChar,
15, "Status"))
sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@History",
System.Data.SqlDbType.VarChar, 2147483647, "History"))
sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateCreated",
System.Data.SqlDbType.DateTime, 8, "DateCreated"))
sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateAuthorised",
System.Data.SqlDbType.DateTime, 8, "DateAuthorised"))
sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateWeekEnding",
System.Data.SqlDbType.DateTime, 8, "DateWeekEnding"))
sqlCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Authorisor",
System.Data.SqlDbType.VarChar, 50, "Authorisor"))

'********* CHILD TABLE *************
Dim sqlChildInsertCommand As New SqlCommand
sqlChildInsertCommand.Connection = Me.sqlConnection
sqlChildInsertCommand.CommandText = "INSERT INTO
ExpenseItems(DateExpensed, ExpensesID, Description, Cost, Vat, Li" & _
"neTotal) VALUES (@DateExpensed, @ExpensesID, @Description, @Cost,
@Vat, @Li" & _
"neTotal);"

sqlChildInsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateExpensed",
System.Data.SqlDbType.DateTime, 8, "DateExpensed"))
sqlChildInsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ExpensesID", System.Data.SqlDbType.Int,
4, "ExpensesID"))
sqlChildInsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.VarChar, 30, "Description"))
sqlChildInsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Cost", System.Data.SqlDbType.Decimal,
9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0,
Byte), "Cost", System.Data.DataRowVersion.Current, Nothing))
sqlChildInsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Vat", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0,
Byte), "Vat", System.Data.DataRowVersion.Current, Nothing))
sqlChildInsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LineTotal",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, CType(18, Byte), CType(0, Byte), "LineTotal",
System.Data.DataRowVersion.Current, Nothing))

Dim daChildTableAdapter As New SqlDataAdapter
daChildTableAdapter.InsertCommand = sqlChildInsertCommand


Try
sqlConnection.Open()

printExpenseItems(expenseClaim)
sqlDataAdapter.Update(expenseClaim.Expenses.Select("", "",
DataViewRowState.Added Or DataViewRowState.ModifiedCurrent))
printExpenseItems(expenseClaim)
daChildTableAdapter.Update(expenseClaim.Tables("ExpenseItems"))

Catch ex As Exception
System.Diagnostics.Debug.WriteLine("**ERROR** : " & ex.Message)

Finally
sqlConnection.Close()
End Try


Exit Function

 

[Bart Mermuys]

Hi,

'There you go. This function is called after the master row and its
children have been added to the DataSet passed as 'expenseClaim' The
relation is created when the DataSet is instantiated from the DataSet.xsd
so this is a strongly typed dataset.

Open your typed DataSet in DataSet designer and check the relation, you
should *not* have set Accept/Reject rule to Cascade, if you have, set it to
none instead.


(And you should also use SCOPE_IDENTITY() instead of @@IDENTITY)

HTH,
Greetings

 

[Mr Newbie]

Thanks Bart. I saw this in a post from David Sceppa and it worked. But I am
confused as to why cascading a change should render child rows unchanged ??
See below.


As you point out in your post, this is because the AcceptRejectRole is set
to cascade would be set to True in this situation. What I am struggling
with is why this happens. If we create a master row, and update it, even it
ifs foreign key in the children have been updated, why should the child row
be set to unchanged when this is clearly not the case. If the Master
somehow had been able to cause the child table in the SQL Server to be
updated then yes, but in this scenario No.

 

[Bart Mermuys]

Hi,

Thanks Bart. I saw this in a post from David Sceppa and it worked. But I
am confused as to why cascading a change should render child rows
unchanged ??

You're not cascading a change, you are cascading
AcceptChanges/RejectChanges.

When you update the parent table, then after each row is updated in the db,
the adapter will call AcceptChanges on that row, marking that row as
unchanged. When AcceptRejectRule is set the Cascade, then AcceptChanges is
called on all related child rows marking them as unchanged too.

So, i don't see any reason why you would want to set Accept/RejectRule to
Cascade since it definitely doesn't suit your needs.

HTH,
Greetings

 

[Mr Newbie]

OK, thanks. When "Would" you need to cascade this. What sort of scenario
would you expect to need that ?

 

[Bart Mermuys]

Hi,

OK, thanks. When "Would" you need to cascade this. What sort of scenario
would you expect to need that ?

Good question, i have no idea when it's usefull, if you really want to know,
you may consider starting a new thread just about that. And post it also to
....dotnet.framework.adonet.



Greetings

 

[Mr Newbie]

I have posted it to the adonet group specifically

Thanks for all your help !

 

[m.posseth]

Well Today was my birthday , the last visitors are now driving out of the
street and i thought lets see what has become of this thread
as i thought with a clear explanation of the problem and some example code
anyone steps in and helps you to solve the problem most of the times when
you do not see a good or not clear response it is only because people do
not exactly understand your problem .

well it is now time for me , to drink some of my left over beers

regards

Michel Posseth

 

[Mr Newbie]

Cheers for your help and happy birthday !