1

I have used the following code to get one more than the biggest ID in a table using razor

@foreach (var top in db.Query("SELECT MAX(ID)+1 as ID FROM mytable"))
            {
                if (@top.ID == null) {@top.ID = 1; }
            }

if the table is empty, @top.ID returns null. I am trying to set its value to 1 if it is null. It however shows an error in the assignment part. How do you assign the value of @top.ID to something else? Or is there a way to user the sql query to set ID as 1 if the table is empty?

Neville Nazerane
  • 6,622
  • 3
  • 46
  • 79

1 Answers1

1

You can use 

 select isnull(max(ID),1) from YourTable

However, if you're trying to generate a new ID for a new record, you'd be better advised to use an identity field.

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • I tried identity field with alter table it was showing errors – Neville Nazerane Oct 20 '13 at 09:29
  • sorry did not complete the previous comment I tried identity field with alter table it was showing errors ALTER TABLE tblDDWeatherTimedValues ALTER COLUMN ID int IDENTITY(1,1) PRIMARY KEY it shows me an error at identity I will however need the above code too some other proposes. And your code works thanks a lot. – Neville Nazerane Oct 20 '13 at 09:35
  • any idea about why my sql query is not working? ALTER TABLE mytable ALTER COLUMN ID int IDENTITY(1,1) PRIMARY KEY – Neville Nazerane Oct 20 '13 at 10:39
  • @NevilleNazerane See http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column – podiluska Oct 20 '13 at 10:42