?
Solved

Grid View Date issue - easy fix I am sure

Posted on 2007-10-17
15
Medium Priority
?
1,126 Views
Last Modified: 2008-01-09
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>
0
Comment
Question by:flfmmqp
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 9

Expert Comment

by:divinewind80
ID: 20093848
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
 

Author Comment

by:flfmmqp
ID: 20094287
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
 

Author Comment

by:flfmmqp
ID: 20094316
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:flfmmqp
ID: 20094328
I take that back that seems to be messing up all of my other filters.  Augh.
0
 
LVL 9

Expert Comment

by:divinewind80
ID: 20094395
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
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20095015
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
 

Author Comment

by:flfmmqp
ID: 20095372
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
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20095969
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
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20096017
isnul is supported in access its only not the same as SQL server ..
try
Iif(IsNull('{0}'),DMA,'{0}') .. see if that works?
0
 

Author Comment

by:flfmmqp
ID: 20100394

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
 

Author Comment

by:flfmmqp
ID: 20100409
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
 

Author Comment

by:flfmmqp
ID: 20100446
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
 
LVL 26

Accepted Solution

by:
Rejojohny earned 2000 total points
ID: 20100569
try
filterexpression="DMA = iif('{0}' = 'All', DMA,'{0}')  AND ....
0
 

Author Comment

by:flfmmqp
ID: 20101102
Perfect.  Thanks for the help.
0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 21266926
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question