|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
identity /autonumber drives me nutsvb.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 This might help,
http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx -- Show quoteHide quoteCarsten Thomsen Communities - http://community.integratedsolutions.dk --------- Voodoo Programming: Things programmers do that they know shouldn't work but they try anyway, and which sometimes actually work, such as recompiling everything. (Karl Lehenbauer) --------- "Stefan" <st@nospam.com> wrote in message news:%23LP4Jx8jGHA.3780@TK2MSFTNGP03.phx.gbl... > 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,
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 Show quoteHide quote "Stefan" <st@nospam.com> schreef in bericht news:%23LP4Jx8jGHA.3780@TK2MSFTNGP03.phx.gbl... > 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 > 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 Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> schreef in bericht news:eCOJ698jGHA.4512@TK2MSFTNGP02.phx.gbl... > 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" <st@nospam.com> schreef in bericht > news:%23LP4Jx8jGHA.3780@TK2MSFTNGP03.phx.gbl... >> 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,
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 Show quoteHide quote "Stefan" <st@nospam.com> schreef in bericht news:%23RYYyjLkGHA.1272@TK2MSFTNGP03.phx.gbl... > 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]" <notmyfirstn***@planet.nl> schreef in bericht > news:eCOJ698jGHA.4512@TK2MSFTNGP02.phx.gbl... >> 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" <st@nospam.com> schreef in bericht >> news:%23LP4Jx8jGHA.3780@TK2MSFTNGP03.phx.gbl... >>> 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 >>> >> >> > > 
Other interesting topics
Undefined function 'InStrRev' in expression.
Is this Possible ? error in the update Array Problems - still cant get something set up right. Dll fails to register Streamreader doesn't read the line properly Click and Double click events are not fired in listview component in VB.NET WithEvents code in module cannot change textbox on main form How to override only Get or only Set? Reading XML file getting error |
|||||||||||||||||||||||
