Solved

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

Posted on 2009-07-10
10
538 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now