Hi,

New on this forum so will try to make it short and to the point. I am stuck at a point Inserting a record into a table (the Primary Index is an Identity field). Immediately afterwards need the new ID in order to read back the details and carry out another process.

Prior to this I have always used a Select sproc whose criterea is the same data as used in the insert Sproc, however the obvious problem with that is the possiblility of a new record having exactly the same data? Which in this case is a possibility as opposed to returning the ID of the newly inserted record.

Return_values sounds like a great idea, but I just can not find any useful code anywhere even in many different books and online.......

Using SQLServer 2000 and VB6. Does anyone have any ADO code and Stored Procedure TSQL code that can return the value of the ID column of the newly inserted record immediately after or in the same Sproc as inserting the record?

Thanks

 

Alan

do this after your insert statement

select scope_identity()

Note that this only returns the last identity in the current scope, so you can't insert your value in your code, close the connection, create a new connection and then call this.

You should return this value in the same stored procedure as you insert your data.

---

If beauty sits the child's kiss of laughter I amend,
Can you catch her if she runs?
With this I would share with you,
All of this come to no end

There are a couple of options for this.

First is create an output parameter in the stored procedure you use to insert the data, and assign the value of that parameter using "@ID_out = scope_identity()" (assuming "@ID_out" is the output parameter), right after the insert statement.  Then, when you call the stored procedure, have this as one member of the parameters set in the properties of your SQL connection object, with the direction of the parameter set as "out".  (I know how to do this in VBA, and VB 6 is probably similar, but I'm not certain.)

Second is return the scope_identity in a select statement in the proc, and have the connection object get that in a recordset (1 row).

---

I couldn't think of anything clever for a signature, so I use this instead.

Here is some example code in c#.  The Northwind Database has a stored proc called OrderItem which creates an order and returns the OrderID that was created.  To access a stored procedure called OrderItem and get back the orderID, you'd to this:

SqlConnection myConn = new SqlConnection();

SqlCommand myCmd = new SqlCommand();

myConn.ConnectionString = "Your connection string here";

myConn.Open();

myCmd.Connection = myConn;

myCmd.CommandText = "OrderItem";

myCmd.CommandType = CommandType.StoredProcedure;

SqlParameter p1 = new SqlParameter("@CustomerID", SqlDbType.NVarChar, 5);

myCmd.Parameters.Add(p1);

myCmd.Parameters.Add(p1);

myCmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.Int));

myCmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));

SqlParameter p2 = new SqlParameter("@OrderID", SqlDbType.Int);

p2.Direction = ParameterDirection.Output;

myCmd.Parameters["@CustomerID"].Value = "PAYNE";

myCmd.Parameters["@ProductID"].Value = 12;

myCmd.Parameters["@Quantity"].Value = 10;

int numrows = myCmd.ExecuteNonQuery();

int OrderID = p2.Value;

MessageBox.Show("Order " + OrderID.ToString() + " has been placed.");

The key to it is the p2.Direction.  It needs to be set as an Output parameter.

Hope that helps.

Bob Payne