views:

2794

answers:

5
MyDataSource.SelectParameters["startDate"].DefaultValue =
                                     fromDate.SelectedDate.Date.ToString();

As you can see I am setting the default value of a parameter in my .Net SqlDataSource. I don't think the string is outputting the correct format though. What is the correct format so a T-SQL Datetime will match it properly?

Here are the formats that are outputted right now:

  • 5/1/2009 12:00:00 AM
  • 5/4/2009 11:59:59 PM

Edit: Before I had DataSource where it should have been written SqlDataSource

And here is some code

<asp:SqlDataSource ID="MyDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:sampledb %>" SelectCommand="Recording_Select" SelectCommandType="StoredProcedure" OnSelecting="MyDataSource_Selecting">
    <SelectParameters>
        <asp:ControlParameter ControlID="respondentID" DefaultValue="%" Name="RespondentID" PropertyName="Text" Type="String" />
        <asp:SessionParameter Name="projectCode" SessionField="proj" Type="String" />
        <asp:ControlParameter ControlID="interviewerList" Name="InterviewerID" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="dispCodeList" Name="dispCode" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="fromDate" DefaultValue="1/1/1900" Name="startDate" PropertyName="SelectedValue" Type="DateTime" />
        <asp:ControlParameter ControlID="toDate" DefaultValue="1/1/1900" Name="endDate" PropertyName="SelectedValue" Type="DateTime" />
    </SelectParameters>
</asp:SqlDataSource>

Edit: Well, I figured out the problem and it will seem a bit silly. The code was never calling the DefaultValue because there was always a value present. So my only solution was to make a new hidden label control and load the corrected datetime into that. That pretty much did the trick. I don't know how I overlooked that.

+1  A: 

I would use this:

MyDataSource.SelectParameters["startDate"].DefaultValue =
                                     fromDate.SelectedDate.Date.ToString("yyyyMMdd HH:mm:ss");
Jhonny D. Cano -Leftware-
+4  A: 

There's a SqlDateTime struct you can use for this

DateTime DotNetDateTime = new DateTime(2009, 5, 4);
System.Data.SqlTypes.SqlDateTime sqlDateTime = new System.Data.SqlTypes.SqlDateTime(DotNetDateTime);

Or directly in one line (as the SqlDateTime takes the same args in one of the constructor overloads as the .NET DateTime class):

System.Data.SqlTypes.SqlDateTime sqlDateTime = new System.Data.SqlTypes.SqlDateTime(2009, 5, 4);
BenAlabaster
I simply can't get this to work. DefaultValue requires a string type.
Joe Philllips
The SelectParameter you mean? YYYYMMDDHHMMSS is Microsoft's recommended format for transferring date types between systems as [so they say] they will all recognise it correctly.
BenAlabaster
+2  A: 

How are you initially creating the parameter? You should be able to specify the datatype as datetime at that time. Then assign your datetime to the parameter directly rather than trying to convert it to a string.


Based on the code you posted, your best option is probably just to pre-populate your control. Additionally, that looks fine for the default value in that context, so I suspect the problem is somewhere else. Try setting the CancelSelectOnNullParameter property of the control to False.

Joel Coehoorn
Good suggestions. I didn't make this in the first place so I'll have to look into why it's not set up that way.
Joe Philllips
I guess the wizard was used to set up a SqlDataSource and I did not see any way to choose a type
Joe Philllips
It should have generated some code - it would help to see that code.
Joel Coehoorn
Your wish is my SqlCommand.
Joe Philllips
Still no luck with the CancelSelectOnNullParameter
Joe Philllips
A: 

I always force my dates to MMM dd yyyy when passing as paramters, that way there is no way it can gets messed up and flipped around.

schooner
+1  A: 

This should do the trick:

MyDataSource.SelectParameters["startDate"].DefaultValue = fromDate.SelectedDate.Date.ToString("yyyy-MM-dd HH:mm:ss")
Colin Cochrane
For some reason this did not work but I do not know why. Investigating.
Joe Philllips
Check your locale (both database and .NET) to make sure they match.
CMerat
Still no luck.........
Joe Philllips