identity /autonumber drives me nuts

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Stefan

    #1

    identity /autonumber drives me nuts

    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(d s, SchemaType.Sour ce, "gegevens")
    ds.Tables("gege vens").Columns( "id").AutoIncre mentSeed = -1
    ds.Tables("gege vens").Columns( "id").AutoIncre mentStep = -1
    da.Fill(ds, "gegevens")
    dv = ds.Tables("gege vens").DefaultV iew
    cmb = New SqlCommandBuild er(da)
    cm = CType(Me.Bindin gContext(dv), CurrencyManager )

    Private Sub da_RowUpdated(B yVal sender As Object, ByVal e As
    System.Data.Sql Client.SqlRowUp datedEventArgs) Handles da.RowUpdated
    If e.Status = UpdateStatus.Co ntinue AndAlso (e.StatementTyp e =
    StatementType.I nsert) Then
    Dim cmdnieuwnr As New SqlCommand("SEL ECT
    IDENT_CURRENT(' dat_test')", cn)
    e.Row("id") = CType(cmdnieuwn r.ExecuteScalar , Integer)
    e.Row.AcceptCha nges()
    End I
    End Sub


    any help apreciated,look ing for the answer for 2 days now

    Kind regards,
    Stefan


  • CT

    #2
    Re: identity /autonumber drives me nuts

    This might help,


    --
    Carsten 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.co m> wrote in message
    news:%23LP4Jx8j GHA.3780@TK2MSF TNGP03.phx.gbl. ..[color=blue]
    > 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(d s, SchemaType.Sour ce, "gegevens")
    > ds.Tables("gege vens").Columns( "id").AutoIncre mentSeed = -1
    > ds.Tables("gege vens").Columns( "id").AutoIncre mentStep = -1
    > da.Fill(ds, "gegevens")
    > dv = ds.Tables("gege vens").DefaultV iew
    > cmb = New SqlCommandBuild er(da)
    > cm = CType(Me.Bindin gContext(dv), CurrencyManager )
    >
    > Private Sub da_RowUpdated(B yVal sender As Object, ByVal e As
    > System.Data.Sql Client.SqlRowUp datedEventArgs) Handles da.RowUpdated
    > If e.Status = UpdateStatus.Co ntinue AndAlso (e.StatementTyp e =
    > StatementType.I nsert) Then
    > Dim cmdnieuwnr As New SqlCommand("SEL ECT
    > IDENT_CURRENT(' dat_test')", cn)
    > e.Row("id") = CType(cmdnieuwn r.ExecuteScalar , Integer)
    > e.Row.AcceptCha nges()
    > End I
    > End Sub
    >
    >
    > any help apreciated,look ing for the answer for 2 days now
    >
    > Kind regards,
    > Stefan
    >[/color]


    Comment

    • Cor Ligthert [MVP]

      #3
      Re: identity /autonumber drives me nuts

      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 uniqueidentifie r (a new guid)



      Feel free to do what you want.

      Cor

      "Stefan" <st@nospam.co m> schreef in bericht
      news:%23LP4Jx8j GHA.3780@TK2MSF TNGP03.phx.gbl. ..[color=blue]
      > 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(d s, SchemaType.Sour ce, "gegevens")
      > ds.Tables("gege vens").Columns( "id").AutoIncre mentSeed = -1
      > ds.Tables("gege vens").Columns( "id").AutoIncre mentStep = -1
      > da.Fill(ds, "gegevens")
      > dv = ds.Tables("gege vens").DefaultV iew
      > cmb = New SqlCommandBuild er(da)
      > cm = CType(Me.Bindin gContext(dv), CurrencyManager )
      >
      > Private Sub da_RowUpdated(B yVal sender As Object, ByVal e As
      > System.Data.Sql Client.SqlRowUp datedEventArgs) Handles da.RowUpdated
      > If e.Status = UpdateStatus.Co ntinue AndAlso (e.StatementTyp e =
      > StatementType.I nsert) Then
      > Dim cmdnieuwnr As New SqlCommand("SEL ECT
      > IDENT_CURRENT(' dat_test')", cn)
      > e.Row("id") = CType(cmdnieuwn r.ExecuteScalar , Integer)
      > e.Row.AcceptCha nges()
      > End I
      > End Sub
      >
      >
      > any help apreciated,look ing for the answer for 2 days now
      >
      > Kind regards,
      > Stefan
      >[/color]


      Comment

      • Stefan

        #4
        Re: identity /autonumber drives me nuts

        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.Co ntinue AndAlso (e.StatementTyp e =
        StatementType.I nsert) Then

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

        intRowUpdatedID = CType(cmdnieuwn r.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.Curren t, DataRowView)

        ds.Tables("gege vens").Merge(ds _new.Tables("ch anges"), False)

        dv.Sort = "id"

        cm.Position = dv.Find(intRowU pdatedID)

        End If





        "Cor Ligthert [MVP]" <notmyfirstname @planet.nl> schreef in bericht
        news:eCOJ698jGH A.4512@TK2MSFTN GP02.phx.gbl...[color=blue]
        > 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 uniqueidentifie r (a new guid)
        >
        > http://www.vb-tips.com/default.aspx?...3-fee3733fcd6f
        >
        > Feel free to do what you want.
        >
        > Cor
        >
        > "Stefan" <st@nospam.co m> schreef in bericht
        > news:%23LP4Jx8j GHA.3780@TK2MSF TNGP03.phx.gbl. ..[color=green]
        >> 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(d s, SchemaType.Sour ce, "gegevens")
        >> ds.Tables("gege vens").Columns( "id").AutoIncre mentSeed = -1
        >> ds.Tables("gege vens").Columns( "id").AutoIncre mentStep = -1
        >> da.Fill(ds, "gegevens")
        >> dv = ds.Tables("gege vens").DefaultV iew
        >> cmb = New SqlCommandBuild er(da)
        >> cm = CType(Me.Bindin gContext(dv), CurrencyManager )
        >>
        >> Private Sub da_RowUpdated(B yVal sender As Object, ByVal e As
        >> System.Data.Sql Client.SqlRowUp datedEventArgs) Handles da.RowUpdated
        >> If e.Status = UpdateStatus.Co ntinue AndAlso (e.StatementTyp e =
        >> StatementType.I nsert) Then
        >> Dim cmdnieuwnr As New SqlCommand("SEL ECT
        >> IDENT_CURRENT(' dat_test')", cn)
        >> e.Row("id") = CType(cmdnieuwn r.ExecuteScalar , Integer)
        >> e.Row.AcceptCha nges()
        >> End I
        >> End Sub
        >>
        >>
        >> any help apreciated,look ing for the answer for 2 days now
        >>
        >> Kind regards,
        >> Stefan
        >>[/color]
        >
        >[/color]


        Comment

        • Cor Ligthert [MVP]

          #5
          Re: identity /autonumber drives me nuts

          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 UniqueIdentifie rs

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

          Cor

          "Stefan" <st@nospam.co m> schreef in bericht
          news:%23RYYyjLk GHA.1272@TK2MSF TNGP03.phx.gbl. ..[color=blue]
          > 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.Co ntinue AndAlso (e.StatementTyp e =
          > StatementType.I nsert) Then
          >
          > Dim cmdnieuwnr As New SqlCommand("SEL ECT IDENT_CURRENT(' dat_test')", cn)
          >
          > intRowUpdatedID = CType(cmdnieuwn r.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.Curren t, DataRowView)
          >
          > ds.Tables("gege vens").Merge(ds _new.Tables("ch anges"), False)
          >
          > dv.Sort = "id"
          >
          > cm.Position = dv.Find(intRowU pdatedID)
          >
          > End If
          >
          >
          >
          >
          >
          > "Cor Ligthert [MVP]" <notmyfirstname @planet.nl> schreef in bericht
          > news:eCOJ698jGH A.4512@TK2MSFTN GP02.phx.gbl...[color=green]
          >> 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 uniqueidentifie r (a new guid)
          >>
          >> http://www.vb-tips.com/default.aspx?...3-fee3733fcd6f
          >>
          >> Feel free to do what you want.
          >>
          >> Cor
          >>
          >> "Stefan" <st@nospam.co m> schreef in bericht
          >> news:%23LP4Jx8j GHA.3780@TK2MSF TNGP03.phx.gbl. ..[color=darkred]
          >>> 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(d s, SchemaType.Sour ce, "gegevens")
          >>> ds.Tables("gege vens").Columns( "id").AutoIncre mentSeed = -1
          >>> ds.Tables("gege vens").Columns( "id").AutoIncre mentStep = -1
          >>> da.Fill(ds, "gegevens")
          >>> dv = ds.Tables("gege vens").DefaultV iew
          >>> cmb = New SqlCommandBuild er(da)
          >>> cm = CType(Me.Bindin gContext(dv), CurrencyManager )
          >>>
          >>> Private Sub da_RowUpdated(B yVal sender As Object, ByVal e As
          >>> System.Data.Sql Client.SqlRowUp datedEventArgs) Handles da.RowUpdated
          >>> If e.Status = UpdateStatus.Co ntinue AndAlso (e.StatementTyp e =
          >>> StatementType.I nsert) Then
          >>> Dim cmdnieuwnr As New SqlCommand("SEL ECT
          >>> IDENT_CURRENT(' dat_test')", cn)
          >>> e.Row("id") = CType(cmdnieuwn r.ExecuteScalar , Integer)
          >>> e.Row.AcceptCha nges()
          >>> End I
          >>> End Sub
          >>>
          >>>
          >>> any help apreciated,look ing for the answer for 2 days now
          >>>
          >>> Kind regards,
          >>> Stefan
          >>>[/color]
          >>
          >>[/color]
          >
          >[/color]


          Comment

          Working...