none
SQL CE and DateTime

    Question

  • 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(TableName);
                        qry.Append("  (DateCreated DATETIME not null PRIMARY KEY,");
                        qry.Append("  Price FLOAT not null)");

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

               }

    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);
                        rs.Insert(record);
                    }
                }

    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

Answers