Getting the auto-incremented id from a database table

[dazza12345]

HI all, im in a bit of a pickle!

I have the following Data Function that will insert Addresses into an AddressDetails table. The first column in the table is an auto-incremented AddressID.

My problem is, how can i get the value fo the AddressID as soon as I have inserted the new row?

    public static void CreateAddressDetails(AddressDetails addressdetails)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
  
                command.CommandText = "INSERT INTO AddressDetails " +
                    "(HouseNumber, AddressLine1, AddressLine2, City, County, Country, PostCode, IsCopy) " +
                    "VALUES(@HouseNumber, @AddressLine1, @AddressLine2, @City, @County, @Country, @PostCode, @IsCopy); SELECT SCOPE_IDENTITY() AS 'InsertedId';";

                command.Parameters.AddWithValue("@HouseNumber", addressdetails.HouseNumber);
                command.Parameters.AddWithValue("@AddressLine1", addressdetails.AddressLine1);
                command.Parameters.AddWithValue("@AddressLine2", addressdetails.AddressLine2);
                command.Parameters.AddWithValue("@City", addressdetails.City);
                command.Parameters.AddWithValue("@County", addressdetails.County);
                command.Parameters.AddWithValue("@Country", addressdetails.Country);
                command.Parameters.AddWithValue("@PostCode", addressdetails.PostCode);
                command.Parameters.AddWithValue("@IsCopy", addressdetails.IsCopy);
                connection.Open();
                int insertId = (int)command.ExecuteScalar();
            }
        }
    }

The SELECT SCOPE_IDENTITY() AS 'InsertedId' line of code is ment to return the AddressID. When inserting the SQL into the Sql Server query tool, the sql is successfully executed also the AddressID is returned successfully.

However, whenever i reach the line of code

int insertId = (int)command.ExecuteScalar();

it returns a Specified cast is not valid error.

Any ideas where im going wrong?

 

[vbkris]

hi,

upto my knowledge the executescalar method will execute the first SQL and try to return back the value for the same (which is basically an insert).

try this:

connection.Open();
SqlDataReader Dt=command.ExecuteReader();
Dt.NextResult();
int insertId=0;
if(Dt.Read())
int insertId= Dt(0);
Dt.Close();

Known is handfull, Unknown is worldfull

 

[tperri]

Actually in your SQL you should return @scope_identity() and use ExecuteNonQuery.

Make sure you add a command parameter for your return value and you should get what you need.

 

[vbkris]

hi tperri,

the usage is correct:

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

>>Make sure you add a command parameter for your return value and you should get what you need

how would you do that if there is no SPs involved? each stmt is treated as a seperate SQL....

Known is handfull, Unknown is worldfull

 

[tperri]

insert Statement; return @scope_identity()? why wont that work unless it's in a stored proc?

 

[vbkris]

return command? hmm i have not used that outside an SP. let me check that and come back to you...

Known is handfull, Unknown is worldfull

 

[vbkris]

and one more thing,

return @scope_identity()

should be:
return scope_identity()

the @ is not there, i had a doubt there, therefore i did some research (GOOGLE ) and came up with that MSDN link...

Known is handfull, Unknown is worldfull

 

[dazza12345]

I have got it to work, all that was wrong was command.ExecuteScalar(); retruns a object value.

Im pretty much sorted appart from a small error: The name 'insertidint' does not exist in the current context.

Simple, im sure. Bit i just cant sort it.

    public static short CreateAddressDetails(AddressDetails addressdetails)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
  
                command.CommandText = "INSERT INTO AddressDetails " +
                    "(HouseNumber, AddressLine1, AddressLine2, City, County, Country, PostCode, IsCopy) " +
                    "VALUES(@HouseNumber, @AddressLine1, @AddressLine2, @City, @County, @Country, @PostCode, @IsCopy); SELECT SCOPE_IDENTITY() AS 'InsertedId';";

                command.Parameters.AddWithValue("@HouseNumber", addressdetails.HouseNumber);
                command.Parameters.AddWithValue("@AddressLine1", addressdetails.AddressLine1);
                command.Parameters.AddWithValue("@AddressLine2", addressdetails.AddressLine2);
                command.Parameters.AddWithValue("@City", addressdetails.City);
                command.Parameters.AddWithValue("@County", addressdetails.County);
                command.Parameters.AddWithValue("@Country", addressdetails.Country);
                command.Parameters.AddWithValue("@PostCode", addressdetails.PostCode);
                command.Parameters.AddWithValue("@IsCopy", addressdetails.IsCopy);
                connection.Open();

                object insertid = command.ExecuteScalar();
                decimal insertiddec = (decimal)insertid;
                short insertidint = (short)insertiddec;
            }
        }
        return insertidint;
    }

Think i must define insertidint somewhere, but not sure where.

Cheers for all ur help guys

 

[tperri]

Have you tried to change this:

SELECT SCOPE_IDENTITY() AS 'InsertedId'

to this

RETURN SCOPE_IDENTITY


and this

object insertid = command.ExecuteScalar();

to this

object insertid = command.ExecuteNonQuery();

 

[BoulderBum]

ExecuteNonQuery() returns the number of rows affected. ExecuteScalar() is correct for getting a single value returned from a query.

What's wrong is that you're trying to return a variable that's out of scope. Try moving the definition:

short insertidint;

...above the using statement and change the assignment to:

//don't redefine variable.
insertidint = (short)insertiddec;