Get next ID which will be used in a SharePoint List/Library

Question

Hi,

I use the client object model in a .net application.
Is it possible to read the next ListItem-ID that will be set for the next added item ?

For examle I created items with Ids from 1-10. then I delete 8-10.
Now the next ID for a new item would be 11.
And I want get this ID before I add the item.

Thanx
Stephan

Answer 1

Hi,

You need to query list to get the last item, and using that item get the item ID.

SPList list = // some list;
SPQuery query = new SPQuery();
query.RowLimit = 1;
query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>
";

return list.GetItems(query).Cast<SPListItem>().FirstOrDefault();

You can covert the same for client object model.

---

-Prashant

Answer 2

Hi Prashant,

the problem is, when I delete the last items (look my example),

the ID is still higher then the really last item.

When you delete 8,9,10 then the last item in the list is ID = 7.

But the next free ID is 11.

Stephan

Answer 3

The solution is given here : http://www.codedigest.com/Articles/Sharepoint/279_How_do_I_tell_what_the_next_list_item_ID_is.aspx

SPSite site = new SPSite("sharepoint site URL");
int NextID = 0;
if(site.WebApplication.ContentDatabases.Count > 0)
{
       string DBConnString = site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
       SqlConnection con = new SqlConnection(DBConnString);
       try
       {
              con.Open();
              SqlCommand com = con.CreateCommand();
              com.CommandText = String.Format("select tp_NextAvailableId from AllLists where tp_ID = '{0}'", listId.ToString());
              NextID = (int)com.ExecuteScalar();
       }
       finally
       {
              con.Close();
       }
}

---

Cordialement/Regards,

Ludovic Caffin
.NET/SharePoint Consultant for A3IS.

Answer 4

Hi,

Please check following code and replace item Id (1) to the id that you want:

 using (SPSite site = new SPSite("SiteURL"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                          SPList list = web.Lists[new Guid("ListGUID")];

                          SPQuery query = new SPQuery();
				// Item ID
                          query.RowLimit = 1;
                           string queryStr = String.Concat("<Where>",
                                                                "<Gt>",
                                                                    "<FieldRef Name='ID' />",
                                                                    "<Value Type='Integer'>1</Value>",
                                                                "</Gt>",
                                                           "</Where>",
                                                           "<OrderBy><FieldRef Name='ID' Ascending='True' /></OrderBy>");
                          query.Query = queryStr;

                        SPListItem item =   list.GetItems(query)[0];

                    }
                }

How to use CAML with Client Object Model: http://karinebosch.wordpress.com/2012/02/03/caml-and-the-client-object-model/

---

Senior Sharepoint Developer,Exceed

Answer 5

Hi Ludovic,

I use Client-Object-Model, I think I don´t have to access to ContentDatabases with it.

Stephan

Answer 6

Stephan,

Why do you need to get the next available ID?  If we have a better idea of what you're trying to do, maybe we can help you find another solution.

---

Bob Guidinger
Please remember to click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

Answer 7

Ah yes, you are right... :)

---

Cordialement/Regards,

Ludovic Caffin
.NET/SharePoint Consultant for A3IS.

Answer 8

Hi Bob,

I write a little program where you can set the next ID in a list manuelly,
For examle you want to have the next ID = 1000.
I creat 1000 items and delete them.
And befor I start the program I want to check if the ID which will be filled by the user is creater than next DB-ID.

Stephan

Answer 9

So,

• the SQL query I gave you seems the only way to get the next ID as this information can only be found on the content database.
• the client object model let us work with a site collection but not with a web application, and so we can't get the content database connection string by this way.

Then,

• I suggect you to create a web service which will return the content data base connection string (if you want to manage the connection from your application) or directly the next id. You will then be able to call this web service from your application.

Walkthrough: Creating a Custom ASP.NET Web Service - http://msdn.microsoft.com/en-us/library/ms464040.aspx

---

Cordialement/Regards,

Ludovic Caffin
.NET/SharePoint Consultant for A3IS.

Answer 10

Okay, but why?  Why would you want to increase the ID's artificially like that?  To me that just seems like a recipe for disaster.

Although Ludovic's solution will work, reading from the content database directly through SQL is not supported by Microsoft.  If you do need the ID's to show up in a certain number range, perhaps it would be better to add a second 'ID' column and use a workflow to compute the new ID and place it in that column.

---

Bob Guidinger
Please remember to click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

Answer 11

There is no real reason to manually specify the ID of an item.  If you want to access an item differently, or on some fixed url, there are better solutions.  The id of an item is so strict for internal management of that item in sharepoint.  The sharepoint object model does not expose the tp_NextAvailableId stored procedure.  Using it in code is not guaranteed to work in the future on cumulative updates, service packs, sql upgrades, etc.

Also, creating 1000 items and deleting them.... I cringe at the thought.  If you give us a little more information on why you have written this program "Why do people need to manually specify a list item id?" we can give you another solution.

---

My Blog: http://www.thesug.org/Blogs/ryan_mann1/default.aspx Website: Under Construction

Answer 12

Hi Ryan,

the reason is that we need the SharePoint Document-ID to get an unique value of the items
The last value of the Document-ID is the DB-ID:  XXXXX-XX-ID

So we have an old system and migrate the old system ids by overwriting the SharePoint Document-ID.

XXXXX-XX-OLDID

Then the user can find there old documents by the old ID.
The new added documents will get a new Document ID. But the user want to set the starting ID for new documents.

So I create and delete dummies until I reach this ID.
For example they want to start at 10.000.
The next new document has the Document-ID XXXXX-XX-10000.
The migrated documents are then bellow 10.000.

Thanx
Stephan

Answer 13

Hello,

Maybe you can try to store the information in the web property bag and modify it when you add a document with a function like this

public string GetIdForName()
        {
            int count = 10000;
            SPWeb web = SPContext.Current.Web;

            if (web.Properties.ContainsKey("ListDocumentID"))
            {
                count = int.Parse(web.Properties["ListDocumentID"].ToString());
                count++;
                web.Properties["ListDocumentID"] = count.ToString();
            }
            else
            {
                web.Properties.Add("ListDocumentID", count.ToString());
            }
            web.Update();

            return count.ToString();
        }

---

Best regards, Christopher.
Blog | Mail
Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.