How to dynamically fetch closest selected value on dropdown formfields.

Hello, I have a form with two dropdown form fields. The user wants to have the selectedvalue prepolated: DDLFrom needs to be preselected to yesterday's value, the DDLTo needs to be preselected to today's date. Here is the catch: the dropdown fields are dynamically loaded from a table and only displays the dates where an issue was logged.
See attached screenshot. So my question is for the DDLFrom I need the closest date to yesterday and for DDLTo needs to be the most recent date.

This code throws an error since there was no today's date nor was there a date for yesterday since it was the weekend and so no activities.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Me.ddlFrom.SelectedValue = DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")
            Me.ddlTo.SelectedValue = DateTime.Now.ToString("MM/dd/yyyy")
        End If
    End Sub

Open in new window

SS.jpg
JessyRobinson1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
dim dt as String

dt = Datetime.Now.AddDays(-1).Tostring("mm/dd/yyyy")

while not ddlFrom.Items.Contains(dt)
   dt = DateTime.Parse(dt).AddDays(-1).Tostring("mm/dd/yyyy")
end while



If the combobox is ordered:

ddlTo.Selectedindex = ddlTo.Items.Count -1

if it's not ordered you can do a loop.
0
JessyRobinson1234Author Commented:
Thank you, I get the error on While Not ddlFrom.Items.Contains(dt):

Value of type 'String' cannot be converted to 'System.Web.UI.WebControls.ListItem'.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
try:
Contains (New ListItem(dt))
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

JessyRobinson1234Author Commented:
Error on: dt = DateTime.Parse(dt).AddDays(-1).ToString("mm/dd/yyyy")

String was not recognized as a valid DateTime.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
mm should be MM
0
JessyRobinson1234Author Commented:
New Error on dt = DateTime.Parse(dt).AddDays(-1).ToString("MM/dd/yyyy")

The added or subtracted value results in an un-representable DateTime.
Parameter name: value
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Needs to be done in both places:

dt = Datetime.Now.AddDays(-1).Tostring("MM/dd/yyyy")

while not ddlFrom.Items.Contains(dt)
   dt = DateTime.Parse(dt).AddDays(-1).Tostring("MM/dd/yyyy")
end while
0
JessyRobinson1234Author Commented:
I actually did and still get the error.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Rework:

dim days as integer = 2
dt = Datetime.Now.AddDays(-1).Tostring("MM/dd/yyyy")



while not ddlFrom.Items.Contains(dt)
   dt = DateTime.Now.AddDays(-1 * days).Tostring("MM/dd/yyyy")
  days += 1  'keep going backward in time.
end while
0
JessyRobinson1234Author Commented:
Hi I am following the logic. However, it goes in an infinite loop and throws this error:

The added or subtracted value results in an un-representable DateTime.
Parameter name: value
0
Kyle AbrahamsSenior .Net DeveloperCommented:
It means it's not finding the date.

How is your combobox populated?
0
JessyRobinson1234Author Commented:
ASP Tag:

<asp:DropDownList ID="ddlFrom" runat="server" DataSourceID="sdsWE" DataTextField="MyDate" DataValueField="MyDate" CssClass="form" AutoPostBack="True" ></asp:DropDownList>                        &nbsp;
     <asp:SqlDataSource ID="sdsWE" runat="server" ConnectionString="<%$ ConnectionStrings:BusDevConnectionString %>" SelectCommand="View_WE_DD" SelectCommandType="StoredProcedure"></asp:SqlDataSource>

Open in new window


Stored Procedure:
SELECT distinct convert(varchar(10),Ship_Date,101) 'MyDate',
ship_date 'ResolvedDate'
--	convert(varchar(10),Resolved_Date,101) 'MyDate'

      FROM Lear_Shipping
where convert(varchar(10),Ship_Date,101) IS NOT NULL  AND Ship_Date > '12/01/2011'
Order By 'ResolvedDate' DESC

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
Try this:

while not ddlFrom.Items.Contains(new ListItem(dt,dt))
   dt = DateTime.Now.AddDays(-1 * days).Tostring("MM/dd/yyyy")
  days += 1  'keep going backward in time.
end while


ddlFrom.SelectedValue = dt
0
JessyRobinson1234Author Commented:
Error:

The added or subtracted value results in an un-representable DateTime. Parameter name: value
0
Kyle AbrahamsSenior .Net DeveloperCommented:
are there actual values in the ddlFrom?

If so, can you send a screen shot?  Is it finding the date or still overflowing?
0
JessyRobinson1234Author Commented:
Thanks. That didn't work. For some odd reason the selected value of ddlFrom remains ""
0
JessyRobinson1234Author Commented:
I think it may have something to do with the fact that I am running this code on page_load and therefore no databound yet. When I add ddlFrom.databind() prior to the while loop it works.
0
JessyRobinson1234Author Commented:
This works, but can i wirte it all in one and the same while loop?

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then

            Dim dtFrom As String
            Dim dtTo As String
            Dim days As Integer = 2

            ddlFrom.DataBind()
            ddlTo.DataBind()

            dtFrom = DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")
            dtTo = DateTime.Now.ToString("MM/dd/yyyy")

            While Not ddlFrom.Items.Contains(New ListItem(dtFrom, dtFrom))
                dtFrom = DateTime.Now.AddDays(-1 * days).ToString("MM/dd/yyyy")
                days += 1  'keep going backward in time.
            End While

            While Not ddlTo.Items.Contains(New ListItem(dtTo, dtTo))
                dtTo = DateTime.Now.AddDays(-1 * days).ToString("MM/dd/yyyy")
                days += 1  'keep going backward in time.
            End While

            ddlFrom.SelectedValue = dtFrom
            ddlTo.SelectedValue = dtTo

        End If
    End Sub

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
Only if dtFrom and To are populate and ordered in the same exact way.

if that's the case you can eliminate the second loop.

If they're different, or can be different, use 2 loops.

In terms of efficiencies you should only have a couple of executions max . . . it'll still be fast.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.