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

John,
I'm not a SQL server person, nor c#, but I found this that may be worth a
read.
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Don't shoot the messenger if it isn't applicable.
jack

On Fri, Aug 10, 2012 at 8:38 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> 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<http://databaseadvisors.com/mailman/listinfo/accessd>
> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com>
>