?
Solved

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

Posted on 2009-07-10
10
Medium Priority
?
562 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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 

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 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

777 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