identity /autonumber drives me nuts

[CT]

This might help,
http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

 

[Cor Ligthert [MVP]]

Stefan,

As advice, try to avoid autonumbers if you are new creating a database and
you want to use ADONET.

The nicest one is the uniqueidentifier (a new guid)

http://www.vb-tips.com/default.aspx?ID=b57d0ce2-3f5f-47d4-9663-fee3733fcd6f

Feel free to do what you want.

Cor

 

[Stefan]

Hello,
vb.net /MSDE question

I have a form to add a new record.When Save buttton is pushed,the
record is saved to the database,but i don't know how to
get the new id back in my dataset (instead of -1)
the rowupdated event more specific :
e.row("id") =.... throws an error : 'column 'id' is readonly'

I know id is readonly.but i in the past i used the same code(except
@@identity instead of ident_current)
with access databases and everthing worked fine.

here's the code i used

da = New SqlDataAdapter(command)
da.FillSchema(ds, SchemaType.Source, "gegevens")
ds.Tables("gegevens").Columns("id").AutoIncrementSeed = -1
ds.Tables("gegevens").Columns("id").AutoIncrementStep = -1
da.Fill(ds, "gegevens")
dv = ds.Tables("gegevens").DefaultView
cmb = New SqlCommandBuilder(da)
cm = CType(Me.BindingContext(dv), CurrencyManager)

Private Sub da_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles da.RowUpdated
If e.Status = UpdateStatus.Continue AndAlso (e.StatementType =
StatementType.Insert) Then
Dim cmdnieuwnr As New SqlCommand("SELECT
IDENT_CURRENT('dat_test')", cn)
e.Row("id") = CType(cmdnieuwnr.ExecuteScalar, Integer)
e.Row.AcceptChanges()
End I
End Sub


any help apreciated,looking for the answer for 2 days now

Kind regards,
Stefan

 

[Stefan]

Cor,
I worked a way around to update the id
rowupdated looks like this:
do you see any problems here?

Dim intRowUpdatedID As Integer

If e.Status = UpdateStatus.Continue AndAlso (e.StatementType =
StatementType.Insert) Then

Dim cmdnieuwnr As New SqlCommand("SELECT IDENT_CURRENT('dat_test')", cn)

intRowUpdatedID = CType(cmdnieuwnr.ExecuteScalar, Integer)

Me.Tag = intRowUpdatedID

Dim ds_new As New DataSet

Dim da_new As New SqlDataAdapter("select * from dat_test where id = " &
intRowUpdatedID, cn)

da_new.Fill(ds_new, "changes")

Dim drv As DataRowView

drv = CType(cm.Current, DataRowView)

ds.Tables("gegevens").Merge(ds_new.Tables("changes"), False)

dv.Sort = "id"

cm.Position = dv.Find(intRowUpdatedID)

End If

 

[Cor Ligthert [MVP]]

Stefan,

AFAIK did nobody succeed in getting the correct created autocreated ID's
with a dataset. Reason, you never know if somebody else has done an insert
already and therefore created a new id.

SQLClient inserts the new ID in the dataset, OleDB does not. The way with
Jet Access is to refill the dataset again

Therefore my message about UniqueIdentifiers

Sorry I spent some time on this and don't know another answer.

Cor