Error Msg: "There is no Original data to access" with MS Access db

[Gregory A Greenman]

I am using VB.Net 2003 with an MS Access database. This program is
one I wrote myself for my own use. It has been in use for a couple
of years. I am using a strongly typed dataset with several tables.
The first field in every table is named "ID" and is the table's
Primary Key.

Recently, I added a couple of new tables. When I ran my save
routine, their data was saved properly, until a couple of days ago.
Now, when my program tries to save their data, I get an error
message that says "There is no Original data to access".

Here's some of my code:

------------ begin code ------------
Public Class frmMain
...

Dim cmdRefresh As OleDbCommand

...

Private Sub Main_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
...

cmdRefresh = New OleDbCommand("SELECT @@IDENTITY", cn)

...

AddHandler adpInventory.RowUpdated, AddressOf
HandleInventoryRowUpdated
AddHandler adpSalesHeader.RowUpdated, AddressOf
HandleSalesHeaderRowUpdated
...
End Sub

Private Sub HandleInventoryRowUpdated(ByVal sender As Object, ByVal
e As OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso (e.StatementType =
StatementType.Insert) Then
e.Row("ID") = CInt(cmdRefresh.ExecuteScalar)
e.Row.AcceptChanges()
End If
End Sub

Private Sub HandleSalesHeaderRowUpdated(ByVal sender As Object,
ByVal e As OleDbRowUpdatedEventArgs)
Dim x As Integer

If e.Status = UpdateStatus.Continue AndAlso (e.StatementType =
StatementType.Insert) Then
x = CInt(cmdRefresh.ExecuteScalar)
Console.WriteLine("RowState = " & e.Row.RowState.ToString)
Console.WriteLine("Current ID = " & e.Row("ID").ToString)
Console.WriteLine("Original ID = " & e.Row("ID",
DataRowVersion.Original).ToString)
'Next line generates: There is no Original data to access
e.Row("ID") = x
e.Row.AcceptChanges()
End If
End Sub
------------ end code ------------

When my program is saving new Inventory rows, it works fine. When
it saves new SalesHeader rows it gives me the error message at the
line indicated above. The console.writelines generate:

RowState = Unchanged
Current ID = -1
Original ID = -1

The only differences in the Handle...RowUpdated procedures are ones
I put in to try to debug this error.

A few days ago, I added some calculated fields to the strongly
typed dataset. I'm not sure if this is was when the problem began.
Removing those fields did not make the problem go away.

Can anyone suggest anything, short of upgrading to a newer version
of Visual Studio (which I plan to do soon anyway), that might solve
this problem?

Thanks for any help.

 

[Cor Ligthert[MVP]]

Gregory,

You know that AcceptChanges means something like:

"Accept the changes in the DataRow as done and remove the Data to update
the database in the row, set the status in the DataRow to unchanged"

Cor

 

[Gregory A Greenman]

Hi,

Thanks.

Yes, I know that. I'm unsure what that has to do with my question.



Greg

Gregory,

You know that AcceptChanges means something like:

"Accept the changes in the DataRow as done and remove the Data to update
the database in the row, set the status in the DataRow to unchanged"

Cor

 

[Cor Ligthert[MVP]]

Gregory,

Your code looks a little bit uncommon to me, can be that it is correct, the
only thing I recognized was the acceptchanges, which has shown here to give
a lot misunderstandings and mistakes.

Cor

Hi,

Thanks.

Yes, I know that. I'm unsure what that has to do with my question.



Greg

 

[Gregory A Greenman]

Hi,

The reason this code looks unusual to you is probably because you
are used to using SQL Server. With SQL Server the same command that
updates the database with added records also retrieves the identity
fields. Access databases don't support that. So, you have to have a
handler for the RowUpdated event which retrieves that data.

The code I have is slightly modified from the help topic:
ms-
help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconretri
evingidentityorautonumbervalues.htm

The SQL query "SELECT @@IDENTITY" retrieves the most recent
identity field. So, the line:

e.Row("ID") = CInt(cmdRefresh.ExecuteScalar)

retrieves that data and assigns it to my Primary Key. The next
line:

e.Row.AcceptChanges()

then propagates that data into the Original DataRowVersion.

Without these two lines, I'd get a concurreny violation if the row
was edited further. Also, without the first one, any child table
rows would not have the correct Foreign Key.

Of course, this makes me wonder. When the parent table's ID is
updated, it has to propagate to the child table. Is it possible
that this error is really being thrown on the update of the child
row?

I had removed the calculated fields from the parent table because,
in my research on this problem, I found a post which said there was
a bug that would cause this error if a table had calculated fields.
At that time, I did not remove the calculated fields from the child
table. I have now done that and the problem has gone away. (Of
course, lack of calculated fields is a problem as well.) This leads
me to believe that the answer to my question is yes.


Greg

Gregory,

Your code looks a little bit uncommon to me, can be that it is correct, the
only thing I recognized was the acceptchanges, which has shown here to give
a lot misunderstandings and mistakes.

Cor

 

[Gregory A Greenman]

I am using VB.Net 2003 with an MS Access database. This program is
one I wrote myself for my own use. It has been in use for a couple
of years. I am using a strongly typed dataset with several tables.
The first field in every table is named "ID" and is the table's
Primary Key.

Recently, I added a couple of new tables. When I ran my save
routine, their data was saved properly, until a couple of days ago.
Now, when my program tries to save their data, I get an error
message that says "There is no Original data to access".

Here's some of my code:

------------ begin code ------------
Public Class frmMain
...

Dim cmdRefresh As OleDbCommand

...

Private Sub Main_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
...

cmdRefresh = New OleDbCommand("SELECT @@IDENTITY", cn)

...

AddHandler adpInventory.RowUpdated, AddressOf
HandleInventoryRowUpdated
AddHandler adpSalesHeader.RowUpdated, AddressOf
HandleSalesHeaderRowUpdated
...
End Sub

Private Sub HandleInventoryRowUpdated(ByVal sender As Object, ByVal
e As OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso (e.StatementType =
StatementType.Insert) Then
e.Row("ID") = CInt(cmdRefresh.ExecuteScalar)
e.Row.AcceptChanges()
End If
End Sub

Private Sub HandleSalesHeaderRowUpdated(ByVal sender As Object,
ByVal e As OleDbRowUpdatedEventArgs)
Dim x As Integer

If e.Status = UpdateStatus.Continue AndAlso (e.StatementType =
StatementType.Insert) Then
x = CInt(cmdRefresh.ExecuteScalar)
Console.WriteLine("RowState = " & e.Row.RowState.ToString)
Console.WriteLine("Current ID = " & e.Row("ID").ToString)
Console.WriteLine("Original ID = " & e.Row("ID",
DataRowVersion.Original).ToString)
'Next line generates: There is no Original data to access
e.Row("ID") = x
e.Row.AcceptChanges()
End If
End Sub
------------ end code ------------

When my program is saving new Inventory rows, it works fine. When
it saves new SalesHeader rows it gives me the error message at the
line indicated above. The console.writelines generate:

RowState = Unchanged
Current ID = -1
Original ID = -1

The only differences in the Handle...RowUpdated procedures are ones
I put in to try to debug this error.

A few days ago, I added some calculated fields to the strongly
typed dataset. I'm not sure if this is was when the problem began.
Removing those fields did not make the problem go away.

Can anyone suggest anything, short of upgrading to a newer version
of Visual Studio (which I plan to do soon anyway), that might solve
this problem?

I'm adding some more info here for the benefit of anyone still
using VS2003.

I have a SalesHeader table and a SalesDetail table. SalesDetail is
a child table of SalesHeader. When HandleSalesHeaderRowUpdated gets
called, the SalesHeader's Primary Key is updated to match the one
issued by the database. Then, the Foreign Key in SalesDetail is
updated. That update is where I believe that the error was being
thrown.

I removed all calculated fields from SalesDetail and the problem
went away. I then added some back and found that the error was only
thrown if there was a calculated field that referred to its parent
table.

I downloaded VS2008Pro and have converted my program. Adding back
the calculated fields that refer to the parent table, the error is
not thrown. So, this problem is specific to VS2003. (I haven't
tried it in VS2005, but since VS2005 uses .Net Framework 2.0 and
that's what my program is currently targeting in VS2008, I doubt
that VS2005 will have this problem.)