New Row into SQL Server with Data Set [asp.net, c#]

Question

I have this code:

SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 

cnn.Open();

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from Szkoda";
cmd.Connection = cnn;

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;

DataSet ds = new DataSet();
da.Fill(ds, "Szkoda");

SqlCommandBuilder cb = new SqlCommandBuilder(da);

DataRow drow = ds.Tables["Szkoda"].NewRow();

drow["Likwidator"] = tbLikwidator.Text;
drow["FirmaObslugujaca"] = DdFirma.Text;
drow["StanSzkody"] = DdStan.Text;
drow["CzyRegres"] = DdRegres.Text;
drow["KrajZdarzenia"] = DdKraj.Text;

ds.Tables["Szkoda"].Rows.Add(drow);

da.Update(ds, "Szkoda");

The question is how to get the inserted record ID? I read about scope but I don't know how I can use this in above code.

I want to get last ID to redirect to view form after save new record. I'm looking for simplest solution:)

Welcome to SO. From the code you have posted I don't see anywhere any insert statement. You have built a sql command that selects all the records from the table called `Szkoda`. Where is the insert ? Thanks — Christos, Jan 18 '15 at 19:37
i select all columns from "szkoda" and insert record by a datarow-NewRow(). on the left you have columns from db, on the right values from textboxes on form. — Mariusz Wyglądała, Jan 18 '15 at 20:00

Steve · Accepted Answer · 2015-01-18T21:34:45.470

You can't do that directly from the Update command of the DataAdapter. You need to prepare a custom insert command that contains two commands. The first insert your record, the second one returns the last inserted id from your connection

string insertText = @"INSERT INTO Szkoda (Likwidator,FirmaObslugujaca, 
                      StanSzkody, CzyRegres, KrajZdarzenia) 
                      values (@lik, @fir, @sta, @czy, @kra);
                      SELECT SCOPE_IDENTITY()";

SqlCommand cmd = new SqlCommand(insertText, connection);
cmd.Parameters.AddWithValue("@lik", tbLikwidator.Text);
cmd.Parameters.AddWithValue("@fir", DdFirma.Text);
cmd.Parameters.AddWithValue("@sta", DdStan.Text);
cmd.Parameters.AddWithValue("@cay", DdRegres.Text);
cmd.Parameters.AddWithValue("@kra", DdKraj.Text);
object result = cmd.ExecuteScalar();
if(result != null)
{
   int lastInsertedID = Convert.ToInt32(result);
   // now insert the row in your dataset table but instead of
   // da.Update(ds, "Szkoda"); call 
   ds.Tables["Szkoda"].AcceptChanges();
}

Of course this should go alongside with your existing code, but instead of calling Update just call AcceptChanges to your datatable to confirm the new record in your table

Ok, @Steve i will use your solution for a moment. but tell me, if two users in the same time insert record, this users would get id of records added by him?? — Mariusz Wyglądała, Jan 18 '15 at 19:52
Yes, [SCOPE_IDENTITY](http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide) returns the ID generated in your connection and by your query. So if two users executes this code concurrently (more or less) each one receives the correct ID — Steve, Jan 18 '15 at 20:31

score 0 · Answer 2 · edited May 23 '17 at 11:43

Aftre insert the record into table(using sql query, not stored procedure) from c# code, you can use Get Records function to Select last record id(not recommended, because in muliuser case, this will be wrong) using max() fucntion.

select * from Szkoda  where ID IN (select max(id) from Szkoda)

If you are using Stored Procedure to insert data, then Use SCOPE_Identity() in stored procedure, and use Output parameter to get value in c# code.

CREATE PROCEDURE dbo.testSP
  @Col1 VARCHAR(50),
  @Col2  VARCHAR(20),
  @new_identity INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.TestTable(Col1, Col2) SELECT @Col1, @Col2;
    SET @new_identity = SCOPE_IDENTITY();
END
GO

Refer this Return identity of last inserted row from stored procedure

New Row into SQL Server with Data Set [asp.net, c#]

2 Answers2