Get RecordId from table

Question

I am new to SQL, I have table with RecordId that is incremented automatically and is primary key. I would like to get RecordId of the row that was inserted into table. Thanks in advance for help.

myCommand.CommandText = "INSERT INTO " + tableName + " (DateRaised,RaisedBy,WeekNo,Platform,Department,Site,Process, Area,NavErrorNo,RootCauseDescription,Status) " +
    "VALUES ('" + currentDate.ToString(format) + "','" +
    sender + "'," +
    weekNumber + ",'" +
    comboBoxPlatform.SelectedItem + "','" +
    comboBoxDepartment.SelectedItem + "','" +
    comboBoxSite.SelectedItem + "','" +
    comboBoxProcess.SelectedItem + "','" +
    comboBoxArea.SelectedItem + "','" +
    textBoxNavError.Text + "','" +
    textBoxIssue.Text + "','Open')";
//int lastInsertedId = 
myCommand.ExecuteNonQuery();

lastInsertedId should be int from RecordId in my table.

This code is a security hazard. Read about SQL Injection and parameterized queries. Do not use multiple tables with the same structure (as the concatenation of the table name implies). — Zohar Peled, Aug 02 '19 at 09:05

score 0 · Answer 1 · answered Aug 02 '19 at 09:16

0

First thing this is not a good idea to call direct SQL statement from code it can cause an issue for SQL injection as @Zohar suggested. You can either user parametrized query or sp.

Inside sp, you can use

SELECT @@IDENTITY AS 'Identity';

after Insert statement, it will return the last auto-incremented value for PK, then return this value as an output parameter and catch it after .ExecuteNonQuery(); in C# code.

answered Aug 02 '19 at 09:16

Tarun Dudhatra

76
1
8

could you change my code to proper way it should be written please? – Pawel G Aug 02 '19 at 09:20
I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else (like `@@IDENTITY`) to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s Aug 02 '19 at 09:21
@marc_s this is what i am looking for but have no idea how to change my code to right practices. – Pawel G Aug 02 '19 at 09:29
@PawelG: added a response of my own showing the valid ways of doing this, IMHO – marc_s Aug 02 '19 at 09:35

marc_s · Accepted Answer · 2019-08-02T09:55:01.460

To do this properly (if this is for SQL Server - you weren't very clear on this), I see two options:

Approach #1 - using SCOPE_IDENTITY

This works well if you're only ever inserting a single row at a time - use something like this:

// set up your query using *PARAMETERS** as you **ALWAYS** should! 
// Using SELECT SCOPE_IDENTITY() to get back the newly inserted "Id"
myCommand.CommandText = "INSERT INTO dbo.SomeTable (list-of-columns) " +
                        "VALUES (@param1, @param2, @param3, ...., @paramN); " +
                        "SELECT SCOPE_IDENTITY();";

// set up the parameters and theirs values

object result = myCommand.ExecuteScalar();

if (result != null)
{ 
    int lastInsertedId = Convert.ToInt32(result);
}

Approach #2 - using the OUTPUT clause

This works well even if you insert multiple rows at once (typically using a SELECT after the INSERT):

// set up your query using *PARAMETERS** as you **ALWAYS** should! 
// Using SELECT SCOPE_IDENTITY() to get back the newly inserted "Id"
myCommand.CommandText = "INSERT INTO dbo.SomeTable (list-of-columns) " +
                        "OUTPUT Inserted.RecordId " + 
                        "VALUES (@param1, @param2, @param3, ...., @paramN); ";

// set up the parameters and theirs values

object result = myCommand.ExecuteScalar();

if (result != null)
{ 
    int lastInsertedId = Convert.ToInt32(result);
}

I will try approach 2, it will take me some time to setup params as i never did it before. Thanks @marc_s — Pawel G, Aug 02 '19 at 10:12

score -1 · Answer 3 · answered Aug 02 '19 at 09:14

This should do the trick for You

private void SelectLast()
        {

            string sqlLast = "SELECT TOP(1) RecordId FROM [YourtableName] ORDER BY 1 DESC";

            Connection.Open();
            using (SqlCommand cmd = new SqlCommand(sqlLast, Connection))
            {
                cmd.CommandType = CommandType.Text;
                {
                    int insertedID = Convert.ToInt32(cmdAdd.ExecuteScalar());
                    textBoxID.Text = Convert.ToString(insertedID);
                }
                Connection.Close();
            }

        }

Get RecordId from table

3 Answers3