Determine the Next SPListItem ID in a MOSS SPList?

Question

Hi,

I am trying to determine what the ID of the next list item to be created in a MOSS list will be. Is there any way of doing this?

Thanks, MagicAndi

Answer 1

The following CAML query would determine the most likely ID to be generated next:

int iNextID = 1;

try
{
    using (SPSite objSite = new SPSite(sSiteUrl))
    {
     using (SPWeb objWeb = objSite.OpenWeb())
     {
      SPList objList = objWeb.Lists[sListName];

      SPQuery objQuery = new SPQuery();
      // objQuery.RowlImit = 1;
      objQuery.Query = "<OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy><RowLimit>1</RowLimit>";
      objQuery.Folder = objList.RootFolder;

      // Execute the query against the list
      SPListItemCollection colItems = objList.GetItems(objQuery);

      if (colItems.Count > 0)
      {
       string sMaxID = colItems[0]["ID"].ToString();
       iNextID += int.Parse(sMaxID);
      }
     }
    }
}
catch (Exception ex)
{
    ...
}

return iNextID;

However, this does not work for the case where the most recently generated list item (with ID N) is deleted, and we then use the above code to give the next ID - it will return N, when it should be N+1.

Answer 2

Look at this link: http://snipplr.com/view/24210/next-splistitem-id-from-splist/, I have tried this solution and it does overcome the other shortcomings, it involves querying the WSS content DB which can be a little bit more expensive, but you could implement a hybrid solution, where you would query the DB ONLY if colItems.Count == 0 after the other logic.

Regards!

Answer 3

If you want to be more sure you can create a listitem, record its ID, delete the list item and the next listitem will have ID+1. This should overcome the case where the last item was deleted.

Of course this is quite uqly.. but so is reading from the content database.

May I ask why you need it? Alternatively you can add a column to your list that contains a GUID so you can pre-generate ID's yourself.

Answer 4

I found another solution to this problem.

The problem I had was related to versions:

var newItem = myList.Items.Add();
newItem["Title"] = "whatever";
newItem.Update();
newItem["MyProperty"] = "whatever" + newItem.ID;
newItem.Update();

I had versioning on my list but didn't want to create two different versions of my new item. Solved it like this:

var newItem = myList.Items.Add();
newItem["Title"] = "whatever";
newItem.Update();
myList.EnableVersioning = false;
myList.Update();
newItem["MyProperty"] = "whatever" + newItem.ID;
newItem.Update();
myList.EnableVersioning = true;
myList.Update();

One might think that turning versioning off would erase all versions except the current but it doesn't.

This doesn't give you the next ID but might solve the underlying problem for someone.