Question about INSERT

Hi,

I have a table with columns X, Y, and Z. X is the primary key and is an integer that is set to auto-increment whenever a new row is added. Y and Z are pieces of information which are not necessarily unique to any given row, so in order to get the row I just added, I really need to know X.

So when I insert a new row, I would like to somehow have X returned to me, so that I can then use it as a pointer since in my application a row that is just added must often be modified very shortly afterwards.

Is there a call which will do this? Right now I am using:

sqlAdapter.InsertCommand.ExecuteNonQuery();

Which just returns the number of the rows affected, not the actual row, or its primary key.

Thanks,

Hi,

Here is a good example that shows you how to do it: http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Try using ExecuteScalar() instead of ExecuteNonQuery() with an appropriate Insert Command String illustrated below. The code just illustrates the basic need as per the scenario in the Question and may not be fully functional.

int newX = 0;
string strInsertCommandText = "INSERT INTO MyTable (Y, Z) VALUES (@.Y, @.Z); SELECT CAST(scope_identity() AS int)";
sqlAdapter.InsertCommand.CommandType = CommandType.Text;
sqlAdapter.InsertCommand.CommandText = strInsertCommandText;

//Set the Parameter Values here.
sqlAdapter.InsertCommand.Parameters.Add("@.Y", SqlDbType.VarChar);
sqlAdapter.InsertCommand.Paramaters["@.Y"].Value = "Y Value";
sqlAdapter.InsertCommand.Parameters.Add("@.Z", SqlDbType.VarChar);
sqlAdapter.InsertCommand.Paramaters["@.Z"].Value = "Z Value";

//This line inserts a new record into the table and returns the generated primary key for the new record.
newX = (Int32)sqlAdapter.InsertCommand.ExecuteScalar();