locked
Sql IN clause fails on OleDb connection parameter substitution RRS feed

  • Question

  • User1395831461 posted

    I am using parameter substitution to build a query to be used as a SqlDataSource for a GridView...

    SqlDataSource1.SelectCommand = "select order, recalled, edte, acct, sub, name, add1, add2, add3, code from libname.tablename t1 where t1.code in (?) and t1.edte >= ? and t1.edte <= ? order by t1.order desc"

    ...and I substitute the parameters...

    SqlDataSource1.SelectParameters.Remove(SqlDataSource1.SelectParameters("@codelist"))
    SqlDataSource1.SelectParameters.Remove(SqlDataSource1.SelectParameters("@startingdate"))
    SqlDataSource1.SelectParameters.Remove(SqlDataSource1.SelectParameters("@endingdate"))
    SqlDataSource1.SelectParameters.Add("@codelist", CodeList)
    SqlDataSource1.SelectParameters.Add("@startingdate", startingdate)
    SqlDataSource1.SelectParameters.Add("@endingdate", endingdate)

    ...and CodeList, startingdate and endingdate are all defined as string.  The strings will have values such as...

    startingdate="06/03/2013"

    endingdate="06/20/2013"

    CodeList="CCM, CCS, CDR, CIR"

    ...and the query execution fails with...

    CWBZZ5012 Data overflow - the parameter data is too large to fit into the host field. This is a programming error. 

    ...but if I change the value of CodeList to have one value...

    CodeList="CCM"

    ...the query executes without any problem and returns the expected results.

    The database is DB2 hosted on an iSeries server and the field "code" in the table is defined as char(3) .

    Looks like query does not like the list of values but is fine with it as long as it has only one value that is the same length as the table field value.

    Can someone kindly tell me what I need to do to make the IN work with the list of values?  Any help greatly appreciated, Roscoe

    I've included the stack trace below...

     

    CWBZZ5012 Data overflow - the parameter data is too large to fit into the host field. This is a programming error.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: CWBZZ5012 Data overflow - the parameter data is too large to fit into the host field. This is a programming error.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    Stack Trace:

    [OleDbException (0x80040e21): CWBZZ5012 Data overflow - the parameter data is too large to fit into the host field. This is a programming error.]
    
    [InvalidOperationException: Conversion failed for command parameter[0] '' because the data value overflowed the type used by the provider.
    ]
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1081356
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult) +135
       System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +163
       System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
       System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +167
       System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +116
       System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +319
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +3404
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +96
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +225
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +127
       System.Web.UI.WebControls.GridView.DataBind() +29
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +108
       System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +43
       System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +87
       System.Web.UI.Control.PreRenderRecursiveInternal() +209
       System.Web.UI.Control.PreRenderRecursiveInternal() +307
       System.Web.UI.Control.PreRenderRecursiveInternal() +307
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4834
    
    Thursday, June 20, 2013 12:23 PM

Answers

All replies