CODEWITHSUNDEEP
c#sqlinsertcommandexecutenonquery
Pawel G
Pawel G

Reputation: 47

Get RecordId from table

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.

Upvotes: 1

Views: 239

Answers (3)

marc_s
marc_s

Reputation: 755207

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);
}

Upvotes: 0

Tarun Dudhatra
Tarun Dudhatra

Reputation: 76

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.

Upvotes: 0

Kuba Do
Kuba Do

Reputation: 155

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();
            }

        }

Upvotes: -1

Related Questions