AJAX Calendar and SQL2005 datetime Column...

HellFireClub

Hi Folks,

I've an AJAX Calendar on a new aspx page that I'm experimenting on to try to get a few SQL queries running on. The idea is that I set up an SQL datasource on the page to my MS SQL 2005 DB, to retrieve data from my DB WHERE the date in by datetime Column in my DB matches the date displayed in my AJAX calendar control. Then I'll bind a GridView back to my SQL Datasource and that's the end result I'm looking for...

What I'm wondering is, if the datetime string in my DB is of the format 17/03/2011 20:33:14, (I've copied & pasted this directly from the data cell in my DB), how should I set up my WHERE query in my SQL Datasource???

This is my AJAX code below:

<form id="form1" runat="server">
<ajaxToolkit:ToolkitScriptManager runat="Server" EnableScriptGlobalization="true" EnableScriptLocalization="true" ID="ScriptManager1" />
<asp:TextBox runat="server" ID="Date5" />
<asp:ImageButton runat="Server" ID="Image1" ImageUrl="~/images/Calendar_scheduleHS.png" AlternateText="Click to show calendar" /><br />
<ajaxToolkit:CalendarExtender ID="calendarButtonExtender" runat="server" TargetControlID="Date5"
PopupButtonID="Image1" />


</form>

This gives me back a TextBox with the date in the format: 17/03/2011

But in my DB, the datetime is in the format: 17/03/2011 20:33:14

So I'm a bit hung up on how to set up my SQL DataSource for this little exercise, I know the lazy way to do it is to insert the date on it's own when the DB record is created and the time in a separate column and to run a simple SQL query that will be comparing two fields of identical formats but it looks a bit ineffieicnt/lazy on the wider scale of things I think and it's not the right way to do it and I've to keep the datetime format in that particular format as it is also used to generate a unique reference number for my project, (Realex related requirement)...

Thanks in advance for any help with this...

Giblet

Try tolongdatestring or "F" as a format pattern

HellFireClub

Giblet wrote: »

Try tolongdatestring or "F" as a format pattern

Would that not format my datetime string to something like 17/03/2011 00:00:00, and cause my SQL Datasource to return no rows, unless one just happened to have the datetime string, 17/03/2011 00:00:00???

But you're onto something there, what I need to do I think is the opposite, I need to truncate the datetime string that is being referenced by my SQL datasource, basically cut the time bit of it away...

FSL

Use Like rather than =.

HellFireClub

For any folks who come across this thread, I've come up with a very simple solution that works a dream...

<form id="form1" runat="server">

<ajaxToolkit:ToolkitScriptManager runat="Server" EnableScriptGlobalization="true" EnableScriptLocalization="true" ID="ScriptManager1" />

<asp:TextBox runat="server" ID="FirstDate" />
<asp:ImageButton runat="Server" ID="Image1" ImageUrl="~/images/Calendar_scheduleHS.png" AlternateText="Click to show calendar" /><br />
<ajaxToolkit:CalendarExtender ID="calendarButtonExtender" runat="server" Format="dd/MM/yyyy HH:mm:ss" TargetControlID="FirstDate" PopupButtonID="Image1" />

<asp:TextBox runat="server" ID="LastDate" />
<asp:ImageButton runat="Server" ID="Image2" ImageUrl="~/images/Calendar_scheduleHS.png" AlternateText="Click to show calendar" /><br />
<ajaxToolkit:CalendarExtender ID="calendarButtonExtender2" runat="server" Format="dd/MM/yyyy HH:mm:ss" TargetControlID="LastDate" PopupButtonID="Image2" />
<asp:Button ID="PostButton" runat="server" onclick="PostButton_Click" PostBackUrl="~/Admin/Calendar.aspx" style="text-align: center" Text="Search" />


<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT * FROM [MyTable] WHERE (([Timestamp] > @Start) AND ([Timestamp] < @End))&quot;>
<SelectParameters>
<asp:ControlParameter Name="Start" ControlId="FirstDate" Type="DateTime"
PropertyName="Text" />
<asp:ControlParameter Name="End" ControlId="LastDate" Type="DateTime"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
EnableModelValidation="True" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID:" SortExpression="ID" />
<asp:BoundField DataField="ProductType" HeaderText="Name:" SortExpression="Name" />
<asp:BoundField DataField="Description" HeaderText="Description:" SortExpression="Description" />
</Columns>

</asp:GridView>

</form>
</body>
</html>

In the Codebehind:

protected void PostButton_Click(object sender, EventArgs e)
{
GridView1.DataBind();
}

The trick is to add Format="dd/MM/yyyy HH:mm:ss" onto the Ajax Calendar extender control, this makes the string in the textbox suitable for the necessary datetime format and comparison...

Giblet

HellFireClub wrote: »

Would that not format my datetime string to something like 17/03/2011 00:00:00, and cause my SQL Datasource to return no rows, unless one just happened to have the datetime string, 17/03/2011 00:00:00???

But you're onto something there, what I need to do I think is the opposite, I need to truncate the datetime string that is being referenced by my SQL datasource, basically cut the time bit of it away...

Ah for some reason I thought you were generating a timestamp with full date-time but you weren't seeing it all. My mistake!