CODEWITHSUNDEEP
c#sql-server-2012sqlcommand
IrishCrf
IrishCrf

Reputation: 115

C# SQLCommand - Parametrized query is cut short

Apologies for the formatting, it's a little rough

I'm having an issue with an INSERT statement through a SQLCommand in C#. Once the insert is completed the ID is returned to the program.

I have a number of variables populated and a SQLCommand coded up. All the parameters are accounted for.

using (SqlCommand sqlCatCmd = new SqlCommand())
{
   sqlCatCmd.CommandText = 
       "INSERT INTO [ChargeType](IsRecurring, IsApplied4NewMembers, Name, PurseID, AllowFamilyDiscount) " +
       "Values (@IsRec, @NewMem, @CName, @PurseID, @FamDisc);" +
       "SET @ChrgID = SCOPE_IDENTITY();";

  //Connections are opened further up the code no need to include
  sqlCatCmd.Connection = _importSettings.sqlServerConnection;
  sqlCatCmd.Transaction = _importSettings.sqlServerTransaction;

  //Parameters
  sqlCatCmd.Parameters.AddWithValue("@IsRec", _reapply);
  sqlCatCmd.Parameters.AddWithValue("@NewMem", _onNewMem);
  sqlCatCmd.Parameters.AddWithValue("@CName", _name);
  sqlCatCmd.Parameters.AddWithValue("@PurseID", _purse);
  sqlCatCmd.Parameters.AddWithValue("@FamDisc", _applyFamDisc);

  //Create parameter for newly added chargeType id value
  SqlParameter ChrgTypeIDReturn = new SqlParameter();
  ChrgTypeIDReturn.ParameterName = "@ChrgID";
  ChrgTypeIDReturn.Size = 4;
  ChrgTypeIDReturn.Direction = ParameterDirection.Output;
  sqlCatCmd.Parameters.Add(ChrgTypeIDReturn);

  //execute
  sqlCatCmd.ExecuteNonQuery();

 //update variable with returned id value
 _chrgTypeReturnedId = Convert.ToInt32(ChrgTypeIDReturn.Value);
 _importSettings.sqlServerTransaction.Commit();
 conn.CloseSqlConnection(_importSettings.sqlServerConnection);
}

When I execute the query the following is caught:

The parameterized query '(@IsRec nvarchar(1),@NewMem nvarchar(1),@CName nvarchar(25),@Pur' expects the parameter '@PurseID', which was not supplied.

It appear's to be cutting the query short, is there a limit I'm missing here? Looking at my string it appears to be syntactically correct

Thanks

Upvotes: 2

Views: 363

Answers (2)

rdyhalt
rdyhalt

Reputation: 96

You can rewrite the SQL statement to output insert id. Like: INSERT INTO table1 (name) OUTPUT Inserted.ID VALUES('FooBar')

Then use ExecuteScalar() and not ExecuteNonQuery(). A rewrite:

using (SqlCommand sqlCatCmd = new SqlCommand())
{
   sqlCatCmd.CommandText = 
       "INSERT INTO [ChargeType](IsRecurring, IsApplied4NewMembers, Name, PurseID, AllowFamilyDiscount) " +
       "OUTPUT Inserted.ID " +
       "Values (@IsRec, @NewMem, @CName, @PurseID, @FamDisc)";

  //Connections are opened further up the code no need to include
  sqlCatCmd.Connection = _importSettings.sqlServerConnection;
  sqlCatCmd.Transaction = _importSettings.sqlServerTransaction;

  //Parameters
  sqlCatCmd.Parameters.AddWithValue("@IsRec", _reapply);
  sqlCatCmd.Parameters.AddWithValue("@NewMem", _onNewMem);
  sqlCatCmd.Parameters.AddWithValue("@CName", _name);
  sqlCatCmd.Parameters.AddWithValue("@PurseID", _purse);
  sqlCatCmd.Parameters.AddWithValue("@FamDisc", _applyFamDisc);

  //ExecuteScalar to get OUTPUT value from Sql string
  int insertedId = sqlCatCmd.ExecuteScalar() as int;

 //update variable with returned id value
 _chrgTypeReturnedId = insertedId;
 _importSettings.sqlServerTransaction.Commit();
 conn.CloseSqlConnection(_importSettings.sqlServerConnection);
}

Upvotes: 1

marsze
marsze

Reputation: 17074

I don't think it cuts the query, because it mentions the full parameter name @PurseID later in the exception message.

Check if _purse == null by any chance. If so, you must use DBNull.Value instead.

Upvotes: 3

Related Questions