Solved

VB Visual Studio How to filter a gridview as >= Current Date

Posted on 2009-07-10
10
555 Views
Last Modified: 2013-11-26
VS 2005
VB.NET
SQL 2005

Hello all.  I am very new to ASP.NET, as I am migrating all of my Classic ASP sites to .NET now.

I have a basic gridview that searches all of its records by "state" from a dropdown control.  That works perfectly.  I would like to take it one step further by altering my WHERE clause to be by "state" AND ">=CurrentDate".

When I click to "configure my datasource" to alter the query, I am not sure of the syntax for the current date.

The WHERE currently is "=" to a "Control" named "DropDownList1".  I supposed I just add another WHERE to that, but I'm not sure how.  I choosed the db field against which I'd like to query.  Then I select ">=" from the next list.  Then I select "none" from the 3rd list.  That prompts me for a parameter, which I assume is where the "CurrentDate" would be, but I'm not sure of the syntax.

Your help is always appreciated.

Thanks so much,
Shane
0
Comment
Question by:lshane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 41

Expert Comment

by:guru_sami
ID: 24826453
Are you using SqlDateSource?
Then try like this:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("@YourDateField").Value = DateTime.Now
End Sub

And your SqlDataSource should look like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:YourConnectionString %>"
            SelectCommand="SELECT [Field1], [Field2] FROM [YourTable] WHERE ([YourDateField] <= @YourDateField)"
                   
            onselecting="SqlDataSource1_Selecting">
     <SelectParameters>
         <asp:Parameter Name="YourDateField" Type="DateTime" />
     </SelectParameters>
      </asp:SqlDataSource>

Note you will still need that DropDownList and state parameter....but the code should give you idea on what adjustments needs to be made.
0
 

Author Comment

by:lshane
ID: 24828552
Hi, guru_sami.  Thanks for your reply.

Being new to ASP.NET, I'm not as fluent as to where I can place and manipulate code, as I am with Classic ASP.

1)  I am not using a code-behind page.  Where would I place the "sub"-block you provided?

2)  So, this "sub"-block you gave does not just making my db field a variable for DateTime.Now, right?  I want to only show records where my db field is >= the current date.  Will your sub-block do that?


Thanks so much,
Shane
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24828824
can you share the code you have ...so I can tell you where to make adjustments...
the code-block I gave should go inside
<script language="c#">
</script>
if you have any on your .aspx page.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lshane
ID: 24828977
Well, I attached the original block of code snippet generated by Visual Studio 2005 below.  

So, you'll see the SELECT statement and how it's filtering for records matching the state from the DropDownList1; but I am wanting to append an AND statement that would further filter the records to display when the [sale_enddate] is >= current system date.  

So... I would think I could modify it like the following somehow, but I'm not getting the correct syntax:

SelectCommand="SELECT [sale_title], [sale_state], [sale_startdate], [sale_city], [sale_county] FROM [sale_listings] WHERE ([sale_state] = @sale_state) AND ([sale_enddate] >= DateTime.Now)">

I know that does not work, but you get the idea of what I'm trying to do.  In Classic ASP, it would be...
... AND ([sale_enddate] >= Date())

I tried that, too, but it also did not work.

I'm sure this is a routine function, but being new to the .NET world, I just don't know the syntax.

Thanks so much,
Shane
                            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:salesConnectionString %>"
                                SelectCommand="SELECT [sale_title], [sale_state], [sale_startdate], [sale_city], [sale_county] FROM [sale_listings] WHERE ([sale_state] = @sale_state)">
                                <SelectParameters>
                                    <asp:ControlParameter ControlID="DropDownList1" Name="sale_state" PropertyName="SelectedValue"
                                        Type="String" />
                                </SelectParameters>
                            </asp:SqlDataSource>

Open in new window

0
 
LVL 41

Accepted Solution

