[AccessD] c#: Get identity value back from SQL Server

Hi John --

Here is a working solution:

http://stackoverflow.com/questions/9645009/i-have-inserted-a-row-i-want-to-get-its-id-and-plus-it-with-an-int-and-insert

Thank you.

-- Shamil


Fri, 10 Aug 2012 08:38:55 -0400 от jwcolby <jwcolby at colbyconsulting.com>:
>	
>
>
	
	
>
		
		
			
>I have written a function to perform an insert using a passed in SQL string.  I need it to return an 
>
integer PK autoincremented by the table:
>
>
public int mExecNonQueryReturnIdentity(string strCnn, string strSQL, int timeOut)
>
{
>
	int intIdentity = 0;       //reset the records affected property before we execute
>
	SqlConnection mCnn = null;      //setup a sql connection object
>
	SqlCommand myCommand = null;        //setup a sql command object
>
         try
>
         {
>
         	mCnn = new SqlConnection(strCnn);       //create a new sql connection using the connection 
>
string passed in
>
                 mCnn.Open();        //open the connection
>
                 myCommand = new SqlCommand(strSQL, mCnn);       //create a new command using the 
>
sql connection and sql statment
>
                 myCommand.CommandTimeout = 60;     //set the command time out
>
                 SqlParameter param = new SqlParameter("@ID", SqlDbType.Int, 4);
>
                 param.Direction = ParameterDirection.Output;
>
                 myCommand.Parameters.Add(param);
>
>
                 myCommand.ExecuteNonQuery();     //execute the sql statement and set the records 
>
affected property
>
                 intIdentity = (int)param.Value;
>
      	}
>
         catch (SqlException ex)
>
         {
>
         	logger.Error(ex.ToString());
>
         }
>
	return intIdentity ;
>
}
>
>
The SQL statement looks like:
>
>
string strSQLInsert = "INSERT INTO " + dbName + ".dbo." + tblName + " " +
>
	"(" +
>
         "CSS_Name," +
>
         "CSS_DteStart" +
>
         ") " +
>
         "VALUES " +
>
         "(" +
>
         "'DatabaseCrossReferenceCount_" + strNow + "'," +
>
         "'" + strNow + "' " +
>
         "); " +
>
         "SELECT @@IDENTITY AS 'ID'"
>
         ;
>
>
PK = mExecNonQueryReturnIdentity(cnn, strSQLInsert, 600);
>
>
The record is inserted into the table but param in mExecNonQueryReturnIdentity is {} which is null I 
>
suppose.  As a result i fall into the catch saying that the (int) "specified cast is not valid".  I 
>
am trying to cast a null to an int.  But the question is why is the returned value a null instead of 
>
an integer ID.
>
>
There are a ton of hits on google about how to do this but they always break down into quarrels 
>
about "This is not safe and that is not safe and ...".  All the safe issues should not apply in my 
>
specific case because I am controlling the context by doing this from my C# code.
>
>
I think the best solution seems to be doing the output parameter and passing something back which is 
>
the ID but I can't seem to figure out which part - the sql statement or the c# function (or both?) 
>
is in error.
>
>
Any help greatly appreciated.
>
>
-- 
>
John W. Colby
>
Colby Consulting
>
>
Reality is what refuses to go away
>
when you do not believe in it
>
>
-- 
>
AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>
Website: http://www.databaseadvisors.com
>