I add a record in my database with an identity value. I want to get the identity value after inserting. I don't want to do that by stored procedure.
This is my code:
SQLString = " INSERT INTO myTable ";
SQLString += " (Cal1, Cal2, Cal3, Cal4) ";
SQLString += " VALUES(N'{0}',N'{1}',N'{2}',N'{3}') ";
SQLString = string.Format(SQLString, Val1, Val2, Val3, Val4);
SQLString += " Declare @_IdentityCode INT SET @_IdentityCode = @@IDENTITY RETURN @_IdentityCode";
int result;
public int DoCommand2(string sql)
{
con = new SqlConnection();
cmd = new SqlCommand();
da = new SqlDataAdapter();
cmd.Connection = con;
da.SelectCommand = cmd;
string cs = GlobalConstants.SqlConnectionString;
con.ConnectionString = cs;
cmd.CommandText = sql;
int i = cmd.ExecuteNonQuery();
return i;
}
but I get this error:
A RETURN statement with a return value cannot be used in this context.
Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:
Replace the last concatenation with:
SQLString += "; SELECT SCOPE_IDENTITY();"
Then to retrieve the ID:
int ID = Convert.ToInt32(command.ExecuteScalar());
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments