[Gtk-sharp-list] SQLite Insert Statement With Parameters Generates SQL logic error or missing database
Aaron Radich
aaron at radich.com
Mon Aug 22 17:26:09 EDT 2011
I'm trying to execute a SQL statement against a SQLite database with
parameters instead of embedding the parameter values directly into the
SQL command text (not using parameters). I know my database is not read
only because I can do an insert without the parameters. When I use the
parameters, though, I get the error below: I've attached a snippet of
the insert code. If you know what I'm doing wrong, I would greatly
appreciate a tip. Thanks.
Aaron
Exception in Gtk# callback delegate
Note: Applications can use GLib.ExceptionManager.UnhandledException to
handle the exception.
System.Reflection.TargetInvocationException: Exception has been thrown
by the target of an invocation. --->
Mono.Data.SqliteClient.SqliteExecutionException: SQL logic error or
missing database
at Mono.Data.SqliteClient.SqliteCommand.ExecuteStatement (IntPtr
pStmt, System.Int32& cols, System.IntPtr& pazValue, System.IntPtr&
pazColName) [0x0001f]
in /build/buildd/mono-2.6.7/mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs:385
at Mono.Data.SqliteClient.SqliteCommand.ExecuteStatement (IntPtr
pStmt) [0x00000]
in /build/buildd/mono-2.6.7/mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs:374
at Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior
behavior, Boolean want_results, System.Int32& rows_affected) [0x000c3]
in /build/buildd/mono-2.6.7/mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs:613
at Mono.Data.SqliteClient.SqliteCommand.ExecuteNonQuery () [0x00000]
in /build/buildd/mono-2.6.7/mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs:501
at Foodle.SQLiteCommon.ScanHistoryGateway.InsertRecord
(Foodle.SQLiteCommon.ScanHistory recScanHistory) [0x0018e]
in /home/aaron/Foodle/GTK#/SQLiteCommon/ScanHistoryGateway.cs:231
at MainWindow.ProcessFoodleScan (System.String sUPCCode) [0x00080]
in /home/aaron/Foodle/GTK#/KitchenApp/KitchenApp/MainWindow.cs:1097
at MainWindow.OnKeyReleaseEvent (System.Object o,
Gtk.KeyReleaseEventArgs args) [0x00160]
in /home/aaron/Foodle/GTK#/KitchenApp/KitchenApp/MainWindow.cs:1030
at (wrapper managed-to-native)
System.Reflection.MonoMethod:InternalInvoke
(object,object[],System.Exception&)
at System.Reflection.MonoMethod.Invoke (System.Object obj,
BindingFlags invokeAttr, System.Reflection.Binder binder,
System.Object[] parameters, System.Globalization.CultureInfo culture)
[0x000d0]
in /build/buildd/mono-2.6.7/mcs/class/corlib/System.Reflection/MonoMethod.cs:213
--- End of inner exception stack trace ---
public int InsertRecord(ScanHistory recScanHistory)
{
int iScanHistoryId = GetNextTableId();
try
{
if (this.Connection.State != System.Data.ConnectionState.Open)
this.Connection.Open();
IDbCommand objDbCommand = this.Connection.CreateCommand();
// select the data from the db
StringBuilder stbSQL = new StringBuilder();
stbSQL.AppendLine("INSERT INTO ScanHistory ");
stbSQL.AppendLine("(ScanHistoryId, UPCCode, ScanDate, ItemId,
Quantity, Note) ");
stbSQL.AppendLine("VALUES
(:ScanHistoryId, :UPCCode, :ScanDate, :ItemId, :Quantity, :Note); ");
objDbCommand.CommandText = stbSQL.ToString();
// assign the parameters
// ScanHistoryId
objDbCommand.Parameters.Add(new SqliteParameter(":ScanHistoryId",
recScanHistory.ScanHistoryId));
// UPCCode
objDbCommand.Parameters.Add(new SqliteParameter(":UPCCode",
recScanHistory.UPCCode));
// ScanDate
objDbCommand.Parameters.Add(new SqliteParameter(":ScanDate",
recScanHistory.ScanDate));
// ItemId
if (recScanHistory.ItemId > 0)
objDbCommand.Parameters.Add(new SqliteParameter(":ItemId",
recScanHistory.ItemId));
else
objDbCommand.Parameters.Add(new SqliteParameter(":ItemId",
DBNull.Value));
// Quantity
objDbCommand.Parameters.Add(new SqliteParameter(":Quantity",
recScanHistory.Quantity));
// Note
if (recScanHistory.Note != "" && recScanHistory.Note != null)
objDbCommand.Parameters.Add(new SqliteParameter(":Note",
recScanHistory.Note));
else
objDbCommand.Parameters.Add(new SqliteParameter(":Note",
DBNull.Value));
objDbCommand.Prepare();
// execute the insert statement
int iRowsAffected = objDbCommand.ExecuteNonQuery();
if (iRowsAffected == 0)
iScanHistoryId = 0;
// clean up
objDbCommand.Dispose();
objDbCommand = null;
stbSQL = null;
}
finally
{
// close the connection
if (this.Connection.State != ConnectionState.Closed)
this.Connection.Close();
}
return iScanHistoryId;
}
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.ximian.com/pipermail/gtk-sharp-list/attachments/20110822/e3cb789c/attachment.html
More information about the Gtk-sharp-list
mailing list