CODEWITHSUNDEEP
c#sql-serverlast-insert-id
user4340666
user4340666

Reputation: 1473

Get the last inserted string Id in SQL Server & C#

I have a DataTable BonLivraison with a primary key that is a string with the format 2016/ + increment number. I used this code to generate this primary key:

SqlCommand cmdRow = new SqlCommand("select TOP(1) CodeBonLivraison from BonLivraison ORDER BY 1 DESC", con);

string LastCode = (string)cmdRow.ExecuteScalar();
string getID = LastCode.Split('/')[1];
int getIntID = Convert.ToInt32(getID);

numeroBonReceptionTextBox1.Text = DateTime.Now.Year.ToString() + "/" + (getIntID + 1).ToString();

This code generates primary keys normally 2016/1...2016/2....until 2016/10 it re-generates 2016/10 which is primary key violation.

So how modify this code to get last inserted key and get 2016/11...2016/12...

Upvotes: 0

Views: 1077

Answers (2)

Roman Marusyk
Roman Marusyk

Reputation: 24579

Try something like this:

select TOP(1) CodeBonLivraison 
from BonLivraison 
ORDER BY CAST(SUBSTRING(CodeBonLivraison, 6, LEN(CodeBonLivraison ) - 5) as int) DESC

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

change your query to

select TOP(1) CodeBonLivraison 
from   BonLivraison 
ORDER BY convert(int,
                 right(CodeBonLivraison, charindex('/', reverse(CodeBonLivraison )) - 1)
                 ) DESC

Upvotes: 1

Related Questions