RowUpdated Exception

[Jason James]

Hi all,

I am struggling to get around a data.readonlyexception issue with
my SQL database.

I have declared my dataadapter withevents and use the event
handler below to obtain the actual PK written to the DB. The
field name is iID. I am using SELECT @@IDENTITY to obtain
the ID of the last written field. This is all working fine.

However, when I get to the line that reads

e.row("iID") = id

the data.readonlyexcpetion is raised.

Private Sub myDaCountedItems_RowUpdated(ByVal sender As Object,
ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles
myDaCountedItems.RowUpdated
If Not IsNothing(e.Errors) Then
Debug.WriteLineIf(myDebugSwitch.Enabled = True, e.Errors)
End If
Try
If e.Status = UpdateStatus.Continue AndAlso
e.StatementType = StatementType.Insert Then
Dim cmdGetIdentity As New SqlCommand("SELECT
@@IDENTITY", myConn)
Dim id As Int32 =
Convert.ToInt32(cmdGetIdentity.ExecuteScalar())
e.Row("iID") = id
Debug.WriteLine(e.Row("iID"))
e.Row.AcceptChanges()
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

I have tried to call row.beginedit and row.endedit, but I still
get the exception. Any ideas what might be causing this? I
use the same technique for an Access DB and all it fine!

Many thanks,

Jason.

 

[Ray Booysen]

Hi Jason

Is the iID field set to IDENTITY in SQL server and to self-increment?

 

[Jason James]

Ray,

Yes it is. The field is an int(4) ad set to Identity with an
identity seed of 1 and an identity increment of 1.

Does this mean I can't do what I want with the DS?

Regards,

Jason.

 

[Ray Booysen]

Hi Jason

If its set to Identity, you cannot enter a value that you want, as it
will self-increment and that field is read-only because SQL server
generates the field value.

If you retrieve the Identity from the SQL Server using the command, why
are you setting e.row("iID") equal to that value? What is the reasoning?

Also, consider using SELECT @@SCOPE_IDENTITY to retrieve the last
identity for your scope otherwise @@IDENTITY will just retrieve the last
created identity field, not neccesarily yours.



Regards
Ray

 

[Jason James]

I am using a dataadapter and a dataset to hold a local copy
of the data. If another user inserts a record into the DS
and then updates the DB, and then I try and do the same,
the ID generated by the DS for my insert (the second one into
the DB) will have the wrong ID. By reading back the ID after
updating the dataase through the data adapter I should be
able to update the local DS with that value. It works great
using autoincrement fields in Access.

I am not writing directly to the DB. I am using a dataadapte
and a data set. The identity is generated by the DS when a
new record is added, but this is local the the machine, and
in another user inserts a record into their DS and then updates
the DB my identity number will be wrong.

Many thankis for sticking with me on this,

Jason.

 

[Jason James]

Below is the SP used to create the table

CREATE TABLE [tblParts] (
[iID] [int] IDENTITY (1, 1) NOT NULL ,
[iPartNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iDescription] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_tblParts] PRIMARY KEY CLUSTERED
(
[iID]
) ON [PRIMARY]
) ON [PRIMARY]
GO


And here is the program that updates the iID field of the table:

Dim ds As DataSet
Dim cmd As SqlCommand
Dim conn As SqlConnection
Dim WithEvents da As SqlDataAdapter
Dim cb As SqlCommandBuilder

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
conn = New SqlConnection("initial catalog = InsertExample;
data source = localhost; integrated security = SSPI")
cmd = New SqlCommand("SELECT * FROM tblParts", conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "Parts")
cb = New SqlCommandBuilder(da)
DataGrid1.DataSource = ds.Tables("Parts")
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim part As DataRow = ds.Tables("Parts").NewRow
part("iPartNumber") = TextBox1.Text
part("iDescription") = TextBox2.Text
ds.Tables("Parts").Rows.Add(part)
da.Update(ds, "Parts")
End Sub

Private Sub da_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles da.RowUpdated
If e.StatementType = StatementType.Insert And e.Status =
UpdateStatus.Continue Then
Dim getID As New SqlCommand("SELECT @@IDENTITY", conn)
Dim id As Int32 = Convert.ToInt32(getID.ExecuteScalar())
e.Row("iID") = id
e.Row.AcceptChanges()
End If
End Sub

In this demo program everything works just as I would expect it to and
even though I have the iID field set as an IDENTITY fied I can still
update it by retrieving @@IDENTITY.

Any thoughts?

Thanks,

Jason.

 

[Jason James]

Still nit sure why the field is read only, but I have managed to
get around it by setting the read only property of the field
to false, updating the DS and then setting the property back
to true again.

myDS.Tables("CountedItem").Columns("iID").ReadOnly = False
myDaCountedItems.Update(myDS.Tables("CountedItem"))
myDS.Tables("CountedItem").Columns("iID").ReadOnly = True

Kind regards,

Jason.