asked on

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

dhnkley

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;

CJSantora

ASKER

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

ASKER CERTIFIED SOLUTION

sumix

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregoryyoung

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

klexys

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;

klexys

Also, the last line of SQL code should be:
"Select @@IDENTITY"
Not
"Select @@IDENTITY from tbl_advspecs"

jvn222

try

oconn.Open();
SqlDataReader reader = ocommand.ExecuteReader();
reader.Read();
int identity = reader.getInt32(0);

Mycode haven't tested. But it is sulution.