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
– Christos, Commented Jan 18, 2015 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
– Mariusz Wyglądała, Commented Jan 18, 2015 at 20:00

Steve · Accepted Answer · 2015-01-18 21:34:45Z

3

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

edited Jan 18, 2015 at 21:34

answered Jan 18, 2015 at 19:47

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mariusz Wyglądała Over a year ago

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??

Steve Over a year ago

Yes, SCOPE_IDENTITY 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

Community · Accepted Answer · 2017-05-23 11:43:30Z

0

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

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jan 18, 2015 at 19:39

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related