Hello, I am trying to use a select command in a SqlDataSource to load a GridView. I am trying to use parameter markers in a case in the where clause of the select but get the following error when the sql executes...
Message: Sys.WebForms.PageRequestManagerServerErrorException: SQL0417: Combination of parameter markers not valid.
Cause . . . . . : The statement string specified as the object of a PREPARE statement contains a predicate or expression where parameter markers have been used as operands of the same operator. The following restrictions apply to the use of parameter markers:
-- Both the operands in a predicate cannot be parameter markers. For example, specifying predicates of the form: ? = ? or ? = ( SELECT ? FROM x ) are not valid. -- Both the operands in a expression cannot be parameter markers. For example, specifying
an expression of the form: ? + ? is not valid. -- At least one of the operands in the BETWEEN predicate cannot be a parameter marker. For example, specifying the predicate of the form: ? BETWEEN ? and ? is not valid. -- At least, one of the operands
of the IN predicate must not be a parameter marker. For example, specifying the predicate of the form: ? IN (?, ?, ?) is not valid. Recovery . . . : Correct the statement so that all operands of the predicate or expression are not parameter markers.
A CAST specification can be used in most cases to assign attributes to a parameter marker. Try the request again.
Line: 869
Char: 13
Code: 0
...and i've tried assigning a type to the parameters without success...and I'm not sure how the errror is advising me to use cast?
The code for my GridView and SqlDataSource is as below...
SelectCommand="Select t1.sosono, t1.sosadn, t1.sosadd, case when t1.sortdt='01/01/0001' then NULL else t1.sortdt end as sortdt, case when t1.sordte='01/01/0001' then NULL else t1.sordte end as sordte from bllib400.blpso t1 where soddte='01/01/0001'
and socode=? and sordte=? and sortdt= case when ? is null then '01/01/0001' else ? end">
<SelectParameters>
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SOCODE)" />
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SORDTE)" />
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SORTDT)"/>
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SORTDT)"/>
</SelectParameters>
</asp:SqlDataSource>
</div>
...My intent in the where clause being that when the SORTDT parameter comes from the "parent" control as NULL it is replace in the select with '01/01/0001'.
Could someone kindly show me how to use the parameter markers correctly in the case?
In the standard SqlDataSource，I don't think it's suitable for you to use "?"（question mark）as a parameter token……I mean you should use @parameterName instead。And your @parameterName should be the same defined in the SelectParameters。
rpfinnimore
Member
196 Points
161 Posts
using parameter markers in case in where clause of SelectCommand in SqlDataSource
Apr 03, 2012 06:36 PM|LINK
Hello, I am trying to use a select command in a SqlDataSource to load a GridView. I am trying to use parameter markers in a case in the where clause of the select but get the following error when the sql executes...
Message: Sys.WebForms.PageRequestManagerServerErrorException: SQL0417: Combination of parameter markers not valid.
Cause . . . . . : The statement string specified as the object of a PREPARE statement contains a predicate or expression where parameter markers have been used as operands of the same operator. The following restrictions apply to the use of parameter markers: -- Both the operands in a predicate cannot be parameter markers. For example, specifying predicates of the form: ? = ? or ? = ( SELECT ? FROM x ) are not valid. -- Both the operands in a expression cannot be parameter markers. For example, specifying an expression of the form: ? + ? is not valid. -- At least one of the operands in the BETWEEN predicate cannot be a parameter marker. For example, specifying the predicate of the form: ? BETWEEN ? and ? is not valid. -- At least, one of the operands of the IN predicate must not be a parameter marker. For example, specifying the predicate of the form: ? IN (?, ?, ?) is not valid. Recovery . . . : Correct the statement so that all operands of the predicate or expression are not parameter markers. A CAST specification can be used in most cases to assign attributes to a parameter marker. Try the request again.
Line: 869
Char: 13
Code: 0
...and i've tried assigning a type to the parameters without success...and I'm not sure how the errror is advising me to use cast?
The code for my GridView and SqlDataSource is as below...
<div id="Div1" style="position: absolute; left:900px; top:50px; height:350px; width:650px;
font-family: Arial; font-size: 15px; color:black; overflow:auto">
<asp:GridView ID="listserviceorders" runat="server"
AllowSorting="True" AutoGenerateColumns="False"
DataSourceID="SqlDataSource3" AllowPaging="False"
HeaderStyle-Wrap="True"
GridLines="None" Visible="true"
Caption='<table class="Freezing" ><tr><td>Service Orders List</td></tr></table>'
CaptionAlign="Left" SelectedRowStyle-BackColor="#CCFFCC"
OnSelectedIndexChanged="showserviceorders" >
<AlternatingRowStyle BackColor="#CCCCFF" Wrap="False" />
<Columns>
<asp:CommandField ShowSelectButton="True" ButtonType="Image" SelectImageUrl="linkimage.gif" />
<asp:BoundField DataField="sosono" HeaderText="SO Number"
SortExpression="sosono" />
<asp:BoundField DataField="sosadn" HeaderText="Street Number"
SortExpression="sosadn" />
<asp:BoundField DataField="sosadd" HeaderText="Address"
SortExpression="sosadd" />
<asp:BoundField DataField="sordte" HeaderText="Request Date"
SortExpression="sordte" />
<asp:BoundField DataField="sortdt" HeaderText="Returned Date"
SortExpression="sortdt" />
</Columns>
<HeaderStyle CssClass="Freezing" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:startpagegridview1ConnectionString %>"
ProviderName="<%$ ConnectionStrings:startpagegridview1ConnectionString.ProviderName %>"
SelectCommand="Select t1.sosono, t1.sosadn, t1.sosadd, case when t1.sortdt='01/01/0001' then NULL else t1.sortdt end as sortdt, case when t1.sordte='01/01/0001' then NULL else t1.sordte end as sordte from bllib400.blpso t1 where soddte='01/01/0001' and socode=? and sordte=? and sortdt= case when ? is null then '01/01/0001' else ? end">
<SelectParameters>
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SOCODE)" />
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SORDTE)" />
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SORTDT)"/>
<asp:ControlParameter ControlID="sobydate2GridView1" Name="?"
PropertyName="SelectedDataKey.Values(SORTDT)"/>
</SelectParameters>
</asp:SqlDataSource>
</div>
...My intent in the where clause being that when the SORTDT parameter comes from the "parent" control as NULL it is replace in the select with '01/01/0001'.
Could someone kindly show me how to use the parameter markers correctly in the case?
Any help greatly appreciated,
Roscoe
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: using parameter markers in case in where clause of SelectCommand in SqlDataSource
Apr 05, 2012 02:10 AM|LINK
Hello：）
In the standard SqlDataSource，I don't think it's suitable for you to use "?"（question mark）as a parameter token……I mean you should use @parameterName instead。And your @parameterName should be the same defined in the SelectParameters。
Reguards！