scope_identity()

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Scrappy
    #1

    scope_identity()

    I have an ASP front end on SQL 2000 database. I have a form that submits to
    an insert query. The entry field is an "identity" and the primary key. I
    have used scope_identity( ) to display the entry# of the record just entered
    on the confirmation page. Now I need to insert the entry into another
    table. This is my query:

    SET NOCOUNT ON
    INSERT wo_main
    (site_id, customer, po_number)
    VALUES ('::site_id::', '::customer::', '::po_number::' )
    SELECT scope_identity( ) AS entry
    INSERT INTO wo_combo_body
    (entry) VALUES ('::entry::')
    SET nocount off

    This query displays the entry number of the record just entered, but inserts
    a 0 in to entry field of the 2nd table. Any help would be great.

    Thanks,
    Darren


    Tags: None
    • Bruce Loving
      #2
      Re: scope_identity( )

      >SELECT scope_identity( ) AS entry
      does not assign the identity to a variable named entry, it just
      returns a recordset like any other select

      either
      declare @Entry int
      set @Entry = (select scope_identity( ))
      insert into table (field) values (@Entry)

      or
      insert int table (field) values (select scope_identity( ))

      also move the set nocount off to the top

      On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
      <celtics@lan-specialist.com> wrote:
      [color=blue]
      >I have an ASP front end on SQL 2000 database. I have a form that submits to
      >an insert query. The entry field is an "identity" and the primary key. I
      >have used scope_identity( ) to display the entry# of the record just entered
      >on the confirmation page. Now I need to insert the entry into another
      >table. This is my query:
      >
      >SET NOCOUNT ON
      >INSERT wo_main
      > (site_id, customer, po_number)
      >VALUES ('::site_id::', '::customer::', '::po_number::' )
      >SELECT scope_identity( ) AS entry
      >INSERT INTO wo_combo_body
      >(entry) VALUES ('::entry::')
      >SET nocount off
      >
      >This query displays the entry number of the record just entered, but inserts
      >a 0 in to entry field of the 2nd table. Any help would be great.
      >
      >Thanks,
      >Darren
      >[/color]

        Comment

        • John Bell
          #3
          Re: scope_identity( )

          Hi

          If you can use a local variable to hold what is returned by SCOPE_IDENTITY.
          This variable can then be used in the second insert statement. You may also
          want to add some error checking! Rather than returning a result set it may
          also be better(faster) to return the value as a parameter

          John

          "Scrappy" <celtics@lan-specialist.com> wrote in message
          news:aptZb.3236 6$um1.10431@twi ster.nyroc.rr.c om...[color=blue]
          > I have an ASP front end on SQL 2000 database. I have a form that submits[/color]
          to[color=blue]
          > an insert query. The entry field is an "identity" and the primary key. I
          > have used scope_identity( ) to display the entry# of the record just[/color]
          entered[color=blue]
          > on the confirmation page. Now I need to insert the entry into another
          > table. This is my query:
          >
          > SET NOCOUNT ON
          > INSERT wo_main
          > (site_id, customer, po_number)
          > VALUES ('::site_id::', '::customer::', '::po_number::' )
          > SELECT scope_identity( ) AS entry
          > INSERT INTO wo_combo_body
          > (entry) VALUES ('::entry::')
          > SET nocount off
          >
          > This query displays the entry number of the record just entered, but[/color]
          inserts[color=blue]
          > a 0 in to entry field of the 2nd table. Any help would be great.
          >
          > Thanks,
          > Darren
          >
          >[/color]


            Comment

            • Scrappy
              #4
              Re: scope_identity( )

              Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
              any pitfalls with doing it with a trigger?

              Also....

              On the same confirmation page I want to diplay links to a page for each
              table. Basically I need to select the entry field from each table that I
              have inserted to with the trigger. I can then use this as a hyperlink to
              the each page. Any ideas on this?


              "Bruce Loving" <BRUCE@LOVINGSC ENTS.COM> wrote in message
              news:tbtc30lq99 c2h3lvjgrh93nt9 hmqk1hisc@4ax.c om...[color=blue][color=green]
              > >SELECT scope_identity( ) AS entry[/color]
              > does not assign the identity to a variable named entry, it just
              > returns a recordset like any other select
              >
              > either
              > declare @Entry int
              > set @Entry = (select scope_identity( ))
              > insert into table (field) values (@Entry)
              >
              > or
              > insert int table (field) values (select scope_identity( ))
              >
              > also move the set nocount off to the top
              >
              > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
              > <celtics@lan-specialist.com> wrote:
              >[color=green]
              > >I have an ASP front end on SQL 2000 database. I have a form that submits[/color][/color]
              to[color=blue][color=green]
              > >an insert query. The entry field is an "identity" and the primary key.[/color][/color]
              I[color=blue][color=green]
              > >have used scope_identity( ) to display the entry# of the record just[/color][/color]
              entered[color=blue][color=green]
              > >on the confirmation page. Now I need to insert the entry into another
              > >table. This is my query:
              > >
              > >SET NOCOUNT ON
              > >INSERT wo_main
              > > (site_id, customer, po_number)
              > >VALUES ('::site_id::', '::customer::', '::po_number::' )
              > >SELECT scope_identity( ) AS entry
              > >INSERT INTO wo_combo_body
              > >(entry) VALUES ('::entry::')
              > >SET nocount off
              > >
              > >This query displays the entry number of the record just entered, but[/color][/color]
              inserts[color=blue][color=green]
              > >a 0 in to entry field of the 2nd table. Any help would be great.
              > >
              > >Thanks,
              > >Darren
              > >[/color]
              >[/color]


                Comment

                • John Bell
                  #5
                  Re: scope_identity( )

                  Hi

                  You have very little scope to insert new records in a secondary table if you
                  use a trigger, as you can not pass parameters to it. If there is only one
                  column in the second table it should be redundant. If there are other
                  columns then you should write a stored procedure and use a transaction to
                  maintain consistency see books online :
                  BEGIN TRANSACTION:
                  mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ts q
                  lref.chm::/ts_ba-bz_96zy.htm
                  ROLLBACK TRANSACTION:
                  mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ts q
                  lref.chm::/ts_ra-rz_471q.htm
                  COMMIT TRANSACTION:
                  mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ts q
                  lref.chm::/ts_ca-co_7w6m.htm

                  You should be able to identify the values inserted in a session by including
                  identifying data in the table such as Username, Datatime, SessionId etc.

                  John




                  "Scrappy" <celtics@lan-specialist.com> wrote in message
                  news:bnxZb.7149 1$n62.1826@twis ter.nyroc.rr.co m...[color=blue]
                  > Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
                  > any pitfalls with doing it with a trigger?
                  >
                  > Also....
                  >
                  > On the same confirmation page I want to diplay links to a page for each
                  > table. Basically I need to select the entry field from each table that I
                  > have inserted to with the trigger. I can then use this as a hyperlink to
                  > the each page. Any ideas on this?
                  >
                  >
                  > "Bruce Loving" <BRUCE@LOVINGSC ENTS.COM> wrote in message
                  > news:tbtc30lq99 c2h3lvjgrh93nt9 hmqk1hisc@4ax.c om...[color=green][color=darkred]
                  > > >SELECT scope_identity( ) AS entry[/color]
                  > > does not assign the identity to a variable named entry, it just
                  > > returns a recordset like any other select
                  > >
                  > > either
                  > > declare @Entry int
                  > > set @Entry = (select scope_identity( ))
                  > > insert into table (field) values (@Entry)
                  > >
                  > > or
                  > > insert int table (field) values (select scope_identity( ))
                  > >
                  > > also move the set nocount off to the top
                  > >
                  > > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
                  > > <celtics@lan-specialist.com> wrote:
                  > >[color=darkred]
                  > > >I have an ASP front end on SQL 2000 database. I have a form that[/color][/color][/color]
                  submits[color=blue]
                  > to[color=green][color=darkred]
                  > > >an insert query. The entry field is an "identity" and the primary key.[/color][/color]
                  > I[color=green][color=darkred]
                  > > >have used scope_identity( ) to display the entry# of the record just[/color][/color]
                  > entered[color=green][color=darkred]
                  > > >on the confirmation page. Now I need to insert the entry into another
                  > > >table. This is my query:
                  > > >
                  > > >SET NOCOUNT ON
                  > > >INSERT wo_main
                  > > > (site_id, customer, po_number)
                  > > >VALUES ('::site_id::', '::customer::', '::po_number::' )
                  > > >SELECT scope_identity( ) AS entry
                  > > >INSERT INTO wo_combo_body
                  > > >(entry) VALUES ('::entry::')
                  > > >SET nocount off
                  > > >
                  > > >This query displays the entry number of the record just entered, but[/color][/color]
                  > inserts[color=green][color=darkred]
                  > > >a 0 in to entry field of the 2nd table. Any help would be great.
                  > > >
                  > > >Thanks,
                  > > >Darren
                  > > >[/color]
                  > >[/color]
                  >
                  >[/color]


                    Comment

                    Previous template Next
                    Working...