Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: SQL2k Merge with recordset update problem!

    im using a database called "test". This database have just one table,
    called "Table1". The structure of this table:

    CREATE TABLE [Table1] (
    [id] [int] IDENTITY (2, 1) NOT NULL ,
    [nome] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    this table is REPLICATED (MERGE REPLICATION) .
    When i update the recordset i cant retrive the identity value
    after the rs.update command.
    But when i drop the publication of this articles this problems immediatly stops
    the code i used above

    Set teste_conn = Server.CreateObject("ADODB.Connection")
    teste_conn.Open "Driver={SQL Server};server=xxx;uid=xxxx;pwd=xxxx;database=TEST "

    Set rs = Server.CreateObject("ADODB.RecordSet")
    rs.Open "SELECT * FROM table1 WHERE id=0",teste_conn,1,2
    If rs.EOF Then rs.Addnew
    rs("nome")="AAAAAAAA"
    rs.Update
    ' ---- I NEED TO GET THIS VALUE----
    id = rs("id")
    '--------------
    rs.Close
    Set rs = Nothing

    teste_conn.Close
    Set teste_conn = Nothing

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Something to look into would be @@IDENTITY in SQLServer. It returns the last identity value inserted, though I am not sure off the top of my head what it's scope is (you may loose it becuase you are in another transaction).

    Try:

    rs.Open "SELECT @@IDENITY AS ID", teste_conn
    id=rs("ID")

    See what you get. If it does work, you may need to worry about other inserts going on, as it is not specific to a table.
    -bpd

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Me again. Scrap that. Use IDENT_CURRENT('table_name').

    So:

    rs2.Open "SELECT IDENT_CURRENT('table1') AS ID", teste_conn
    id=rs2("ID")
    -bpd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •