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();

我正在尝试调试此 INSERT，但运气不佳。为什么 sqllite 试图将我的日期时间转换为 int？数据库模式需要一个日期时间对象。有没有办法在发送到数据库时查看插入命令文本？

下面是我尝试插入的类型:

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; }

这是插入的一个例子:

INSERT INTO DATAOUT (id, uid, application, eventname, clienttoken, version, datetime, 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 times or in non-Attic dialects. For det","answer":"nunciations "},{"id":"beta_456","question":"official documents such as laws an","answer":"or modif"},{"id":"gamma_789","question":" maintained or modified slightly to fit Greek phonology; thus, ?","answer":"iation of Attic"},{"id":"delta_098","question":"econstructed pronunciation of Attic in the late 5th an","answer":"unciation of "},{"id":"epsilon_076","question":"erent stylistic variants, with the descending tail either going straight down o","answer":"Whole bunch"},{"id":"zeta_054","question":"rough breathing when it begins a word. Another diacritic use","answer":"other dia"}]')

最后一个值是一个 JSON 字符串。 dateTime 字段显然是问题所在(第 7 个参数)。对于上面的示例，我只是添加了文本“1/1/2011”。代码正在通过参数构造插入。 datetime 参数在调试器中具有有效日期。

当我检查参数“dateTimeParam”时，调试器显示 dbType = Int32。这是怎么回事？

更新

删除了插入语句中参数周围的引号。它导致插入字符串文字@paramname。 谢谢!

最佳答案

我的猜测是您在查询中没有围绕 @dateTimeParam 的引号，并且查询解析器假设它是一个 int。

但真正让我感到困扰的是为什么要用引号将参数括起来。

试试这个:

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);

更新

我不知道是什么导致了这个问题。我试着模仿 this code .这有帮助吗？

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();

关于c# - Sqlite - 插入给出错误 - 无效的转换异常 - "Invalid cast from ' DateTime' 到 'Int32'。”，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/5144569/