Some points here:
1. What are you trying to do with the DataAdapter here? DataAdapter is used
as bridge between DataSet(DataTable) and data store. If you simply want to
execute some SQL code (dynamic SQL or store procedure), just use SqlCommand,
no need to put Command into a DataAdapter.
2. From what I saw, you are inserting record and then want to get the
record's ID, which is Identity column in database. Since you use SQL Server,
the better approach is to use SqlCommand to pass parameters to a stored
procedure, which uses those parameters to insert a new record and return the
record ID. This way, your app only need one round trip to the Sql Server,
instead of two as your code does, one for inserting and one for retrieving
(and retriving possible wrong ID, as you are experiencing).
3. SCOPE_IDENTITY(), as its name implies, only gets the newly generated ID
in certain scope. In your case, you try to call it in a seperate trip to the
SQL Server, of course you get nul, because the inserting execution has been
done in previour trip and out of SCOPE already.
4. Yes, @@Identity MAY give you correct ID, as you have seen. But it is not
reliable, especially in you case (tow seperate trips to get the ID), because
after the execution of inserting and before your next call to @@Identity,
other user may also insert a new record. If so, your second trip to call
@Identity will definitely give you wrong ID.
So, as I mentioned, you'd better use SP to do the inserting and returning ID
in one shot, like this
CREATE PROCEDURE InsertNewRecord
(
@ID int OUTPUT
@Col1 ..
@Col2..
...
)
AS
INSERT INTO TheTable (Col1,Col2...) VALUES (@Col1,@Col2...)
SET @ID=SCOPE_IDENTITY()
RETURN
The in your app, you just create a SlqCommand and populate its Parameters
collection and call Command.ExcuteNonQuery(). Then retrieve ID from output
parameter:
int ID=(int)cmd.Parameters["@ID"].Value;
"Hardy Wang" <ha*******@hotmail.com> wrote in message
news:eW**************@TK2MSFTNGP15.phx.gbl...
Hi all,
I have the following codes, but SCOPE_IDENTITY() just returns NULL to
me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it
works fine!! Since I have a trigger on the table, I have to use
SCOPE_IDENTITY(). Any ideas?
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
//Create the dataadapter
SqlDataAdapter da = new SqlDataAdapter();
//Assign the connection & Create and execute the Insert Command
da.InsertCommand = new SqlCommand("insert into table1......");
da.InsertCommand.Connection = conn;
da.InsertCommand.ExecuteNonQuery();
//Create,assign and Execute the Identity statement
da.SelectCommand = new SqlCommand("SELECT SCOPE_IDENTITY()",
da.InsertCommand.Connection);
//da.SelectCommand = new SqlCommand("SELECT @@IDENTITY",
da.InsertCommand.Connection);
int intID = Convert.ToInt32(da.SelectCommand.ExecuteScalar());
conn.Close();
return(intID);
--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
