[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-07-10
10
Medium Priority
?
570 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

650 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