1

I am currently working on a project where I use Unity (C#) with SQLite, I am trying to make a database each time a player starts a new game and after some reaserch I found out that I should use WHERE NOT EXISTS to Insert items that is not in the db from before. What did I do wrong since my code isn't working, but a normal insert works.

void QueryPlayer(string firstName, string lastName, int team) {
    string doneString;

    //This works
    //doneString = "INSERT INTO Players (firstName, lastName, teamPlayingOn) VALUES ('Kyrie', 'Irving', 8)"; 


    doneString = @"INSERT INTO Players(firstName, lastName, teamPlayingOn) SELECT 
        '" + firstName+ @"' as firstName,
        '" + lastName + @"' as lastName,
        " + team + @" as teamPlayingOn
    FROM Players
        WHERE NOT EXISTS(SELECT * 
        FROM Players WHERE  
        firstName='" + firstName+"' AND lastName='"+lastName+"' AND teamPlayingOn='"+team+@"')
    ";

    QueryDB(doneString);
}

My database is:

CREATE TABLE IF NOT EXISTS Players(
playerID INTEGER PRIMARY KEY AUTOINCREMENT,
firstName VARCHAR(99),
lastName VARCHAR(99),
teamPlayingOn INTEGER,
FOREIGN KEY(teamPlayingOn) REFERENCES Teams(teamID)
user3604304
  • 55
  • 7
  • Define 'not working'. Does it throw an exception? Something else? – mjwills Jul 16 '17 at 21:52
  • 1
    Also have a read of https://stackoverflow.com/questions/21225354/c-sharp-sqlite-injection ASAP. Your code as-is is open to SQL injection. – mjwills Jul 16 '17 at 21:54

0 Answers0