The parameterized query is truncated and parameters are lost

Question

I have a piece of code:

command.Parameters.Clear();
command.Parameters.Add(ownerUserIDParam);
command.Parameters.Add(buddyUserIDParam);
command.Parameters.Add(timestampParam);
command.Parameters.Add(new SqlParameter("@GiftID", giftID));
command.Parameters.Add(new SqlParameter("@GiftStatus", (byte)GiftStatusEnum.wait));
command.CommandText = "INSERT INTO SentGefts (OwnerUserID, BuddyUserID, CreateTS, GiftID, Status, AcceptRejectTS, ParentEntityType, ParentEntityID) VALUES (@OwnerUserID, @BuddyUserID, @TS, @GiftID, @GiftStatus, @TS, 0 ,0);";
command.CommandText += "SELECT @@IDENTITY;";
result.GiftInstanceID = long.Parse(command.ExecuteScalar().ToString());

And I recieve: The parameterized query '(@OwnerUserID int,@BuddyUserID int,@TS datetime,@GiftID int,@Gif' expects the parameter '@GiftStatus', which was not supplied.

Note: '(@OwnerUserID int,@BuddyUserID int,@TS datetime,@GiftID int,@Gif' is truncated and is exactly 64 symbols... And it just ends on an unfinished paramter name 'Gif' (and the exception is also about this parameter).

Why it can't see my parameter?

UPD: If I replce adding the last parameter (@GiftStatus) this way: command.Parameters.AddWithValue("@GiftStatus", (byte)GiftStatusEnum.wait);

This way things start working. But I just can't figure out what's wrong with .Add(new SqlParamter());

Answer 1

You need to supply all parameters along with their names.
You'd better use Paramteres.AddWithValue(...). So:

Parameters.AddWithValue("@OwnerUserID", ...);
Parameters.AddWithValue("@BuddyUserID", ...);
Parameters.AddWithValue("@TS", ...);
Parameters.AddWithValue("@GiftID", ...);
Parameters.AddWithValue("@GiftStatus", ...);

Answer 2

I think you are missing the following command before ExecuteScalar()

command.CommandText += "SELECT @@IDENTITY;";
command.CommandType = CommandType.Text;
result.GiftInstanceID = long.Parse(command.ExecuteScalar().ToString());