Grid View Date issue - easy fix I am sure

I have a dropdown list (ddlLastUpdate) that I am using to filter using date.  Everything works great except when I want to see all of the values.  Normally I select the blank spot in the dropdown list and get all records for the other filters but in this case I get a "Cannot perform '=' operation on System.DateTime and System.String." error

<asp:sqldatasource runat="server" id="MySource"
   connectionstring="<%$ ConnectionStrings:CompEvents_DSNConnection %>" ProviderName="<%$ ConnectionStrings:CompEvents_DSNConnection.ProviderName %>"
   SelectCommand="SELECT * FROM [Events]"
   DeleteCommand="DELETE FROM [Events] WHERE [EventID] = ?"
   UpdateCommand="UPDATE Events SET DivStatus = ?, LastUpdate = ?, LastUpdateBy = ? WHERE EventID = ? " 
  filterexpression="DMA Like '{0}' AND DivStatus Like '{1}' AND Division Like '{2}' AND Retailer Like '{3}' AND Action Like '{4}' and LastUpdate = '{5}'  AND ARStatus Like '{6}'"
   >
 
    <UpdateParameters>
          <asp:Parameter Name="DivStatus" Type="String" />
          <asp:SessionParameter Name="gsLastUpdDateTime" SessionField="SysDate" Type=dateTime/>
          <asp:SessionParameter Name="gsUserName" SessionField="UserName" type=string/>
          <asp:Parameter Name="EventID" Type="Int32" />
    </UpdateParameters>

    <filterparameters>
        <asp:ControlParameter ControlID="ddlDMA" DefaultValue="*" Name="dma" PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlDivStatus" DefaultValue="*" Name="DivStatus" PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlDivision" DefaultValue="*" Name="Division" PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlRetailer" DefaultValue="*" Name="Retailer" PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlAction" DefaultValue="*" Name="Action" PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlLastUpdate" DefaultValue="*" Name="LastUpdate" PropertyName="SelectedValue"/>
        <asp:ControlParameter ControlID="ddlARStatus" DefaultValue="*" Name="ARStatus" PropertyName="SelectedValue" />
   </filterparameters>
flfmmqpAsked:
Who is Participating?
 
RejojohnyCommented:
try
filterexpression="DMA = iif('{0}' = 'All', DMA,'{0}')  AND ....
0
 
divinewind80Commented:
I would suggest using an If...Then statement.  Kind of, if the selected dropdownlist text is "  " then use the following SQL, otherwise, proceed as usual using variables.

Does that make sense?
0
 
flfmmqpAuthor Commented:
I know how to use if then statements and if it were that easy it would be done by now.  I don't know where I would do an if then statement.

I think if I change my filterexpression expression or the ControlParameter I should be able to do this.  I just don't know what that change is.  The Date field is the only one this does not work for.  
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
flfmmqpAuthor Commented:
I think I have it.  It seems to work if I take out the "*" from the default value

        <asp:ControlParameter ControlID="ddlLastUpdate" DefaultValue="" Name="LastUpdate" PropertyName="SelectedValue"/>
0
 
flfmmqpAuthor Commented:
I take that back that seems to be messing up all of my other filters.  Augh.
0
 
divinewind80Commented:
I would suggest using the If...Then in the OnSelectedIndexChanged event of the dropdownlist.  If you are going to take this approach, you will likely have to remove your Parameter from your SQLDatasource, or it will cause an error.  Rather, set the variable through the code-behind like "SELECT * FROM TABLE WHERE ID = " & ddl1.selecteditem & ""
0
 
RejojohnyCommented:
if you have a parameter which may or may not be filled .. its not advisable to use the "like" operator .. like operator tends to be slow and will not use the indexes of your table .. what you need to use is the isnull statement (assuming SQl server and i think access also has it . .no idea about oracle) ..

try
filterexpression="DMA = isnull('{0}', DMA) AND DivStatus = isnull('{1}', DivStatus) ...
if you cannot have null for the parameters then use "case" statements instead of isnull, but the idea is the same i.e if the parameter is not available (no filter for that field), use the value that the field currently has in the row of the table ..

this approach works in stored procedure and queries .. i have never tried it with SQLdatasource and filterexpression.. but I do not see a reason why it would not work ..

Rejo
0
 
flfmmqpAuthor Commented:
I want to keep the sql statment in the sqldatasource because that allows me to do many other things manaually and I believe a way has to exist for me to accomplish this with out a lot of coding changes

Rejo:
FYI - I am using Access.  The isnull is not working and i do not know how I would use a case stament inside the SQLDataSource.  Can you show me how?

0
 
RejojohnyCommented:
you can also create dynamic SQL or update the filterexpression dynamically so that only the conditions that are valid are entered  .. in the meantime, let me see if I can write some code for access ..

Rejo
0
 
RejojohnyCommented:
isnul is supported in access its only not the same as SQL server ..
try
Iif(IsNull('{0}'),DMA,'{0}') .. see if that works?
0
 
flfmmqpAuthor Commented:

I thought it might help if I show the data source of the DMA dropdowlist.  I just changed from having a default value of '' to have an 'All' option.  What I am hoping to accomplish is a way for the user to see all of the DMA's.  Only way to do this is to give them some sort of option.  I thought All might be better then a blank field and easier to programe the filterexpression.  However the '*' is not working.  Suggestions.

        <asp:SqlDataSource ID="CompEvents_DMA" runat="server" ConnectionString="<%$ ConnectionStrings:DSN_CompEvents %>"
            ProviderName="<%$ ConnectionStrings:DSN_CompEvents.ProviderName %>" SelectCommand="SELECT Events.DMA FROM Events GROUP by events.DMA HAVING (((Events.DMA) Is Not Null)) UNION select 'All' as Division  FROM EVENTS GROUP BY 'All' HAVING ((('All') Is Not Null)) ORDER BY events.DMA;">
        </asp:SqlDataSource>



   filterexpression="DMA = iif('{0}' = 'All','*','{0}') AND DivStatus Like '{1}' AND Division Like '{2}' AND Retailer Like '{3}' AND Action Like '{4}' AND ARStatus Like '{6}'">
0
 
flfmmqpAuthor Commented:
the filter expression did not like the isnull option at all.  Honestly, I'm not sure if the value selected is really null anyway since I take out null values in my query.
0
 
flfmmqpAuthor Commented:
This is working so I know the iif statment is working.  I just need to know how to pull all the records.
   filterexpression="DMA = iif('{0}' = 'All','San Antonio, TX','{0}') AND DivStatus Like '{1}' AND Division Like '{2}' AND Retailer Like '{3}' AND Action Like '{4}' AND ARStatus Like '{6}'">
0
 
flfmmqpAuthor Commented:
Perfect.  Thanks for the help.
0
 
RDWaibelCommented:
This is good stuff.  It solved my issue but not exactly the way I wanted it to.  I was using a stored procedure to populate the SqlDatasource.  Once I replaced it with the actual query, it worked fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.