Identity Number of Newly Inserted Row

Identity Number of Newly Inserted Row

Post by Martin A. Weinberge » Sat, 06 Jan 2001 04:42:18



Hi Guys,

    I'm sorry to bother you guys again, but hopefully you can help.  I have
a table, where one of the columns is an auto number column, called Identity,
in SQL Server.  Every time there is a new row, SQL Server automatically
assigns a number to the cell.  My question is how do I programatically
determine what the identity number is of the newly inserted row?  For
whatever it is worth, I can already successfully add, update, and retrieve
data from the SQL Server database.

My Thoughts
-----------------
The problem seems to be to transfer all the columns of the newly inserted
rows into the data results set buffer.  Once I do that, all I have to do is
get the appropriate column of the first row in the resulting data set and
I'm in business.  Does SQL / ODBC automatically fill the results data set
with the entire contents of the row?  I would seriously doubt that.

Thanks in advance you guys,

-Marty

 
 
 

Identity Number of Newly Inserted Row

Post by Ryszard Gawro » Sat, 06 Jan 2001 18:15:02


Hello Marty,
here some information:
http://support.microsoft.com/support/kb/articles/Q195/2/24.ASP
regards
Ryszard Gawron



Quote:> Hi Guys,

>     I'm sorry to bother you guys again, but hopefully you can help.  I
have
> a table, where one of the columns is an auto number column, called
Identity,
> in SQL Server.  Every time there is a new row, SQL Server automatically
> assigns a number to the cell.  My question is how do I programatically
> determine what the identity number is of the newly inserted row?  For
> whatever it is worth, I can already successfully add, update, and retrieve
> data from the SQL Server database.

> My Thoughts
> -----------------
> The problem seems to be to transfer all the columns of the newly inserted
> rows into the data results set buffer.  Once I do that, all I have to do
is
> get the appropriate column of the first row in the resulting data set and
> I'm in business.  Does SQL / ODBC automatically fill the results data set
> with the entire contents of the row?  I would seriously doubt that.

> Thanks in advance you guys,

> -Marty


 
 
 

Identity Number of Newly Inserted Row

Post by Martin A. Weinberge » Sun, 07 Jan 2001 01:57:18


Hi guys,

    I  checked out the VB script and still have a follow up question.
First, I'm using C++ (not MFC, though) and the ODBC API.

    The relavent code fragment from the sample, I believe is:

         'Uncomment this line and it works without the Unique index.
         '.Open "SET NOCOUNT ON;INSERT idTest(Col1, Col2) " & _
              "VALUES('" & strCol1 & "', '" & dtCol2 & "');" & _

         'Comment this line if you uncomment the one above.
         .Open "SELECT * FROM idTest WHERE 1=0"

    I'm not sure that I understand the comment or that this does what I
want.  I have to use the SQLExecute() API along with the SQLBindParameter().
If this code fragment does what I need it to do, then I need a bit of help
decyphering into plain English what the code fragment does.

    From a C++ point of view here is ultra supremely simplified code
fragment that illustrates the code necessary to insert a row:

    //
    rResult = SQLPrepare( hstmtCmd, "INSERT (col1, col2) INTO [Table_Name]
VALUES (?, ?)", SQL_NTS );

    // Set data to insert.
    lpVal[0] = &<variable name here>;

    // Bind for each parameter, inside a for loop.
    rResult  = SQLBindParameter( hstmtCmd, shPNum, SQL_PARAM_INPUT,
shCType[shBind],
                                                       shPType[shBind],
uColSize[shBind], 0, lpVal[shBind], 0, &iILen[shBind] );

    // Do the magic.
     rResult = SQLExecute( hstmtCmd );

    As you can see, the code inserts a row, but does not return the identity
number of that row.  If the code about does do that, I'd appreciate an
explanation, so that I understand it.

    Thanks in advance,

-Marty

 
 
 

1. Get Identity Number for Newly Inserted Row

Hi,

    I hope you guys can help.  I'm using Visual C++ (no MFC, though)
together with the ODBC API to interact with MS SQL Server 2000.  In one
particular table that I am working with, I defined one of the columns as an
identity column, meaning that SQL Server assigns a unique number to that
column.  SQL Server assigns the number at row creation time.  From my code I
can already successfully add a new row without any problems.  My question is
how do I obtain the identity number for the row that I just added.  The code
that I use to insert a new row is (greatly simplified):

 SQLAllocHandle( SQL_HANDLE_STMT, hdbcSQL, &hstmtCmd );

 rResult = SQLPrepare( hstmtCmd, lpszCommand, SQL_NTS ); // INSERT (<col.
list>) INTO [table name] VALUES (?...)

 rResult  = SQLBindParameter( hstmtCmd, shPNum, SQL_PARAM_INPUT, ... );

 rResult = SQLExecute( hstmtCmd );

    What I don't see is how to tell ODBC to return to me the column value in
a data result set that I can read for the inserted row.  In a LISTBOX
control, adding or inserting a row returns an index, which the caller can
than use to access the row.  What is the equivalent here?

    Thanks in advance,

-Marty
-------------------

2. 6.4 processes under VMS

3. How to get Identity column values for newly inserted row

4. Encryption

5. Identity cannot be determined for newly inserted rows

6. telling how packed are indexes

7. get back the identity of a newly inserted row thru ODBC

8. Fw: Onarchive problem

9. Getting IDENTITY value of newly inserted row

10. Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)

11. Identity cannot be determined for newly inserted rows

12. Getting the identity value of a newly inserted row

13. Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)