Sqlite - Insert gives error - Invalid casting exception - "Invalid cast from" DateTime "to" Int32 "."

Question

var idParam = new SQLiteParameter("@idParam", SqlDbType.Text) { Value = insertData.ID }; var userIdParam = new SQLiteParameter("@userIdParam", SqlDbType.VarChar) { Value = insertData.Uid }; var applicationNameParam = new SQLiteParameter("@applicationNameParam", SqlDbType.VarChar) { Value = insertData.Application }; var eventNameParam = new SQLiteParameter("@eventNameParam", SqlDbType.VarChar) { Value = insertData.EventName }; var clientTokenParam = new SQLiteParameter("@clientTokenParam", SqlDbType.VarChar) { Value = insertData.ClientToken }; var versionParam = new SQLiteParameter("@versionParam", SqlDbType.VarChar) { Value = insertData.Version }; var dateTimeParam = new SQLiteParameter("@dateTimeParam", SqlDbType.DateTime) { Value = insertData.DateTime }; var dataParam = new SQLiteParameter("@dataParam", SqlDbType.Text) { Value = insertData.Data }; SQLiteCommand command = new SQLiteCommand("INSERT INTO DATAOUT (id, uid, application, eventname, clienttoken, version, datetime, data)" + " VALUES ('@idParam','@userIdParam','@applicationNameParam','@eventNameParam','@clientTokenParam','@versionParam',@dateTimeParam,'@dataParam')",Connection); command.Parameters.Add(idParam); command.Parameters.Add(userIdParam); command.Parameters.Add(applicationNameParam); command.Parameters.Add(eventNameParam); command.Parameters.Add(clientTokenParam); command.Parameters.Add(versionParam); command.Parameters.Add(dateTimeParam); command.Parameters.Add(dataParam); command.ExecuteNonQuery(); 

I am trying to debug this INSERT, but I am not very lucky. Why is sqllite trying to pass my datetime to int? The database schema expects a datetime object. Is there any way to see the text of the insert command since it is being sent to db?

Here's a look at the types I'm trying to insert:

 public string Uid { get; private set; } public string Application { get; private set; } public string EventName { get; private set; } public string ClientToken { get; private set; } public string Version { get; private set; } public DateTime DateTime { get; private set; } public string Data { get; private set; } 

Here is an example insert:

INSERT INTO DATAOUT (id, uid, application, eventname, clienttoken, version, date and time, data) VALUES ('00000000-0000-0000-0000-000000000000', '123abcd', 'My Application' '', 'Test Client '' 1.0.0 ',' 1/1/2000 ',' [{"ID": "alpha_123", "question": "ronunciations in pre-classical time or in non-Attic dialects. For DET" "answer": " nunciations "}, {" ID ":" beta_456 "," question ":" Official documents such as laws "„ answer “:" or MODIF "}, {" ID ":" gamma_789 "," question ":" slightly Greek phonology is supported or modified; Thus ,? "," answer ":" Attic "}, {" ID ":" delta_098 "," question ":" attic pronunciation Attica at the end of the fifth "answer": "annulment"}, {"ID": "epsilon_076", "in pros ":" different stylistic options, with a downward tail down o "," answer ":" Whole bunch "}, {" ID ":" zeta_054 "," question ":" rough breathing when a word begins. Other diacritical use " , "answer": "other dia"}] ')

The last value is a JSON string. The dateTime field seems to be a problem (7th parameter). For the example above, I just added the text '1/1/2011'. The code creates an insert using parameters. The datetime parameter has a valid date in the debugger.

When I check the "dateTimeParam" parameter, the debugger shows dbType = Int32. What's up with that?

UPDATE

The quotes around the parameters in the insert statement are removed. This results in string literals, @paramname to be inserted. Thanks!

Answer 1

I assume that you do not have quotes around @dateTimeParam in the request, and the request parser assumes this is an int .

Which really bothers me, so why do you surround the parameters with quotation marks in general.

Try the following:

 var command = new SQLiteCommand ( "INSERT INTO DATAOUT (id, uid, application, eventname, clienttoken, version, datetime, data)" + " VALUES (@idParam, @userIdParam, @applicationNameParam, @eventNameParam, @clientTokenParam, @versionParam, @dateTimeParam, @dataParam)", Connection); 

UPDATE

I do not know what causes the problem. I tried to simulate this code . Does it help?

 var command = new SQLiteCommand ( "INSERT INTO DATAOUT (id, uid, application, eventname, clienttoken, version, datetime, data)" + " VALUES (@idParam,@userIdParam,@applicationNameParam,@eventNameParam,@clientTokenParam,@versionParam,@dateTimeParam,@dataParam)", Connection); command.Parameters.AddWithValue("@idParam", insertData.ID); command.Parameters.AddWithValue("@userIdParam", insertData.Uid); command.Parameters.AddWithValue("@applicationNameParam", insertData.Application); command.Parameters.AddWithValue("@eventNameParam", insertData.EventName); command.Parameters.AddWithValue("@clientTokenParam", insertData.ClientToken); command.Parameters.AddWithValue("@versionParam", insertData.Version); command.Parameters.AddWithValue("@dateTimeParam", insertData.DateTime); command.Parameters.AddWithValue("@dataParam", insertData.Data); command.ExecuteNonQuery(); 

Answer 2

My problem was caused by using the parameter data type: System.Data.DbType.DateTime2 and .NET Sqlite api does not recognize this enumeration.