Can I use executescalar for update the record

Question

I am using SQL Server 2005 and c#. I want to get primary key value when I insert record into database.

For this I have a C# method in which I insert and update records. I am using ExecuteScalar method for inserting and updating.

Insert record is successful but when I edit record then no update is successful.

So tell me can I use ExecuteScalar method for update and insert record and HOW

Answer 1

Generally, ExecuteScalar method is used for queries which return at least one field of data (e.g. result from SELECT query). If you just want something to be executed, you should use ExecuteNonQuery method instead.

Even with ExecuteNonQuery method you could still read values of parameters after function completed. For example, your command could be something like:

INSERT INTO TableName(someFields) VALUES (someValues)
SET @Id = SCOPE_IDENTITY()

if you're using autoincrement key or

SET @Id = newid()
INSERT INTO (ID, someFields) VALUES (@id, someValues)

if you're using uniqueidentifier as a key.

After that you could add output @Id parameter to the command, call ExecuteNonQuery and after that read the value from parameter.

Answer 2

With ExecuteScalar, you should be able to execute any query (including insert/update) although, it makes more sense to use ExecuteNonQuery for update operation as generally, you don't expect any result. What happens when you do Update operation? Do you get any exception or the record simply does not get updated? If former then share the exception details. If later (i.e. record does not get updated) then check the value of primary id field that you are passing (I believe via some parameter). If its null or zero the no record will be updated.