0

I've got a question regarding how to retrieve the auto-increment or identity value for a column in SQL Server 2005, when said column is not the first declared column in a table.

I can get the generated value for a table just by issuing the following code:

MyTable newRecord = new MyTable();
newRecord.SomeColumn = 2;
newRecord.Save();
return newRecord.MyIdColumn;

Which works fine regardles of how many other columns make up the primary key of that particular table, but the first column declared MUST be the identity column, otherwise this doesn't work.

My problem is that I have to integrate my code with other tables that are out of my reach, and they have identity columns which are NOT the first columns in those tables, so I was wondering if there is a proper workaround to my problem, or if I'm stuck using something along the lines of SELECT @@IDENTITY to manually get that value?

Many thanks in advance for all your help!

Fervelas
  • 73
  • 6
  • 1
    SubSonic doesn't set the key by ordinal position - if you're using 2.2 then after save the SetPrimarykey method goes off and asks the schema which prop to set. So - the question is, what does "doesn't work" mean? Do you get an error? –  Jan 14 '10 at 23:54
  • 1
    Nope, no error at all. "Doesn't work" means that if I have a table with two int columns, col1 and col2, and only col2 is identity, then when I do a .Save() and then check for the value of col2, it returns 0 instead of the just-generated key value. If I move col2 as the first column instead of col1 and perform the same code as before, then the generated key value is correctly returned. I've tried this many times and it always gives me consistent results; I have to have my identity column as the first column, otherwise I can't access the key value of my recently inserted record. – Fervelas Jan 15 '10 at 03:34

1 Answers1

0

From the "ewww gross" department:

Here's my workaround for now, hopefully someone may propose a better solution to what I did.

MyTable newRecord = new MyTable();
newRecord.SomeColumn = 2;
newRecord.Save();
CodingHorror horror = new CodingHorror();
string SQL = "SELECT IDENT_CURRENT(@tableName)";
int newId = horror.ExecuteScalar<int>(SQL, "MyTable");
newRecord.MyIdColumn = newId;
newRecord.MarkClean();
return newRecord.MyIdColumn;
Fervelas
  • 73
  • 6