2

I am trying to insert a record into a table in C#, but when I get to the line of code to execute the query to insert the record into the table, it just comes up with this error:

An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll but was not handled in user code

Additional information: datatype mismatch

The code that I have to insert the data into the database is as follows:

string stmt = string.Format("INSERT INTO quotes (id, user, quote, date) VALUES ('', '{0}', '{1}', '{2}')", person, quote, date);

SQLiteCommand cmd = new SQLiteCommand(stmt, connection);

int rowsAffected = cmd.ExecuteNonQuery();

person, quote and date are all strings and the structure of the database is as follows:

id = INTEGER PRIMARY KEY, user = STRING(255), quote = STRING(255) and date = STRING(255)

Community
  • 1
  • 1
John
  • 45
  • 1
  • 4
  • It looks like you're trying to insert a string (the first element in the values clause) into an integer (id). – Dave D Dec 04 '16 at 17:46
  • I do not know about missmatch problem but you can write query like that too string stmt = string.Format("INSERT INTO quotes (user, quote, date) VALUES ('{0}', '{1}', '{2}')", person, quote, date); if id is autoincremented. – t.m. Dec 04 '16 at 17:46
  • Start by using SQL PArameters. If the text includes something like `O'Toole` it will crash; they will also assure the right type is passed – Ňɏssa Pøngjǣrdenlarp Dec 04 '16 at 17:47
  • Why are you inserting an empty string in the ID ? – Zein Makki Dec 04 '16 at 17:48
  • Look at this answer on how to do your task http://stackoverflow.com/questions/12785780/sqlite-insert-into-identity-column – Steve Dec 04 '16 at 17:48
  • I normally do that for PHP, so I guess it was just a habit @user3185569 – John Dec 04 '16 at 17:49
  • @John Just don't specify the id column if it is auto generated column (Which seems to be from your definition) .. Like this: `INSERT INTO quotes (user, quote, date)` – Zein Makki Dec 04 '16 at 17:49
  • Downvoter care to comment? – John Dec 04 '16 at 17:53

1 Answers1

3

If id is autoincremented, you can insert data with this query

stmt = string.Format("INSERT INTO quotes (user, quote, date) VALUES ('{0}', '{1}', '{2}')", person, quote, date);
t.m.
  • 1,430
  • 16
  • 29