CODEWITHSUNDEEP
c#sql-server
DinoBaba
DinoBaba

Reputation: 67

How to get last value inserted in a database to generate new one

Using Text Area I'm sending Values to database by clicking button, but I want postID to auto generate how I can do it?

protected void btnPost_Click(object sender, EventArgs e)
{
    string PostQuery = "INSERT INTO [Post] (PostID,PostDet,Votes,UserID) VALUES('<auto Genatare post ID>','" + TxtPost.Text + "','12','Us001')";
    dbClass.ConnectDataBaseToInsert(PostQuery);
    Response.Redirect("~/computing.aspx");
}

Upvotes: 0

Views: 160

Answers (5)

Rahul Chaturvedi
Rahul Chaturvedi

Reputation: 1

Try this..

select * from tablename order by id desc limit 1

where id would be your primary key attribute name.

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

You could make PostID a UNIQUEIDENTIFIER column and then pass in a newly generated GUID (Guid.NewGuid()).

Also, please use parameterized queries to avoid SQL injection. Especially if the inputs come directly from WEB users.

To do so, change your ConnectDataBaseToInsert method to not take SQL text, but an SqlCommand which you prepare with the respective parameters.

From your comment to the question: The PostID should be like PO0001. Then the only way to do it properly and to respect for concurrency is to generate a stored procedure that takes the value to insert, which generates the ID itself.

To do so, create a new table that contains the last post ID. Then, use an UPDATE ... OUTPUT statement to increment and return in one go. This is the only way to do an atomic update of the post ID so that no two users create the same ID.

Example Table PostIDTable

Current
=======
0

Example SELECT to update and retrieve the current post ID:

-- We need a temp table, because OUTPUT can not output into a single variable
-- This increments Current by one and outputs the value that was set in one go.
-- This prevents simultaneous calls to get the same ID
DECLARE #postID (ID INT)
UPDATE PostIDTable
OUPUT INSERTED.Current INTO #postID
SET
    Current = Current + 1

-- Get the value from the temp table and convert it into the desired format
DECLARE @pID INT = (SELECT TOP 1 ID FROM #postID)
DECLARE @id NVARCHAR(6) = 'PO' + RIGHT('0000' + CONVERT(NVARCHAR, @pID), 4)

-- Do the actual INSERT
INSERT INTO (PostDet, Votes,UserID) VALUES (@id, ..., ...)

Upvotes: 1

Jaroslav Kubacek
Jaroslav Kubacek

Reputation: 1447

the best way is to use auto increment column ID for your table. Please, see w3school tutorial where is described construction for all main db.

Upvotes: 0

Oscar Bralo
Oscar Bralo

Reputation: 1907

If PostId is autogenerated, only do this:

protected void btnPost_Click(object sender, EventArgs e)
{
    string PostQuery = "INSERT INTO [Post] (PostDet,Votes,UserID) VALUES('" + TxtPost.Text + "','12','Us001')";
    dbClass.ConnectDataBaseToInsert(PostQuery);
    Response.Redirect("~/computing.aspx");
}

Removing PostId from your query, it will generate the next Id

Upvotes: 0

Christos
Christos

Reputation: 53958

You should make the PostID column to be an IDENTITY(1,1) column (in case of using MSSQL. server). So when you will insert new rows to your database the corresponding PostID values will be autogenerated by your database. The same holds for each other RDBMS you may use.

Having done the above change you code will change to the following:

protected void btnPost_Click(object sender, EventArgs e)
{
    string PostQuery = "INSERT INTO [Post] (PostDet,Votes,UserID) VALUES("TxtPost.Text + "','12','Us001')";
    dbClass.ConnectDataBaseToInsert(PostQuery);
    Response.Redirect("~/computing.aspx");
}

Upvotes: 0

Related Questions