adding extra values to datetime

[dipopo]

Hello Guys,

I have a webpage for selecting values from a MySQL database, using 1 dropdownlist and 2 calendars for date.

My problem is that the calendar returns dates in the form '08/06/2011' and my database date entries are in datetime format '08/06/2011 14:00:21' hence decided to add the time on the date selection in my query as such

SqlDataSource2.SelectCommand = "SELECT time as TIME,sum(value) as Value FROM splog WHERE register = @register1 AND (time BETWEEN @time2 00:00:00 AND @time1 00:00:00)";

But I get errors regarding this at runtime.

How do I add the time field of the datetime in my command?

Thanks in advance.

Dipopo

[thines01]

You would need to remove the 00:00:00 from your SQL string (first).
Then, if the objects behind parameters @time1 and @time2 are DateTime objects, add the extra time (to the ending time) there (in the objects).
...then convert them to the SQL-suitable timestamps.

Example:

[Actual Timestamps / file names]
2010-08-11 09:03:22	 yayz.txt
2011-04-28 18:01:16	 MSTR_0428111801.csv
2012-03-02 12:26:53	 LOADCELL_030212.csv
1999-01-01 00:00:00	 yaz.txt
2012-03-04 07:37:39	 DB
2012-03-05 05:37:38	 DB
2011-06-28 15:14:22	 Info_2011_06.zip
2012-03-04 23:21:44	 NPA

[Target Timestamps]
2010-08-11 09:03:22	 yayz.txt
2011-04-28 18:01:16	 MSTR_0428111801.csv
1999-01-01 00:00:00	 yaz.txt

using System;
using MySql.Data.MySqlClient;

namespace DW_415687_CS_CON
{
   using DB_MyLister; //Personal DB Connection string holder
   class Program
   {
      static void Main(string[] args)
      {
         using (MySqlConnection conn = new MySqlConnection(CDB_MyLister.csb.ToString()))
         {
            conn.Open();

            string strSQL =
               "SELECT LAST_FILE_NAME from LAST_SYSTEM_FILE " +
               "where (DATE_LOADED Between @time1 And @time2)";

            DateTime time1 = new DateTime(1999, 1, 1, 0, 0, 0);
            DateTime time2 = new DateTime(2011, 5, 28, 6, 1, 16);

            MySqlCommand cmd = new MySqlCommand(strSQL, conn);
            cmd.Parameters.AddWithValue("@time1", time1);
            cmd.Parameters.AddWithValue("@time2", time2);

            using(MySqlDataReader rdr = cmd.ExecuteReader())
            {
               while(rdr.Read())
               {
                  Console.WriteLine(rdr["LAST_FILE_NAME"].ToString().Trim());
               }
               //
               rdr.Close();
            }
            //
            conn.Close();
         }
      }
   }
}

Result:

yayz.txt
MSTR_BSA1_MBD_0428111801.csv
yaz.txt

[dipopo]

Thanks Thines01, I should understand your example but alas not too bright me. My code is as such for the parameter assignment. How do I include it here.

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:sppduConnectionString %>"
ProviderName="<%$ ConnectionStrings:sppduConnectionString.ProviderName %>"
SelectCommand="SELECT sum(value) as Value FROM splog WHERE register = @register1 AND (time BETWEEN '@time2' AND '@time1')">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList3" Name="register1" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="Calendar1" Name="time1"
PropertyName="SelectedDate" Type="DateTime" />
<asp:ControlParameter ControlID="Calendar2" Name="time2"
PropertyName="SelectedDate" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>

[thines01]

Where in your code are you getting errors at runtime?

I see you have a variable called SelectCommand, but where are you building your MySqlCommand?

I don't see any references to MySql at all in your code.

The example I gave can easily be placed into a code-behind function and I would do it no other way.

Don't modify anything until you understand it.

[dipopo]

Thanks again Thines01 very much appreciated, no longer getting any errors having removed the 00:00:00 appendage.

My codebehind is as follows:

protected void Button2_Click(object sender, EventArgs e)
        {
            \* to show what is selected 
Label1.Text = Calendar1.SelectedDate.ToShortDateString() + " " + Calendar2.SelectedDate.ToShortDateString(); *\
            SqlDataSource2.SelectCommand = "SELECT sum(value) as Value FROM splog WHERE register = @register1 AND (time BETWEEN @time2 AND @time1)";
            SqlDataSource2.Select(DataSourceSelectArguments.Empty);
            GridView2.DataBind();
        }

[thines01]

Where are you binding the Parameters to your MySqlCommand?

[dipopo]

Hi Thines01, this is done in the sqldatasource control parameter.

<SelectParameters>
<asp:ControlParameter ControlID="DropDownList3" Name="register1" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="Calendar1" Name="time1"
PropertyName="SelectedDate" Type="DateTime" />
<asp:ControlParameter ControlID="Calendar2" Name="time2"
PropertyName="SelectedDate" Type="DateTime" />
</SelectParameters>

[dipopo]

Sorted this thanks Thines01, apparently I had changed the name for a control parameter and VS2010 did not like it. Deleted the sqldatasource and recreated it and problem solved.