get last identity of table

Question

iam trying to get the last identity of my table "image"... the following code isnt working..plz help

SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DSN"]);
                SqlCommand ident = new SqlCommand("SELECT IDENT_CURRENT(‘image’)", connection);
                connection.Open();
                id = Convert.ToInt32(ident.ExecuteScalar());

:
:
:

I NEED THIS SO THAT EVEN IF I DELETE LAST ROW I CAN GET THE LAST AUTOGENERATED IDENTITY

Answer 1

For MySQL, your code looks like it should work. If the command is not working, what error message do you receive?

For SQL Server, you can retrieve the last generated identity from the sys.identity_columns table:

select last_value
where name = 'ColumnName'
and object_id = object_id('TableName')

Answer 2

@Andomar my application uploads a file on server and save it to db and bind it to (gridview with remove link for each row)

if i use last_value iam getting the same last id evry time

SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DSN"]);
                SqlCommand ident = new SqlCommand("select LAST_VALUE where name = 'Id' and object_id = object_id('image')", connection);
                connection.Open();
                id = Convert.ToInt32(ident.ExecuteScalar());



                    _fileName = fileName;

                    _fullFileName = _outputPath  + (id + 1).ToString() + prefix + Path.GetFileName(_fileName);

                    _fs = new FileStream(_fullFileName, FileMode.Create);
:
:
:

my problem is that wen i was using MAX(Id) like below ,it was all fine until i loose my identity when someone deletes the last row or MAX row ....that is why i am trying to get the last autoincremented identity not the last value of id coloumn

SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DSN"]);
SqlCommand ident = new SqlCommand("SELECT MAX(Id) from image", connection);
 connection.Open();


            SqlDataReader result = ident.ExecuteReader();
            result.Read();

                object obValue = result.GetValue(0);
                id = Convert.ToInt32(obValue.ToString());


            connection.Close();

Answer 3

SELECT
SCHEMA_NAME( OBJECTPROPERTY( C.OBJECT_ID, 'SCHEMAID' )) AS [SCHEMA NAME],
OBJECT_NAME( C.OBJECT_ID ) AS [TABLE NAME], C.NAME AS [COLUMN NAME],
T.NAME AS [COLUMN DATA TYPE],SEED_VALUE,
INCREMENT_VALUE,LAST_VALUE AS CURRENTMAXVALUE
FROM SYS.IDENTITY_COLUMNS C INNER JOIN SYS.TYPES T ON C.USER_TYPE_ID = T.USER_TYPE_ID
WHERE COLUMNPROPERTY(OBJECT_ID, C.NAME, 'ISIDENTITY') = 1
AND LAST_VALUE IS NOT NULL
ORDER BY LAST_VALUE DESC