SQL CE and DateTime


  • I am having issues with another personal project using C# and SQL CE.   Not sure where I went wrong so I will post some code.

    Create the Table:

    using (var command = new SqlCeCommand())
                        StringBuilder qry = new StringBuilder();
                        qry.Append("CREATE TABLE ");
                        qry.Append("  (DateCreated DATETIME not null PRIMARY KEY,");
                        qry.Append("  Price FLOAT not null)");

                        command.Connection = cn;
                        command.CommandText = qry.ToString();


    To Update the Table:

                using (var command = new SqlCeCommand())
                    command.Connection = cn;
                    //  Populate Table
                    command.CommandText = TableName;
                    command.CommandType = CommandType.TableDirect;
                   using (SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
                        SqlCeUpdatableRecord record = rs.CreateRecord();
                        record.SetDateTime(0, date);
                        record.SetValue(1, Price);

    Then when doing a Select Query on the DateCreated which is DateTime to return the Price which is a float it breaks:

                protected string GetSessionValueLineQuery(string TableName, DateTime SessionDate)
                    StringBuilder qry = new StringBuilder();
                    qry.Append("SELECT Price FROM ").Append(TableName).Append(" ");
                    qry.Append("WHERE DateCreated = ").Append(SessionDate);
                    return qry.ToString();


    The Qry string returns:

    SELECT Price FROM AllPOC WHERE DateCreated = 3/27/2014 4:15:00 PM

                cmd = new SqlCeCommand(GetSessionValueLineQuery("AllPOC", dt), cn);
                double POC = Convert.ToDouble(cmd.ExecuteScalar());
                 Print(String.Format("   POC {0:0.00}", POC ));

    The Error is "[ Token line number = 1,Token line offset = 56,Token in error = 4 ]"

    So I decided to Print a substring of the Query from 0 to 56 and get this:

    SELECT Price FROM AllPOC WHERE DateCreated = 3/27/2014 4

    So I am guessing that the issue is with DateTime.  Can anyone see where I went wrong?  Creating the Table?  Updating the Table?  Selecting from the table?   Go easy on the Old Guy.  Thanks.

    Sunday, May 25, 2014 3:37 PM