[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • Last Modified:

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
0
CJSantora
Asked:
CJSantora
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now