asked on

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

Kyle Abrahams, PMP

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.

JessyRobinson1234

ASKER

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'.

Kyle Abrahams, PMP

try:
Contains (New ListItem(dt))

JessyRobinson1234

ASKER

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

String was not recognized as a valid DateTime.

Kyle Abrahams, PMP

mm should be MM

JessyRobinson1234

ASKER

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

Kyle Abrahams, PMP

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

JessyRobinson1234

ASKER

I actually did and still get the error.

Kyle Abrahams, PMP

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

JessyRobinson1234

ASKER

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

Kyle Abrahams, PMP

It means it's not finding the date.

How is your combobox populated?

JessyRobinson1234

ASKER

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

Kyle Abrahams, PMP

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

JessyRobinson1234

ASKER

Error:

The added or subtracted value results in an un-representable DateTime. Parameter name: value

Kyle Abrahams, PMP

are there actual values in the ddlFrom?

If so, can you send a screen shot? Is it finding the date or still overflowing?

JessyRobinson1234

ASKER

Thanks. That didn't work. For some odd reason the selected value of ddlFrom remains ""

JessyRobinson1234

ASKER

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.

JessyRobinson1234

ASKER

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

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial