Kevin,

Thank you so much. I just have one more question.

If I determine the size of the array while I go through the loop, how do I redimension the array? I know I could build an ArrayList of non-blank parameters that get passed and then create the IDbParameter array using ArrayList.Count to determine the number of rows, but can I do it all in one loop?

Thanks.

Linda

[UPDATE]
I tried your suggestion, using the arraylist approach I mentioned above. When it creates the command string, it seems to be passing back the wrong parameter prefix. The string I get back is:

SELECT OnRent.*, InvClass.Descriptn, Reqn.ApproveBy, Reqn.ReqBy, Reqn.MasterNo, Reqn.Block, Reqn.Phone, Reqn.DueDate, Reqn.Status, Reqn.Location, Reqn.DeliverTo, 00000000.00 as Billings, Reqn.ApprPrty, space(50) as BlockDesc, Reqn.Notes FROM OnRent, Reqn, InvClass WHERE OnRent.ReqNo = Reqn.ReqNo AND OnRent.InvClass = InvClass.InvClass AND NOT EMPTY(OnRent) and empty(OffRent) AND OnRent.Source = ??Param0?Source


Here's the stack trace:

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at OakLeaf.MM.Main.Data.mmDataAccessOleDb.FillDataSet(DataSet ds, IDbDataAdapter dbAdapter, String tableName, Boolean clearOnFill)
at OakLeaf.MM.Main.Data.mmDataAccessOleDb.FillDataSet(DataSet ds, IDbCommand command, String tableName, Boolean clearOnFill)
at OakLeaf.MM.Main.Data.mmDataAccessOleDb.FillDataSet(DataSet ds, String command, String tableName, CommandType cmdType, IDbDataParameter[] dataParms, Boolean clearOnFill)
at OakLeaf.MM.Main.Business.mmBusinessObject.FillDataSet(DataSet ds, String command, String tableName, String databaseKey, CommandType cmdType, IDbDataParameter[] dataParams)
at OakLeaf.MM.Main.Business.mmBusinessObject.GetDataSet(String command, String tableName, String databaseKey, CommandType cmdType, IDbDataParameter[] dataParams)
at OakLeaf.MM.Main.Business.mmBusinessObject.GetDataSet(String command, String tableName, String databaseKey, IDbDataParameter[] dataParams)
at OakLeaf.MM.Main.Business.mmBusinessObject.GetDataSet(String command, String tableName, IDbDataParameter[] dataParams)
at OakLeaf.MM.Main.Business.mmBusinessObject.GetDataSet(String command, IDbDataParameter[] dataParams)
at RSC.TotalControl2006.Business.Onrent.GetOpenItems(String reqBy, String approveBy, String invClass, String source) in C:\deve\TotalControl2006\Total Control 2006 Business CSharp\Onrent.cs:line 142
at OpenItems.btnView_Click(Object sender, EventArgs e) in c:\deve\TotalControl2006\Total Control 2006 Web CSharp\OpenItems.aspx.cs:line 85
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


Is there something I missed?

Here's the code I used:

            StringBuilder BldCondition = new StringBuilder();
            string ParamString;
            ArrayList AReportCriteria = new ArrayList();

            // Create an array list of all non-blank parameters and 
            //   build the string of additional selection criteria
            if (reqBy != "0")
            {
                BldCondition.Append(" AND Reqn.ReqBy = @ReqBy");
                AReportCriteria.Add(reqBy);
            }
            if (approveBy != "0")
            {
                BldCondition.Append(" AND  Reqn.ApproveBy = @ApproveBy");
                AReportCriteria.Add(approveBy);
            }
            if (invClass != "0")
            {
                BldCondition.Append(" AND OnRent.InvClass = @InvClass");
                AReportCriteria.Add(invClass);
            }
            if (source != "0")
            {
                BldCondition.Append(" AND OnRent.Source = @Source");
                AReportCriteria.Add(source);
            }

            // Determine the size of the IDbDataParameter array
            int ParamCount = AReportCriteria.Count;
            string sCondition = BldCondition.ToString();

            // Create the IDbDataParameter array and load the values from the AReportCriteria arraylist
            IDbDataParameter[] Params = new IDbDataParameter[ParamCount];
            mmDataAccessBase DAO = this.GetDataAccessObject();

            for (int ICounter = 0; ICounter < ParamCount; ICounter++)
            {
                ParamString = DAO.ParameterPrefixChar + "Param" + ICounter.ToString();
                // This is the key to creating parameters to be passed...replace the 0 with the real value
                Params[ICounter] = this.CreateParameter(ParamString, AReportCriteria[ICounter]);
            }

            string CommandString = "SELECT OnRent.*, InvClass.Descriptn, " +
                "Reqn.ApproveBy, Reqn.ReqBy, Reqn.MasterNo, Reqn.Block, " +
                "Reqn.Phone, Reqn.DueDate, Reqn.Status, Reqn.Location, Reqn.DeliverTo, " +
                "00000000.00 as Billings, Reqn.ApprPrty, space(50) as BlockDesc, Reqn.Notes " +
                "FROM OnRent, Reqn, InvClass WHERE OnRent.ReqNo = Reqn.ReqNo " +
                "AND OnRent.InvClass = InvClass.InvClass AND NOT EMPTY(OnRent) and empty(OffRent)" +
                sCondition;
            //// Note that an IDbDataParameters array is passed rather than passing individual parameters...another key to solving this problem
            DataSet ds = this.GetDataSet(CommandString, Params);

Gratefully,

Linda