Getting the id generated by SQL Server for a new record

Discussion in 'ASP .Net' started by Nathan Sokalski, Nov 30, 2007.

  1. I am using ASP.NET 2.0 to add records to a database table in an SQL Server
    database. The id field is automatically created when a record is added, and
    I would like to be able to know what value was assigned to the id field for
    use in my ASP.NET application. Is there a way to find out what value was
    assigned to the record at the same time I create the record (in otherwords,
    I do not want to use a SELECT statement)? Thanks.

    --
    Nathan Sokalski

    http://www.nathansokalski.com/
     
    Nathan Sokalski, Nov 30, 2007
    #1
    1. Advertising

  2. On Nov 29, 10:42 pm, "Nathan Sokalski" <> wrote:
    > I am using ASP.NET 2.0 to add records to a database table in an SQL Server
    > database. The id field is automatically created when a record is added, and
    > I would like to be able to know what value was assigned to the id field for
    > use in my ASP.NET application. Is there a way to find out what value was
    > assigned to the record at the same time I create the record (in otherwords,
    > I do not want to use a SELECT statement)? Thanks.
    >
    > --
    > Nathan Sokalski
    > ://www.nathansokalski.com/


    Look in the Books Online for the SCOPE_IDENTITY function. Lots of
    good examples there.
     
    Stuart Ainsworth, Nov 30, 2007
    #2
    1. Advertising

  3. Nathan Sokalski

    Dan Gartner Guest

    On Nov 29, 9:42 pm, "Nathan Sokalski" <> wrote:
    > I am using ASP.NET 2.0 to add records to a database table in an SQL Server
    > database. The id field is automatically created when a record is added, and
    > I would like to be able to know what value was assigned to the id field for
    > use in my ASP.NET application. Is there a way to find out what value was
    > assigned to the record at the same time I create the record (in otherwords,
    > I do not want to use a SELECT statement)? Thanks.
    >
    > --
    > Nathan Sokalski
    > ://www.nathansokalski.com/


    You would want to return the SCOPE_IDENTITY() value as an output
    parameter from your DbCommand after you perform the insert.
     
    Dan Gartner, Nov 30, 2007
    #3
  4. Nathan Sokalski

    Manfred Sorg Guest

    On 30 Nov., 07:18, Dan Gartner <> wrote:
    > On Nov 29, 9:42 pm, "Nathan Sokalski" <> wrote:
    > > I do not want to use a SELECT statement)? Thanks.

    > You would want to return the SCOPE_IDENTITY() value as an output


    You could use the OUTPUT feature of sql server 2005. But in this case
    SCOPE_IDENTITY() and @@IDENTITY give back odd results while
    IDENT_CURRENT() and inserted.id seem to give correct values.
    inserted.id is the way the output feature is meant.

    Example:
    begin tran
    create table tmp (id int identity, xyz varchar)
    insert into tmp (xyz) output inserted.id values ('w')
    insert into tmp (xyz) output @@identity values ('x')
    insert into tmp (xyz) output ident_current('tmp') values ('y')
    insert into tmp (xyz) output scope_identity() values ('z')
    select * from tmp
    drop table tmp
    rollback

    Results:
    1
    1
    3
    3

    id xyz
    ----------- ----
    1 w
    2 x
    3 y
    4 z

    (4 Zeile(n) betroffen)
     
    Manfred Sorg, Nov 30, 2007
    #4
  5. Hi
    You can use the output parameter of the stored procedure and return the
    value that is being inserted. If you are using identity column then you can
    use @@IDENTITY to return the last inserted indentity value.
    --
    ---------------------------
    Thanks,
    Ibrahim

    Software Consultant - Web Development, GB


    "Nathan Sokalski" wrote:

    > I am using ASP.NET 2.0 to add records to a database table in an SQL Server
    > database. The id field is automatically created when a record is added, and
    > I would like to be able to know what value was assigned to the id field for
    > use in my ASP.NET application. Is there a way to find out what value was
    > assigned to the record at the same time I create the record (in otherwords,
    > I do not want to use a SELECT statement)? Thanks.
    >
    > --
    > Nathan Sokalski
    >
    > http://www.nathansokalski.com/
    >
    >
    >
     
    Ibrahim Shameeque, Nov 30, 2007
    #5
  6. Nathan Sokalski

    TheSQLGuru Guest

    This method obviously requires using stored procs (which is almost always a
    good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
    bit of a bind I think. IIRC you can't issue an insert statement and get a
    select back out in a single Execute... type command.

    --
    Kevin G. Boles
    TheSQLGuru
    Indicium Resources, Inc.


    "Ibrahim Shameeque" <> wrote in
    message news:...
    > Hi
    > You can use the output parameter of the stored procedure and return the
    > value that is being inserted. If you are using identity column then you
    > can
    > use @@IDENTITY to return the last inserted indentity value.
    > --
    > ---------------------------
    > Thanks,
    > Ibrahim
    >
    > Software Consultant - Web Development, GB
    >
    >
    > "Nathan Sokalski" wrote:
    >
    >> I am using ASP.NET 2.0 to add records to a database table in an SQL
    >> Server
    >> database. The id field is automatically created when a record is added,
    >> and
    >> I would like to be able to know what value was assigned to the id field
    >> for
    >> use in my ASP.NET application. Is there a way to find out what value was
    >> assigned to the record at the same time I create the record (in
    >> otherwords,
    >> I do not want to use a SELECT statement)? Thanks.
    >>
    >> --
    >> Nathan Sokalski
    >>
    >> http://www.nathansokalski.com/
    >>
    >>
    >>
     
    TheSQLGuru, Nov 30, 2007
    #6
  7. Kevin,

    Sure you can, like this:

    cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
    (@StudentName, @Test1, @Test2); Select Scope_Identity()"

    Then:

    ID = cmd.ExecuteScalar

    Kerry Moorman


    "TheSQLGuru" wrote:

    > This method obviously requires using stored procs (which is almost always a
    > good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
    > bit of a bind I think. IIRC you can't issue an insert statement and get a
    > select back out in a single Execute... type command.
    >
    > --
    > Kevin G. Boles
    > TheSQLGuru
    > Indicium Resources, Inc.
    >
    >
    > "Ibrahim Shameeque" <> wrote in
    > message news:...
    > > Hi
    > > You can use the output parameter of the stored procedure and return the
    > > value that is being inserted. If you are using identity column then you
    > > can
    > > use @@IDENTITY to return the last inserted indentity value.
    > > --
    > > ---------------------------
    > > Thanks,
    > > Ibrahim
    > >
    > > Software Consultant - Web Development, GB
    > >
    > >
    > > "Nathan Sokalski" wrote:
    > >
    > >> I am using ASP.NET 2.0 to add records to a database table in an SQL
    > >> Server
    > >> database. The id field is automatically created when a record is added,
    > >> and
    > >> I would like to be able to know what value was assigned to the id field
    > >> for
    > >> use in my ASP.NET application. Is there a way to find out what value was
    > >> assigned to the record at the same time I create the record (in
    > >> otherwords,
    > >> I do not want to use a SELECT statement)? Thanks.
    > >>
    > >> --
    > >> Nathan Sokalski
    > >>
    > >> http://www.nathansokalski.com/
    > >>
    > >>
    > >>

    >
    >
    >
     
    Kerry Moorman, Nov 30, 2007
    #7
  8. Nathan Sokalski

    TheSQLGuru Guest

    I seem to recall a client trying to do that recently (using ADO classic) and
    it not working. Perhaps they missed the semicolon. I will recheck their
    attempts and see if that does it.

    One additional question since I am not an ADO guru. Does the Select
    Scope_identity() not return a single-column single-row result set, which the
    executescalar isn't expecting?

    --
    Kevin G. Boles
    TheSQLGuru
    Indicium Resources, Inc.


    "Kerry Moorman" <> wrote in message
    news:D...
    > Kevin,
    >
    > Sure you can, like this:
    >
    > cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
    > (@StudentName, @Test1, @Test2); Select Scope_Identity()"
    >
    > Then:
    >
    > ID = cmd.ExecuteScalar
    >
    > Kerry Moorman
    >
    >
    > "TheSQLGuru" wrote:
    >
    >> This method obviously requires using stored procs (which is almost always
    >> a
    >> good idea for a bunch of reasons). If you are using ADO/ADONET you are
    >> in a
    >> bit of a bind I think. IIRC you can't issue an insert statement and get
    >> a
    >> select back out in a single Execute... type command.
    >>
    >> --
    >> Kevin G. Boles
    >> TheSQLGuru
    >> Indicium Resources, Inc.
    >>
    >>
    >> "Ibrahim Shameeque" <> wrote in
    >> message news:...
    >> > Hi
    >> > You can use the output parameter of the stored procedure and return the
    >> > value that is being inserted. If you are using identity column then you
    >> > can
    >> > use @@IDENTITY to return the last inserted indentity value.
    >> > --
    >> > ---------------------------
    >> > Thanks,
    >> > Ibrahim
    >> >
    >> > Software Consultant - Web Development, GB
    >> >
    >> >
    >> > "Nathan Sokalski" wrote:
    >> >
    >> >> I am using ASP.NET 2.0 to add records to a database table in an SQL
    >> >> Server
    >> >> database. The id field is automatically created when a record is
    >> >> added,
    >> >> and
    >> >> I would like to be able to know what value was assigned to the id
    >> >> field
    >> >> for
    >> >> use in my ASP.NET application. Is there a way to find out what value
    >> >> was
    >> >> assigned to the record at the same time I create the record (in
    >> >> otherwords,
    >> >> I do not want to use a SELECT statement)? Thanks.
    >> >>
    >> >> --
    >> >> Nathan Sokalski
    >> >>
    >> >> http://www.nathansokalski.com/
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
    TheSQLGuru, Nov 30, 2007
    #8
  9. Kevin,

    ExecuteScalar returns the first column of the first row in the result set
    returned by the query.

    Kerry Moorman


    "TheSQLGuru" wrote:

    >
    > One additional question since I am not an ADO guru. Does the Select
    > Scope_identity() not return a single-column single-row result set, which the
    > executescalar isn't expecting?
    >
    > --
    > Kevin G. Boles
    > TheSQLGuru
    > Indicium Resources, Inc.
    >
     
    Kerry Moorman, Nov 30, 2007
    #9
  10. Nathan Sokalski

    Miha Markic Guest

    Right.
    BTW one should always use Scope_Identity() and not @@Identity as posted by
    Ibrahim.

    --
    Miha Markic [MVP C#, INETA Country Leader for Slovenia]
    RightHand .NET consulting & development www.rthand.com
    Blog: http://cs.rthand.com/blogs/blog_with_righthand/

    "Kerry Moorman" <> wrote in message
    news:D...
    > Kevin,
    >
    > Sure you can, like this:
    >
    > cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
    > (@StudentName, @Test1, @Test2); Select Scope_Identity()"
     
    Miha Markic, Nov 30, 2007
    #10
  11. Nathan Sokalski

    TheSQLGuru Guest

    I just checked back with the developer that had the issue. He swears that
    using VB6 and ADO classic your example fails. Were you using ADOc or
    ADO.NET?

    --
    Kevin G. Boles
    TheSQLGuru
    Indicium Resources, Inc.


    "Kerry Moorman" <> wrote in message
    news:...
    > Kevin,
    >
    > ExecuteScalar returns the first column of the first row in the result set
    > returned by the query.
    >
    > Kerry Moorman
    >
    >
    > "TheSQLGuru" wrote:
    >
    >>
    >> One additional question since I am not an ADO guru. Does the Select
    >> Scope_identity() not return a single-column single-row result set, which
    >> the
    >> executescalar isn't expecting?
    >>
    >> --
    >> Kevin G. Boles
    >> TheSQLGuru
    >> Indicium Resources, Inc.
    >>

    >
     
    TheSQLGuru, Nov 30, 2007
    #11
  12. Kevin,

    My example was using ADO.Net.

    Kerry Moorman


    "TheSQLGuru" wrote:

    > I just checked back with the developer that had the issue. He swears that
    > using VB6 and ADO classic your example fails. Were you using ADOc or
    > ADO.NET?
    >
    > --
    > Kevin G. Boles
    > TheSQLGuru
    > Indicium Resources, Inc.
    >
    >
    > "Kerry Moorman" <> wrote in message
    > news:...
    > > Kevin,
    > >
    > > ExecuteScalar returns the first column of the first row in the result set
    > > returned by the query.
    > >
    > > Kerry Moorman
    > >
    > >
    > > "TheSQLGuru" wrote:
    > >
    > >>
    > >> One additional question since I am not an ADO guru. Does the Select
    > >> Scope_identity() not return a single-column single-row result set, which
    > >> the
    > >> executescalar isn't expecting?
    > >>
    > >> --
    > >> Kevin G. Boles
    > >> TheSQLGuru
    > >> Indicium Resources, Inc.
    > >>

    > >

    >
    >
    >
     
    Kerry Moorman, Nov 30, 2007
    #12
  13. The approach in VB6 is virtually identical.

    --
    ____________________________________
    William (Bill) Vaughn
    Author, Mentor, Consultant, Dad, Grandpa
    Microsoft MVP
    INETA Speaker
    www.betav.com
    www.betav.com/blog/billva
    Please reply only to the newsgroup so that others can benefit.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    __________________________________
    Visit www.hitchhikerguides.net to get more information on my latest book:
    Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
    -----------------------------------------------------------------------------------------------------------------------

    "Kerry Moorman" <> wrote in message
    news:...
    > Kevin,
    >
    > My example was using ADO.Net.
    >
    > Kerry Moorman
    >
    >
    > "TheSQLGuru" wrote:
    >
    >> I just checked back with the developer that had the issue. He swears
    >> that
    >> using VB6 and ADO classic your example fails. Were you using ADOc or
    >> ADO.NET?
    >>
    >> --
    >> Kevin G. Boles
    >> TheSQLGuru
    >> Indicium Resources, Inc.
    >>
    >>
    >> "Kerry Moorman" <> wrote in message
    >> news:...
    >> > Kevin,
    >> >
    >> > ExecuteScalar returns the first column of the first row in the result
    >> > set
    >> > returned by the query.
    >> >
    >> > Kerry Moorman
    >> >
    >> >
    >> > "TheSQLGuru" wrote:
    >> >
    >> >>
    >> >> One additional question since I am not an ADO guru. Does the Select
    >> >> Scope_identity() not return a single-column single-row result set,
    >> >> which
    >> >> the
    >> >> executescalar isn't expecting?
    >> >>
    >> >> --
    >> >> Kevin G. Boles
    >> >> TheSQLGuru
    >> >> Indicium Resources, Inc.
    >> >>
    >> >

    >>
    >>
    >>
     
    William Vaughn, Nov 30, 2007
    #13
  14. Manfred,

    > insert into tmp (xyz) output scope_identity() values ('z')
    > select * from tmp


    The OP wrote that he does not want to use the "Select" statement

    In my idea at the moment impossible however this seems ignored in any reply
    in this messagethread

    Cor
     
    Cor Ligthert[MVP], Dec 1, 2007
    #14
  15. Nathan Sokalski

    TheSQLGuru Guest

    Recalling that I am NOT an ADO guru - despite having a copy of Vaughn's
    book - I tried to get this to work. First I found that in ADO 2.8 there is
    no ExecuteScalar (at least not that I found). So I tried it using a
    recordset, with the following code:

    Private Sub Command1_Click()

    'here is the table def I used
    'use northwind
    'go
    'create table c (c int identity, b char(1))

    ' connection, command, and recordset variables
    Dim Cnxn As Connection
    Dim cmdChange As Command
    Dim rs As Recordset

    ' Open connection
    Set Cnxn = New Connection
    Cnxn.Open "Provider='sqloledb';Data Source='(local)';Initial
    Catalog='Northwind';Integrated Security='SSPI';"

    ' Create command object
    Set cmdChange = New Command
    Set cmdChange.ActiveConnection = Cnxn
    cmdChange.CommandText = "insert c (b) values ('A'); select
    scope_identity() as a"

    Set rs = cmdChange.Execute()

    rs.MoveFirst

    Do While Not rs.EOF
    id = rs.Fields(0)
    Loop

    Cnxn.Close
    Set rstTitles = Nothing
    Set Cnxn = Nothing
    Exit Sub

    End Sub


    I get the following message when I step on rs.MoveFirst - Operation is not
    allowed when the object is closed.

    Perhaps it can be done using a parameter with the command execute?

    --
    Kevin G. Boles
    TheSQLGuru
    Indicium Resources, Inc.


    "William Vaughn" <> wrote in message
    news:...
    > The approach in VB6 is virtually identical.
    >
    > --
    > ____________________________________
    > William (Bill) Vaughn
    > Author, Mentor, Consultant, Dad, Grandpa
    > Microsoft MVP
    > INETA Speaker
    > www.betav.com
    > www.betav.com/blog/billva
    > Please reply only to the newsgroup so that others can benefit.
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    > __________________________________
    > Visit www.hitchhikerguides.net to get more information on my latest book:
    > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
    > -----------------------------------------------------------------------------------------------------------------------
    >
    > "Kerry Moorman" <> wrote in message
    > news:...
    >> Kevin,
    >>
    >> My example was using ADO.Net.
    >>
    >> Kerry Moorman
    >>
    >>
    >> "TheSQLGuru" wrote:
    >>
    >>> I just checked back with the developer that had the issue. He swears
    >>> that
    >>> using VB6 and ADO classic your example fails. Were you using ADOc or
    >>> ADO.NET?
    >>>
    >>> --
    >>> Kevin G. Boles
    >>> TheSQLGuru
    >>> Indicium Resources, Inc.
    >>>
    >>>
    >>> "Kerry Moorman" <> wrote in
    >>> message
    >>> news:...
    >>> > Kevin,
    >>> >
    >>> > ExecuteScalar returns the first column of the first row in the result
    >>> > set
    >>> > returned by the query.
    >>> >
    >>> > Kerry Moorman
    >>> >
    >>> >
    >>> > "TheSQLGuru" wrote:
    >>> >
    >>> >>
    >>> >> One additional question since I am not an ADO guru. Does the Select
    >>> >> Scope_identity() not return a single-column single-row result set,
    >>> >> which
    >>> >> the
    >>> >> executescalar isn't expecting?
    >>> >>
    >>> >> --
    >>> >> Kevin G. Boles
    >>> >> TheSQLGuru
    >>> >> Indicium Resources, Inc.
    >>> >>
    >>> >
    >>>
    >>>
    >>>

    >
     
    TheSQLGuru, Dec 2, 2007
    #15
  16. Classic ADO treats the "rows affected" message as a recordset. Try adding SET NOCOUNT ON before your
    INSERT statement to suppress this.

    --
    Tibor Karaszi, SQL Server MVP
    http://www.karaszi.com/sqlserver/default.asp
    http://sqlblog.com/blogs/tibor_karaszi


    "TheSQLGuru" <> wrote in message news:...
    > Recalling that I am NOT an ADO guru - despite having a copy of Vaughn's book - I tried to get this
    > to work. First I found that in ADO 2.8 there is no ExecuteScalar (at least not that I found). So
    > I tried it using a recordset, with the following code:
    >
    > Private Sub Command1_Click()
    >
    > 'here is the table def I used
    > 'use northwind
    > 'go
    > 'create table c (c int identity, b char(1))
    >
    > ' connection, command, and recordset variables
    > Dim Cnxn As Connection
    > Dim cmdChange As Command
    > Dim rs As Recordset
    >
    > ' Open connection
    > Set Cnxn = New Connection
    > Cnxn.Open "Provider='sqloledb';Data Source='(local)';Initial Catalog='Northwind';Integrated
    > Security='SSPI';"
    >
    > ' Create command object
    > Set cmdChange = New Command
    > Set cmdChange.ActiveConnection = Cnxn
    > cmdChange.CommandText = "insert c (b) values ('A'); select scope_identity() as a"
    >
    > Set rs = cmdChange.Execute()
    >
    > rs.MoveFirst
    >
    > Do While Not rs.EOF
    > id = rs.Fields(0)
    > Loop
    >
    > Cnxn.Close
    > Set rstTitles = Nothing
    > Set Cnxn = Nothing
    > Exit Sub
    >
    > End Sub
    >
    >
    > I get the following message when I step on rs.MoveFirst - Operation is not allowed when the object
    > is closed.
    >
    > Perhaps it can be done using a parameter with the command execute?
    >
    > --
    > Kevin G. Boles
    > TheSQLGuru
    > Indicium Resources, Inc.
    >
    >
    > "William Vaughn" <> wrote in message
    > news:...
    >> The approach in VB6 is virtually identical.
    >>
    >> --
    >> ____________________________________
    >> William (Bill) Vaughn
    >> Author, Mentor, Consultant, Dad, Grandpa
    >> Microsoft MVP
    >> INETA Speaker
    >> www.betav.com
    >> www.betav.com/blog/billva
    >> Please reply only to the newsgroup so that others can benefit.
    >> This posting is provided "AS IS" with no warranties, and confers no rights.
    >> __________________________________
    >> Visit www.hitchhikerguides.net to get more information on my latest book:
    >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
    >> -----------------------------------------------------------------------------------------------------------------------
    >>
    >> "Kerry Moorman" <> wrote in message
    >> news:...
    >>> Kevin,
    >>>
    >>> My example was using ADO.Net.
    >>>
    >>> Kerry Moorman
    >>>
    >>>
    >>> "TheSQLGuru" wrote:
    >>>
    >>>> I just checked back with the developer that had the issue. He swears that
    >>>> using VB6 and ADO classic your example fails. Were you using ADOc or
    >>>> ADO.NET?
    >>>>
    >>>> --
    >>>> Kevin G. Boles
    >>>> TheSQLGuru
    >>>> Indicium Resources, Inc.
    >>>>
    >>>>
    >>>> "Kerry Moorman" <> wrote in message
    >>>> news:...
    >>>> > Kevin,
    >>>> >
    >>>> > ExecuteScalar returns the first column of the first row in the result set
    >>>> > returned by the query.
    >>>> >
    >>>> > Kerry Moorman
    >>>> >
    >>>> >
    >>>> > "TheSQLGuru" wrote:
    >>>> >
    >>>> >>
    >>>> >> One additional question since I am not an ADO guru. Does the Select
    >>>> >> Scope_identity() not return a single-column single-row result set, which
    >>>> >> the
    >>>> >> executescalar isn't expecting?
    >>>> >>
    >>>> >> --
    >>>> >> Kevin G. Boles
    >>>> >> TheSQLGuru
    >>>> >> Indicium Resources, Inc.
    >>>> >>
    >>>> >
    >>>>
    >>>>
    >>>>

    >>

    >
    >
     
    Tibor Karaszi, Dec 2, 2007
    #16
  17. Nathan Sokalski

    TheSQLGuru Guest

    THANKS Tibor - that was the trick I was looking for!

    --
    Kevin G. Boles
    TheSQLGuru
    Indicium Resources, Inc.


    "Tibor Karaszi" <> wrote in
    message news:...
    > Classic ADO treats the "rows affected" message as a recordset. Try adding
    > SET NOCOUNT ON before your INSERT statement to suppress this.
    >
    > --
    > Tibor Karaszi, SQL Server MVP
    > http://www.karaszi.com/sqlserver/default.asp
    > http://sqlblog.com/blogs/tibor_karaszi
    >
    >
    > "TheSQLGuru" <> wrote in message
    > news:...
    >> Recalling that I am NOT an ADO guru - despite having a copy of Vaughn's
    >> book - I tried to get this to work. First I found that in ADO 2.8 there
    >> is no ExecuteScalar (at least not that I found). So I tried it using a
    >> recordset, with the following code:
    >>
    >> Private Sub Command1_Click()
    >>
    >> 'here is the table def I used
    >> 'use northwind
    >> 'go
    >> 'create table c (c int identity, b char(1))
    >>
    >> ' connection, command, and recordset variables
    >> Dim Cnxn As Connection
    >> Dim cmdChange As Command
    >> Dim rs As Recordset
    >>
    >> ' Open connection
    >> Set Cnxn = New Connection
    >> Cnxn.Open "Provider='sqloledb';Data Source='(local)';Initial
    >> Catalog='Northwind';Integrated Security='SSPI';"
    >>
    >> ' Create command object
    >> Set cmdChange = New Command
    >> Set cmdChange.ActiveConnection = Cnxn
    >> cmdChange.CommandText = "insert c (b) values ('A'); select
    >> scope_identity() as a"
    >>
    >> Set rs = cmdChange.Execute()
    >>
    >> rs.MoveFirst
    >>
    >> Do While Not rs.EOF
    >> id = rs.Fields(0)
    >> Loop
    >>
    >> Cnxn.Close
    >> Set rstTitles = Nothing
    >> Set Cnxn = Nothing
    >> Exit Sub
    >>
    >> End Sub
    >>
    >>
    >> I get the following message when I step on rs.MoveFirst - Operation is
    >> not allowed when the object is closed.
    >>
    >> Perhaps it can be done using a parameter with the command execute?
    >>
    >> --
    >> Kevin G. Boles
    >> TheSQLGuru
    >> Indicium Resources, Inc.
    >>
    >>
    >> "William Vaughn" <> wrote in message
    >> news:...
    >>> The approach in VB6 is virtually identical.
    >>>
    >>> --
    >>> ____________________________________
    >>> William (Bill) Vaughn
    >>> Author, Mentor, Consultant, Dad, Grandpa
    >>> Microsoft MVP
    >>> INETA Speaker
    >>> www.betav.com
    >>> www.betav.com/blog/billva
    >>> Please reply only to the newsgroup so that others can benefit.
    >>> This posting is provided "AS IS" with no warranties, and confers no
    >>> rights.
    >>> __________________________________
    >>> Visit www.hitchhikerguides.net to get more information on my latest
    >>> book:
    >>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    >>> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
    >>> -----------------------------------------------------------------------------------------------------------------------
    >>>
    >>> "Kerry Moorman" <> wrote in
    >>> message news:...
    >>>> Kevin,
    >>>>
    >>>> My example was using ADO.Net.
    >>>>
    >>>> Kerry Moorman
    >>>>
    >>>>
    >>>> "TheSQLGuru" wrote:
    >>>>
    >>>>> I just checked back with the developer that had the issue. He swears
    >>>>> that
    >>>>> using VB6 and ADO classic your example fails. Were you using ADOc or
    >>>>> ADO.NET?
    >>>>>
    >>>>> --
    >>>>> Kevin G. Boles
    >>>>> TheSQLGuru
    >>>>> Indicium Resources, Inc.
    >>>>>
    >>>>>
    >>>>> "Kerry Moorman" <> wrote in
    >>>>> message
    >>>>> news:...
    >>>>> > Kevin,
    >>>>> >
    >>>>> > ExecuteScalar returns the first column of the first row in the
    >>>>> > result set
    >>>>> > returned by the query.
    >>>>> >
    >>>>> > Kerry Moorman
    >>>>> >
    >>>>> >
    >>>>> > "TheSQLGuru" wrote:
    >>>>> >
    >>>>> >>
    >>>>> >> One additional question since I am not an ADO guru. Does the
    >>>>> >> Select
    >>>>> >> Scope_identity() not return a single-column single-row result set,
    >>>>> >> which
    >>>>> >> the
    >>>>> >> executescalar isn't expecting?
    >>>>> >>
    >>>>> >> --
    >>>>> >> Kevin G. Boles
    >>>>> >> TheSQLGuru
    >>>>> >> Indicium Resources, Inc.
    >>>>> >>
    >>>>> >
    >>>>>
    >>>>>
    >>>>>
    >>>

    >>
    >>

    >
     
    TheSQLGuru, Dec 3, 2007
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Geoff
    Replies:
    3
    Views:
    448
    Patrick.O.Ige
    Jan 16, 2006
  2. Bobby Edward
    Replies:
    2
    Views:
    3,058
    Bobby Edward
    Oct 5, 2008
  3. Frits van Soldt

    How to insert a new record in detailsview when there are no record

    Frits van Soldt, Nov 16, 2005, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    746
    Frits van Soldt
    Nov 17, 2005
  4. sean
    Replies:
    3
    Views:
    172
  5. Maximus
    Replies:
    2
    Views:
    193
    Bob Barrows [MVP]
    Apr 12, 2007
Loading...

Share This Page