retrieving the highest IDENTITY number in a table:sqlserv05?

[giddy_guitarist]

hi, According to my knowledge , when a column is an IDENTITY it increments by 1 , now even if a row has a value of , lets say 5 , and i delete it , the next row will have a value that increments the previous value , even if it was deleted. So i need to figure the Next number that will get into the IDENTITY column or the highest number(including deletions) of that IDENTITY column. I'm working with C# 2.0 in .NET 2.0 using SQL Server 2005. Thanks so much Gideon

[Mark J Miller]

Use SCOPE_IDENTITY() like this: -- begin sql -- DECLARE @Id INT INSERT INTO myTable (val1, val2, val3) SET @Id = SCOPE_IDENTITY() -- end sql --

[giddy_guitarist]

hi Mark, Thanks so much for your reply but i'm afraid i have to be a little more annoying. How do i use the syntax you've mentioned??. I did search msdn and google but came up with some confusing results. In my little DataLayer , i have 2 small functions that [1] Execute a query and returns a DataTable and [2] execute a non-query sql statement. So far , i've only used non-query sql statements to do all my edit/delete operations. I dont know much about anything else like stored procedures or triggers:( I'm guessing i'm supposed to use stored procedures? Is this [^]a good article? Thanks so much Gideon

[giddy_guitarist]

guess a little more searching and reading is all it took! I put it all in a stored proc and It works a treat now!:) Thanks so much.