agnostic date query fromdropdown

[shaunk]

I have a dropdown of dates which is a controlparameter(@Provider_Date) used in a sqldatasource query.

WHERE (convert(varchar,Provider_Day.Provider_Date,103) = convert(varchar,@Provider_Date,103).

The trouble I am having is to craft the dropdown of dates(sorted by date,today plus 10 days forward), so that the value of the dropdown works in the SQL Server dateformat agnostic query above, and is sorted properely and is minus the time component in the dropdown.

The code I have is:

DimcFowardate as sorteddictionary(of date,string)
Dim today As New DateTime(Now.Year, Now.Month, (Now.Day))

For Counter As Integer = 0 To 10

Dim dt As New DateTime(DateTime.Now.AddDays(Counter).Year, DateTime.Now.AddDays(Counter).Month, DateTime.Now.AddDays(Counter).Day)



dcFowardDates.Add(FormatDateTime(DateTime.Now.AddDays(Counter), vbShortDate), FormatDateTime(DateTime.Now.AddDays(Counter), vbShortDate))


Next



drpFowardDates..DataSource = dcFowardDates

drpFowardDates..DataTextField = "key"

drpFowardDates..DataValueField = "value"

drpFowardDates.DataBind()







The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[jmeckley]

your sql doesn't make sense. why convert the date to a string just to compare dates? if you want to compare dates, compare the values as a date type.

as for getting the value out of the drop down and into sql I recommend 2 things.
1. git rid of the sql datasource control. it serverly limits your control over data. you cannot preformance tune a sql datasource either, this is key for any medium to large scale project.
2. use a date picker control to select the date rather than a drop down. it's more natural for the user. there are a number of date picker controls out there. jquery.ui, basicdatepicker, ms ajax control toolkit.

parse the date from the request and pass this value in as a sql parameter.

var date = DateTime.Parse(thecontrol.Text);

var parameter = command.CreateParameter();
parameter.Name = "Prod_Date";
parameter.Value = date;
command.Parameters.Add(parameter);

...

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[shaunk]

OK.
You wrote:
"your sql doesn't make sense. why convert the date to a string just to compare dates? if you want to compare dates, compare the values as a date type."

The original query in the sqldatasource was
WHERE (Provider_Day.Provider_Date = @Provider_Date AND Providers.Current_Clinician = 'True').
@Provider_Date is the control parameter set to the selected value of the dropdownlist of dates.

This works fine on the development website(on my PC), but not on the Production website. We have only just implemented this in Production...it is not live yet. This is the case when I am pointing both Dev and Prod to the same database(Production).

So I figure it's some sort of dateformat issue and to take it out of the equation I introduce the conversion to format 103 (dd/mm/yy) in the query, which works in Dev but not Prod.

I have worked out how to populate the dropdownlist with the correct dates. I take your point about using a better control, but am not in a position to investigate the AJAX toolkit and other options yet.

I do use ADO.net for high volume pages but this particular page is low volume, low usage and I'm happy sticking to the sqlDatasource control.

Thanks






The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[jmeckley]

if this works on your local box and the dev server, but not the prod server the problem isn't the date format, it's somewhere else.

by using the sqldatasource you inhibit your ability to debug the code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[shaunk]

Apologies re my lack of knowledge about using this forum correctly but I cannot seem to find how you can include a code block as you have in your thread?

I solved the problem by adding 'set dateformat dmy;' to the top of the query. I reverted to my original code where the date was just a straight compare.

I execute the following code in dev and prod with the same result i.e. us_english, which defines how dates are interpreted by default.

declare @lang varchar(100)
set @lang = @@language
select @lang

I also checked the language in use for the logins in dev and prod and they are the same - us_english.

So I'm uneasy about the solution as I think you are right in saying it is some other problem.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[jmeckley]

below the reply textbox are a series of checkboxes (step 2). click on the link for Process TGML. it will show you a list o possible tags. example:
[ignore]

Console.WriteLine("Hello World");

[/ignore]
will display

Console.WriteLine("Hello World");

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259