Database input datetime

Questions & Answers
1

I’m building an app and ran into an issue. In all the other tables I’ve used strings. In this one, I’m using datetime for id. string GigID=System.DateTime.Now.ToString(“MM/dd/yyyy”);
I’m using the SQLIter asset pack from the assetstore.
Thanks in advance!

While it seemed to work, I cannot enter anything into the SQLite database(was able to get one in, not sure how) and when I try to display them, I get an error Format Exception on the DATETIME.
Table was set up thusly
mSQLString = “CREATE TABLE IF NOT EXISTS " + SQL_TABLE_NAME + " (” +
COL_TN_ID + " DATE PRIMARY KEY, " +
COL_TN_NAME + " TEXT," +
COL_TN_ADDRESS + " TEXT," +
COL_TN_DATE + " TEXT," +
COL_TN_TIME + " TEXT)";

Form:
public void Form()
{
string ID=System.DateTime.Now.ToString(“MM/dd/yyyy”);
GUI.Label (new Rect (Screen.width/2, 10, 700, 50), “Add-a-Tron!”);
GUI.Label (new Rect (10, 30, 700, 50), “Enter the name of the event” );
Name=GUI.TextField(new Rect(155, 50, 100, 25), Name);
GUI.Label (new Rect (10, 70, 700, 50), “Enter the Address.”);
Address=GUI.TextField(new Rect(155, 90, 100, 25), Address);
GUI.Label (new Rect (10, 110, 700, 50), “Enter the date of the event” );
Date=GUI.TextField(new Rect(155, 120, 100, 25), Date);
GUI.Label (new Rect (10, 130, 700, 50), “Enter the time of the event” );
Time=GUI.TextField(new Rect(155, 140, 110, 25), Time);
if(GUILayout.Button( “Add Gig”))
{
Add(ID, Name, Address, Date, Time);

			}
		}

Add to the Database: (All other functions have worked to put stuff in the database)
public void Add(string ID, string Name, string Address, string Date, string Time)
{
Debug.Log(ID + " "+ Name + " " + Address + " " + Date + " " +Time);

			mSQLString = "INSERT OR REPLACE INTO " + SQL_TABLE_NAME
				+ " ("
					+ COL_TN_ID + ","
					+ COL_TN_NAME + ","
					+ COL_TN_ADDRESS + ","
					+ COL_TN_DATE + ","
					+ COL_TN_TIME
					+ ") VALUES ("
					+ "'" + ID + "',"  // note that string values need quote or double-quote delimiters
					+ "'" + Name + "',"
					+ "'" + Address + "',"
					+ "'" + Date + "',"
					+ "'" + Time +"');";
			
			Debug.Log(mSQLString + "Added!");
			ExecuteNonQuery(mSQLString);
		}

To display them onscreen, I made a prefab and hooked it up. Here are the functions that handle that. I did put in a test entry using the newest DB Browser for SQLite:

public void GetAll()
	{
		Listname.Clear();
		mConnection.Open();
		mSQLString = "SELECT * FROM " + SQL_TABLE_NAME; 
		mCommand.CommandText = mSQLString;
		mCommand.ExecuteNonQuery();
		mReader = mCommand.ExecuteReader();
		
		while (mReader.Read())
		{
			listname.Add(new listname(mReader.GetString(0), 
			                  mReader.GetString(1), 
			                  mReader.GetString(2), 
			                  mReader.GetString(3), 
			                  mReader.GetString(4)));

			//Debug.Log(mReader.GetDateTime(0) + mReader.GetString(1) +mReader.GetString(2) + mReader.GetString(3) + mReader.GetDateTime(4) );
		}

Finally…

public void ShowAll()
	{	Debug.Log(listname.Count);
		for(int i = 0; i< listname.Count; i++)
		{
			GameObject giObj = Instantiate(thePrefab);
			listname tmpVar = listname*;*

  •  	giObj.GetComponent<theScript>().Displaylistnames(tmplistname.ID, tmplistname.Name, tmplistname.Address, tmplistname.Date, tmplistname.TIme);*

  •  	giObj.transform.SetParent(Equipparent);* 

  •  }*

  • }*

2

I’ve not read all your code, but I expect the problem is that you’re trying to use a DateTime as a primary key. In SQL terms, the primary key must be unique and a datetime (especially one restricted to just the year, month and day) will only be unique if you only expect one entry per day. I guess this is how you managed to enter one row.

You might be able to get away with including hours, minutes and seconds to form the id but it’s probably not a good idea as two rows within the same second will cause issues. Also, you generally shouldn’t be using a string for your ID as it won’t be efficient when querying the DB.

So, in short, make sure your Primary key is an ID or something that is guaranteed to be unique. Either a simple integer or if supported, a GUID. This can be auto generated for you by your database when setup properly and inserting a new row.

3

I suddenly realized after posting this that I had moved the project to another drive and had been looking at the database from the original! D’OH! Things had been saved after all!
I think I will switch over to an integer id instead. Should I use AUTOINCREMENT after primary key specificity? There had been some controversy about using it.

If anyone else has ideas or suggestions, please let me know.
Thanks!