get last identity of table

I am tryin开发者_Python百科g to get the last identity of my table image.

The following code isn't working:

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 the last row I can get the last autogenerated identity.

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

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')

SELECT IDENT_CURRENT(‘image’) - 1

Assuming you are increamenting by 1

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 I am getting the same last id every 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 when I was using MAX(Id) like below, it was all fine until I lose 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 column

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();