none
SQLCommand RRS feed

  • Question

  •  

    I have to execute a stored procedure using SQLCommand object. The prcedure has numerous of arguments. When I tried to execute the procedure by passing some arguement values as null. The it shows an error message as given below. Pls give me a solution.

     

    {System.Data.SqlClient.SqlException: Procedure 'sp__NewCostCentre' expects parameter '@Descriptions', which was not supplied.

     

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at DBTask.ExecuteNonQuery_SP(String ProcedureName, Object[,] ParamArrayName) in e:\Projects\Web\Accounts\App_Code\Components\DAL\DBTask.cs:line 82}

    Thursday, November 29, 2007 12:25 PM

Answers

  •  

    You need to set the value in the command parameter. You should have code that looks similar to this:

     

    // ADD_ENTITY_COMMAND:
    //      INSERT INTO [dbo].[Accounts] ([CustomerId], [Amount], [CreationDate]) VALUES (@CustomerId, @Amount, @CreationDate);
    //      SELECT AccountId, CustomerId, Amount, CreationDate, TimeStamp FROM Accounts WHERE (AccountId = SCOPE_IDENTITY())

     

    public void Insert(T entity)
            {
                if (entity == null) {
                    throw new ArgumentNullException("entity");
                }
                try {
                    m_connection.Open();
                    DbCommand cmd = m_connection.CreateCommand();
                    cmd.CommandText = ADD_ENTITY_COMMAND;
                    AddInsertParameters(entity, cmd);

                    DbDataReader reader = cmd.ExecuteReader();

                    using (reader) {
                        if (reader.RecordsAffected == 0) {
                            throw new PersistanceException();
                        }
                        PopulateSingle(entity, reader);
                    }
                }
                catch (PersistanceException) {
                    throw;
                }
                catch (Exception ex) {
                    throw new PersistanceException(ex);
                }
                finally {
                    m_connection.Close();
                }
            }

     

            protected override void AddInsertParameters(Account entity, DbCommand command)
            {
                AddParameter(command, "@CustomerId", entity.CustomerId);
                AddParameter(command, "@Amount", entity.Amount);
                AddParameter(command, "@CreationDate", entity.CreationDate);
            }

     

    Hopes this helps,

    Charles

    Saturday, December 1, 2007 12:03 AM

All replies

  • You need to supply a value for the parameter @Descriptions that isn't null.  If the SP is coded to handle empty values, then you might be able to pass in String.Empty or something like that, but in most cases they are not and you have to supply a value for the parameter that the SP understands.  Also check that @Descriptions is not an output parameter which have to be handled slightly differently in the command object.

    You may consider using the Data Access Blocks in the Enterprise Library provided by Microsoft.  This will make your data layer tasks much less painful as it encapsulates a lot of common tasks into easy to use functions that have been tested for years now.  For example, getting a dataset from executing a stored procedure can be done in one line of code:

    Code Block

    dsTemp = DB.ExecuteDataSet(m_str_SP_GETCALCULATIONDATA, CalcDescriptor.BenchMarkProductID, _
                            CalcDescriptor.BeginYear, CalcDescriptor.BeginMonth, _
                            CalcDescriptor.EndYear, CalcDescriptor.EndMonth, strPerformanceType, -1, 0)

    'dsTemp is a DataSet object, m_str_SP_GETCALCULATIONDATA is a constant that contains the string value for the SP name, and the rest of the values are user object properties intended as parameters for the stored procedure.


    There are plenty of hands on labs and samples out there on the web that will help you get started with the data access blocks in the enterprise library.  The data access blocks require very little setup and will make your life much easier in the long run. 

    Hope this helps.

    Thursday, November 29, 2007 2:13 PM
  • Hi,

    Your problem is that you must set the null values to DBNull.Value. Here's a helper function you can use to add a command parameter to cleanly hide this:

    Code Block

            protected void AddParameter<TValue>(DbCommand command, string name, TValue value)
            {
                DbParameter param = command.CreateParameter();
                param.ParameterName = name;
                if (value != null) {
                    param.Value = value;
                } else {
                    param.Value = DBNull.Value;
                }
                command.Parameters.Add(param);
            }


    Hope this helps,
    Charles

    Friday, November 30, 2007 2:11 AM
  • Hi    cverdon

     

    I tried like this

     

    int? BranchID;

    if (value == null)

    BranchID = DBNull.value;

    else

    BranchID = value;

     

     

     Here is another error Cannot convert DBNull.value to int?

     

    What I can do?

    Friday, November 30, 2007 6:39 AM
  •  

    You need to set the value in the command parameter. You should have code that looks similar to this:

     

    // ADD_ENTITY_COMMAND:
    //      INSERT INTO [dbo].[Accounts] ([CustomerId], [Amount], [CreationDate]) VALUES (@CustomerId, @Amount, @CreationDate);
    //      SELECT AccountId, CustomerId, Amount, CreationDate, TimeStamp FROM Accounts WHERE (AccountId = SCOPE_IDENTITY())

     

    public void Insert(T entity)
            {
                if (entity == null) {
                    throw new ArgumentNullException("entity");
                }
                try {
                    m_connection.Open();
                    DbCommand cmd = m_connection.CreateCommand();
                    cmd.CommandText = ADD_ENTITY_COMMAND;
                    AddInsertParameters(entity, cmd);

                    DbDataReader reader = cmd.ExecuteReader();

                    using (reader) {
                        if (reader.RecordsAffected == 0) {
                            throw new PersistanceException();
                        }
                        PopulateSingle(entity, reader);
                    }
                }
                catch (PersistanceException) {
                    throw;
                }
                catch (Exception ex) {
                    throw new PersistanceException(ex);
                }
                finally {
                    m_connection.Close();
                }
            }

     

            protected override void AddInsertParameters(Account entity, DbCommand command)
            {
                AddParameter(command, "@CustomerId", entity.CustomerId);
                AddParameter(command, "@Amount", entity.Amount);
                AddParameter(command, "@CreationDate", entity.CreationDate);
            }

     

    Hopes this helps,

    Charles

    Saturday, December 1, 2007 12:03 AM