Returning @@IDENTITY from query

I am doing an insert into a database tbale and need to return the auto id number which is created, I have no problems with this regarding SQL, however I am not sure how to assign it to a var so I can return it to the function call:


        int intspecid = 0;

        // begin to setup insert string
        string oString = "Insert Into tbladvspecs(advid, bannertype, banneractive, bannerstart, bannerend) " +
            "Values(@advid, @bannertype, @banneractive, @bannerstart, @bannerend) " +
            "Declare id int " +
            "Select id = @@IDENTITY from tbl_advspecs ";

        string connectionString = (string)ConfigurationManager.AppSettings["connString"];

        SqlConnection oconn = new SqlConnection(connectionString);
        SqlCommand ocommand = new SqlCommand(oString, oconn);
        ocommand.Connection = oconn;

        ocommand.Parameters.Add(new SqlParameter("@advid", intadvid));
        ocommand.Parameters.Add(new SqlParameter("@bannertype", promotype));
        ocommand.Parameters.Add(new SqlParameter("@bannerstart", strstartdate));
        ocommand.Parameters.Add(new SqlParameter("@bannerend", strenddate));

        try
        {
            oconn.Open();
            intspecid = ocommand.ExecuteNonQuery();

        }
        catch (Exception ee)
        {
            Response.Write(ee.ToString());

        }
        finally
        {
            oconn.Close();
            oconn.Dispose();
        }
        return intspecid;


Thanks in Advance for your help
CJSantora
CJSantoraAsked:
Who is Participating?
 
sumixCommented:

 Output parameters are generally used with stored procedures that return values.

 For your function a simpler solution is to call ExecuteScalar method of ocommand instead of ExecuteNonQuery
 replace
  intspecid = ocommand.ExecuteNonQuery();
 with
  intspecid = Convert.ToInt32(ocommand.ExecuteScalar());

 
0
 
dhnkleyCommented:
int intspecid = 0;

        // begin to setup insert string
        string oString = "Insert Into tbladvspecs(advid, bannertype, banneractive, bannerstart, bannerend) " +
            "Values(@advid, @bannertype, @banneractive, @bannerstart, @bannerend) " +
            "Declare id int " +
            "Select id = @@IDENTITY from tbl_advspecs ";

        string connectionString = (string)ConfigurationManager.AppSettings["connString"];

        SqlConnection oconn = new SqlConnection(connectionString);
        SqlCommand ocommand = new SqlCommand(oString, oconn);
        ocommand.Connection = oconn;

        ocommand.Parameters.Add(new SqlParameter("@advid", intadvid));
        ocommand.Parameters.Add(new SqlParameter("@bannertype", promotype));
        ocommand.Parameters.Add(new SqlParameter("@bannerstart", strstartdate));
        ocommand.Parameters.Add(new SqlParameter("@bannerend", strenddate));
        ocommand.Parameters["@Return_Value"].Direction = ParameterDirection.ReturnValue;

        try
        {
            oconn.Open();
            ocommand.ExecuteNonQuery();
            ntspecid = Int32.Parse(ocommand.Parameters["@Return_Value"].Value.ToString());

        }
        catch (Exception ee)
        {
            Response.Write(ee.ToString());

        }
        finally
        {
            oconn.Close();
            oconn.Dispose();
        }
        return intspecid;
      
0
 
CJSantoraAuthor Commented:
I appreciate your quick response, I am not familiar with this technique. I tried pasting youir changes into my code but received the following error when I tried to use it:

An SqlParameter with ParameterName '@Return_Value' is not contained by this SqlParameterCollection.

Should I be replaceing @Return_Value with something?

CJSantora
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
gregoryyoungCommented:
btw if this is for SQL server you most likely want to use SCOPE_IDENTITY() not @@IDENTITY

http://weblogs.asp.net/rosherove/archive/2003/11/13/37217.aspx

Cheers,

Greg Young
0
 
klexysCommented:
i do not have vs.net on this machine to test this answer, but it ought to work.

        int intspecid = 0;

        // begin to setup insert string
        string oString = "Insert Into tbladvspecs(advid, bannertype, banneractive, bannerstart, bannerend) " +
            "Values(@advid, @bannertype, @banneractive, @bannerstart, @bannerend) " +
            "Select @@IDENTITY from tbl_advspecs ";

        string connectionString = (string)ConfigurationManager.AppSettings["connString"];

        SqlConnection oconn = new SqlConnection(connectionString);
        SqlCommand ocommand = new SqlCommand(oString, oconn);
        ocommand.Connection = oconn;

        ocommand.Parameters.Add(new SqlParameter("@advid", intadvid));
        ocommand.Parameters.Add(new SqlParameter("@bannertype", promotype));
        ocommand.Parameters.Add(new SqlParameter("@bannerstart", strstartdate));
        ocommand.Parameters.Add(new SqlParameter("@bannerend", strenddate));

        try
        {
            oconn.Open();
            intspecid = (int)ocommand.ExecuteScalar();
        }
        catch (Exception ee)
        {
            Response.Write(ee.ToString());
        }
        finally
        {
            oconn.Close();
            oconn.Dispose();
        }
        return intspecid;
0
 
klexysCommented:
Also, the last line of SQL code should be:
"Select @@IDENTITY"
Not
"Select @@IDENTITY from tbl_advspecs"
0
 
jvn222Commented:
try
     
            oconn.Open();
            SqlDataReader reader = ocommand.ExecuteReader();
            reader.Read();
             int identity = reader.getInt32(0);

Mycode haven't tested. But it is sulution.

     
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.