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:)

Answer 1

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