by:
guru_sami earned 500 total points
ID: 24829015
I have set the sqlDAta source accordingly:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:salesConnectionString %>"
                                SelectCommand="SELECT [sale_title], [sale_state], [sale_startdate], [sale_city], [sale_county] FROM [sale_listings] WHERE (([sale_state] = @sale_state) AND ([sale_enddate] >= @sale_enddate))"
onselecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="sale_state" PropertyName="SelectedValue"
                                        Type="String" />
  <asp:Parameter Name="sale_enddate" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>

What is new in there:
1: ([sale_enddate] >= @sale_enddate)
2: a parameter sale_enddate added to SelectParameters
3: event handler for OnSelecting event...onselecting="SqlDataSource1_Selecting"
4: The code below should go inside your code behind file i.e aspx.vb

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("@YourDateField").Value = DateTime.Now
End Sub

In case if you have confusion where the above code should go
Check in your VS Solution Explorer if you have your code-behind file or not..
You just posted the SqlDatasource code and not the entire .aspx page so I am unable to explain wher this code will go.
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24829020
Sorry that should be:
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("@sale_enddate").Value = DateTime.Now
End Sub
0
 

Author Comment

by:lshane
ID: 24829053
Hi, guru_sami.  That was great.  Thank you.

I placed the "sub" block of code just above the "datasource" block (Which is in between the Gridview and Datasource blocks of code) and it seems to work great.  I will attach the snippet of how I arranged it.  I would imagine it's fine in that location.  Hopefully no ordering probs regarding execution of code.


One other little piece of trivia, though:
That seems like a lot of code just to obtain one piece of information from a SELECT statement.  In Classic ASP, I would have only had to append the "...&Date()&..." to achieve the same thing.

I have been hearing all of my peers say how wonderful .NET is and how "easy" it is, but to me... it seems like everything requires so much more coding.  

Is this true?  
Am I just doing things the wrong way?

I noticed you actually specified earlier when you were asking if I was using "SqlDatasource".  Is there an advantage to using that, as opposed to "ObjectDataSource"???  What would that be?

I tried "ObjectDataSource" first, and all ran fine in my VS test browser; but... when I uploaded all the files to my hosting provider's server... I continued to get "Failed to enable contraints..." errors.  I reconstructed the page to use "SqlDataSource" instead... and all seems to be working great.  

Any insights on that?

<script runat=server>
    Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
        e.Command.Parameters("@sale_enddate").Value = DateTime.Now
    End Sub
 
</script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:salesConnectionString %>"
                                SelectCommand="SELECT [sale_title], [sale_state], [sale_startdate], [sale_city], [sale_county] FROM [sale_listings] WHERE (([sale_state] = @sale_state) AND ([sale_enddate] >= @sale_enddate))" 
onselecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="sale_state" PropertyName="SelectedValue"
                                        Type="String" />
  <asp:Parameter Name="sale_enddate" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>

Open in new window

0
 

Author Closing Comment

by:lshane
ID: 31602214
Fantastic.
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24830431
---In Classic ASP, I would have only had to append the "...&Date()&..." to achieve the same thing.
In classic asp you have your markup as well as business logic togethere.....while in ASP.NET you are seperating the business logic from UI.
What you did is all correct. Although if I would prefer a seperate code-behind file instead of putting my code in <script> block.

When using ObjectDataSource you do the DB interaction i.e. your class methods will retrieve/update data. While using SqlDataSource that all work is done for you. ObjectDataSource gives you more control on how to handle the Data.
Sorry, but I am not sure why you got that error on your host...
0
 

Author Comment

by:lshane
ID: 24830885
Thanks so much, guru_sami.  That's good information.  If I had a larger page, or something, I would probably opt for the code-behind.  Right now, I'm just trying to compare .NET with Classic on one page so I can see the differences on how they are structured.

You will probably be seeing a lot of questions from me in the next few weeks, as I'm trying to migrate all my Classic sites to .NET to keep them (and myself) current, and to utilize a better connection with SQL.  So, a lot of the things I know how to do in Classic, I will be seeking help with accomplishing in .NET.

Thanks so much,
Shane

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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