New DataRow in SQL-Server with ID
-
I want to insert a row in a table which has a PK in the first field. I make a INSERT and give parameters to the sql server for all fields except the id field which is set in the db as Autoincrement so i dont have to worry about. ALL of this is working fine. But now i want to work immediatly after the insert with the new id of this row, which was given to the row automaticly by the sql-server. My problem now is that i have no Idea how to get the id value into my code (sure I can make a select query questioning all the parameters which i still have from the insertcommand, and ask for the id but this way seems very complicated to me!!!) So if any more experienced Programmer than me can help me, please!!! 10000 Thanks in advanced. PS Languages C#, ADO.Net, and Sql Server 2000 best regards helli
-
I want to insert a row in a table which has a PK in the first field. I make a INSERT and give parameters to the sql server for all fields except the id field which is set in the db as Autoincrement so i dont have to worry about. ALL of this is working fine. But now i want to work immediatly after the insert with the new id of this row, which was given to the row automaticly by the sql-server. My problem now is that i have no Idea how to get the id value into my code (sure I can make a select query questioning all the parameters which i still have from the insertcommand, and ask for the id but this way seems very complicated to me!!!) So if any more experienced Programmer than me can help me, please!!! 10000 Thanks in advanced. PS Languages C#, ADO.Net, and Sql Server 2000 best regards helli
Typically, you add a SELECT statement after your INSERT statement when used with a
SqlDataAdapter(fills aDataSetfrom a SQL Server database usingFill, or vice versa usingUpdate). This makes sure that yourDataSetis filled with what SQL Server assigned to the PK field. To include both an INSERT and SELECT, separate your statements with a semi-colon (;):sqlDataAdapter1.InsertCommand = new SqlCommand(
"INSERT INTO MyTable Name VALUES (@Name); SELECT ID, Name FROM MyTable",
sqlConnection1);Microsoft MVP, Visual C# My Articles
-
I want to insert a row in a table which has a PK in the first field. I make a INSERT and give parameters to the sql server for all fields except the id field which is set in the db as Autoincrement so i dont have to worry about. ALL of this is working fine. But now i want to work immediatly after the insert with the new id of this row, which was given to the row automaticly by the sql-server. My problem now is that i have no Idea how to get the id value into my code (sure I can make a select query questioning all the parameters which i still have from the insertcommand, and ask for the id but this way seems very complicated to me!!!) So if any more experienced Programmer than me can help me, please!!! 10000 Thanks in advanced. PS Languages C#, ADO.Net, and Sql Server 2000 best regards helli
I think you need to use "IDENTITY/IDENT_CURRENT or SCOPE_IDENTITY" method of SQL. But I don't know how you can use it. If you get full source code please let me know.