3

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());

Алексей Медведев
  • 137
  • 1
  • 1
  • 7
  • Just a note, ExecuteScalar returns proper data types wrapped in an object. You are likely being given an `int` or a `long` so there is no need to do `.ToString` with the `Parse`. – Adam Houldsworth May 29 '12 at 13:36
  • Take a look at this http://stackoverflow.com/questions/2214628/insert-bytes-array-into-varbinarymax-record – John Gathogo May 29 '12 at 13:38

2 Answers2

3

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", ...);
Marco
  • 56,740
  • 14
  • 129
  • 152
  • Doesn't this line do the trick? command.Parameters.Add(new SqlParameter("@GiftStatus", (byte)GiftStatusEnum.wait)); – Алексей Медведев May 29 '12 at 13:35
  • 3
    you are combining the 2 things in your code (normal value and SqlParameter). AddWithValue is much easier to read and use... – RvdK May 29 '12 at 13:38
  • Actually I only add SqlParameter type values (ownerUserIDParam, ... - those are all of SqlParameter type). – Алексей Медведев May 29 '12 at 13:43
  • But if I change just line with GiftStatus parameter to AddWithValue way, things start working... Thank you, I am very happy with working code, but I just want to figure out what's wrong with .Add(new SqlParameter()); – Алексей Медведев May 29 '12 at 13:44
  • Also works fine this way: command.Parameters.Add("@GiftStatus", SqlDbType.TinyInt).Value = (byte)GiftStatusEnum.wait; – Алексей Медведев May 29 '12 at 14:08
  • The only point I could stick to is the fact, that sometimes when I cast to byte, SqlParamter constructor fails to determine SqlDbType correctly. So it is better to explicitly specify SqlDbType for parameters. – Алексей Медведев May 30 '12 at 13:38
  • I was unable to make the database accept an string when sent as AddWithValue. It always complained that the parameter wasn't supplied. I then used `cmd.Parameters.Add("@error_msg", System.Data.SqlDbType.NVarChar, 256); cmd.Parameters["@error_msg"].Value = error_msg;` which made the database accept the parameter (unless it is null in which case I have to supply DBNull.Value). – galmok Aug 28 '14 at 11:25
1

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());
Internet Engineer
  • 2,514
  • 8
  • 41
  • 54