[Gtk-sharp-list] SQLite Insert Statement With Parameters Generates SQL logic error or missing database

[Aaron Radich]

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