How to save date, datetime and time to SQLite Database in Xamarin Forms?

Question

I want to insert a Date, DateTime(without seconds) and Time to my SQLite database. I can insert the data but the information is wrong. The variable that holds the data is correct but when the data is inserted to the database the information is wrong.

I have this table:

[Table("tblCaf")]
public class CAFTable
{
[PrimaryKey, MaxLength(100)]
public string CAFNo { get; set; }
public int EmployeeID { get; set; }
public DateTime CAFDate { get; set; }
[MaxLength(100)]
public string CustomerID { get; set; }
public DateTime StartTime { get; set; }
public DateTime EndTime { get; set; }
public string Photo1 { get; set; }
public string Photo2 { get; set; }
public string Photo3 { get; set; }
public string MobilePhoto1 { get; set; }
public string MobilePhoto2 { get; set; }
public string MobilePhoto3 { get; set; }
[MaxLength(1000)]
public string Remarks { get; set; }
[MaxLength(1000)]
public string OtherConcern { get; set; }
public DateTime LastSync { get; set; }
public DateTime ServerUpdate { get; set; }
public DateTime MobileUpdate { get; set; }
}

This is my code:

var caf = entCafNo.Text;
var retailerCode = entRetailerCode.Text;
var employeeNumber = entEmployeeNumber.Text;
var date = dpDate.Date; //I get the date inside the datepicker
var startTime = tpTime.Time; //I get the time inside the time picker
var endTime = DateTime.Now.TimeOfDay; //I get the current time
var photo1url = entPhoto1Url.Text;
var photo2url = entPhoto2Url.Text;
var photo3url = entPhoto3Url.Text;
var otherconcern = entOthers.Text;
var remarks = entRemarks.Text;
var current_datetime = DateTime.Now.ToString("yyyy-MM-dd hh:mm"); //I get the current datetime

string caf_sql = "INSERT INTO tblCaf(CAFNo, EmployeeID, CafDate, CustomerID, StartTime, EndTime, Photo1, Photo2, Photo3, Remarks, OtherConcern, LastSync, MobileUpdate) 
VALUES('" + caf + "','" + employeeNumber + "', '" + date + "', '" + retailerCode + "', '" + startTime + "', '" + endTime + "', '" + photo1url + "', '" + photo2url + "', '" + photo3url + "', '" + remarks + "', '" + otherconcern + "', '" + current_datetime + "', '" + current_datetime + "')";

await conn.ExecuteAsync(caf_sql);

I can get the right date, time and datetime. The problem is when I save this the date became 01/01/0001, the time became 00:00:00 and the datetime became 01/01/0001 00:00:00 in other words the data is not adding correctly. What can I improve to my code?

is there a reason you're doing an insert query instead of using the Insert method? — Jason
await conn.InsertAsync(obj); where obj is a CAFTable - see the docs: github.com/praeclarum/sqlite-net — Jason
You are also using TimeSpan objects for times and inserting them into DateTime fields, which will not work — Jason
@Jason I don't understand can you show me a code to improve my code? — loot verge

Jason Jason · Accepted Answer · 2018-09-23T05:04:02

instead of manually building an insert statement, you'll probably get better results by using the Insert function

var item = new CAFTable {
  CAFDate = dpDate.Date,
  StartTime = tpTime.Date, // note Time is a TimeSpan, not a DateTime
  EndTime = DateTime.Now, // note TimeOFDay is a TimeSpan, not a DateTime
  LastSync = DateTime.Now,
  MobileUpdate = DateTime.Now
  ... set any other properties as needed
};

await conn.InsertAsync(item);

How to save date, datetime and time to SQLite Database in Xamarin Forms?

1 Answers