c# and SQL Server problem.

  • Thread starter Thread starter Jacek Jurkowski
  • Start date Start date
J

Jacek Jurkowski

My application is using an identity field of an SQL Server.
Identity field called "ID" is a Primary Key too. The problem
is when i add some record to the table SQL Server is
assigning it sime new id. But thouse is not returned into a
Data Table so the id number on SQL Server is not equal
thouse in the data table. So when I'm trying for exemple
delete row, it causes an error because UPDATE command
cannot find an ID on the SQL Server equal to the DataSet Table.
Close and re open table solves that problem but is not acceptable
for performance goals ...

How to make DataSet Table to retrieve id from the SQL Server
after insert command?
 
Jacek,

The only way to do this would be to select the identity from SQL server
after the insert, like this:

select @@identity

This query will return the last inserted identity. You can then get
this value and set it on your row.

Either way, you will have to perform another query to get that value.

Hope this helps.
 
Jacek Jurkowski said:
My application is using an identity field of an SQL Server.
Identity field called "ID" is a Primary Key too. The problem
is when i add some record to the table SQL Server is
assigning it sime new id. But thouse is not returned into a
Data Table so the id number on SQL Server is not equal
thouse in the data table. So when I'm trying for exemple
delete row, it causes an error because UPDATE command
cannot find an ID on the SQL Server equal to the DataSet Table.
Close and re open table solves that problem but is not acceptable
for performance goals ...

How to make DataSet Table to retrieve id from the SQL Server
after insert command?

Are you using a data adapter? If so just change your INSERT statement to
look like this:

INSERT INTO customers(name, addr, city, state, zip) VALUES(@name, @addr,
@city, @state, @zip) ; SELECT id, name, addr, city, state, zip FROM
customers WHERE id = @@IDENTITY

Similarly, for the UPDATE statement, you should do something like:

UPDATE customers SET name = @name, ... WHERE id = @id ; SELECT id, name,
addr, city, state, zip FROM customers WHERE id = @id

This will catch any timestamp or similar columns that sql might have updated
but your app otherwise wouldn't know about.

HTH,

Mike Rodriguez
 
So maby I should replace identity field with
Guid assigned by myself? Another select will
slow down the performance and the mathod
with changing the INSERT command ... The
problem is that command is generated automatically
by CommandBuilder and I would like to keep that way ...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top