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

Thanks!

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 8/11/2012 12:37 PM, Salakhetdinov Shamil wrote:
> 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.
>>
>>