An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection RSS

• • Reply
  

  Sir Codalot

  Member

  7 Points

  10 Posts

  An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 02, 2012 10:37 PM|LINK

  I'm getting: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  ...but my SQL insert statement IS working - it inserts the correct data from my collection (from a file). My params start at 0. Column names are correct, otherwise the insert wouldn't work. I can't figure out what's going on. 

  foreach (string dataLine in userData)
  {
  var dataItem = dataLine.Split(delimiterChar);
  var FileToDB = "insert into Table1 (Column1, Column2, Column3, Column4, Column5, Column6, Column7) values (@0, @1, @2, @3, @4, @5, @6)";

  db.Execute(FileToDB, dataItem);
  }

  Please help! Thanks! 

• • Reply
  

  Sir Codalot

  Member

  7 Points

  10 Posts

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 03, 2012 12:41 AM|LINK

  By the way, I'm running SQL Server Compact 4.0.8482.1. I don't think this is related to the localization problem that 3.5 had.

  Call stack:

  [IndexOutOfRangeException: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection.]
     System.Data.SqlServerCe.SqlCeParameterCollection.ThrowParamterNotFoundException(String parameterName) +266
     System.Data.SqlServerCe.SqlCeParameterCollection.RangeCheck(String parameterName, SqlCeParameter& param) +164
     System.Data.SqlServerCe.SqlCeParameterCollection.get_Item(String parameterName) +50
     System.Data.SqlServerCe.SqlCeCommand.CreateParameterAccessor(MetaData[] metadata) +267
     System.Data.SqlServerCe.SqlCeCommand.CreateDataBindings() +161
     System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() +863
     System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) +383
     System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() +46
     WebMatrix.Data.Database.Execute(String commandText, Object[] args) +194
     ASP._Page_ApptsImported_cshtml.Execute() in c:\My Documents\My Web Sites\WXwRazor1\ApptsImported.cshtml:36
     System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +273
     System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +107
     System.Web.WebPages.WebPage.ExecutePageHierarchy() +149
     System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +172
     System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContext context) +249
  

• • Reply
  

  GmGregori

  Contributor

  5446 Points

  735 Posts

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 03, 2012 06:20 AM|LINK

  I think that this error is dependent on the presence of an empty or null value.

  You should test every value with a conditional operator like

  (String.IsNullOrEmpty(dataItem[1]) ? DBNull.Value : dataItem[1])

  before to pass it to the sql parameter.

   

• • Reply
  

  Mikesdotnett...

  All-Star

  154852 Points

  19855 Posts

  Moderator

  MVP

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 03, 2012 06:41 AM|LINK

  Parameter values need to be passed in as an array of objects to the Database.Execute method, not an array of strings (which will be viewed as one object). You need to convert your array of strings (created by the Split method) to an array of objects, which is easily achieved:

  var dataItem = dataLine.Split(delimiterChar).Select(value => (object)value).ToArray();

  
  
  

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  Sir Codalot

  Member

  7 Points

  10 Posts

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 03, 2012 12:16 PM|LINK

  Thanks GmGregori, but there aren't any null or empty values in my file. Note, the data IS being written correctly to the database. All fields are populated from the data in my file. I just need this pesky error to go away. 

• • Reply
  

  Sir Codalot

  Member

  7 Points

  10 Posts

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 03, 2012 12:20 PM|LINK

  Thanks, Mike. I tried the array of objects, but I still get the error. Note, the data is being written to the database correctly if I use an array of strings or an array of objects. I'm starting to wonder if this is a platform bug. Why would I get an error if the insert works? 

• • Reply
  

  Sir Codalot

  Member

  7 Points

  10 Posts

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 03, 2012 12:31 PM|LINK

  This used to work, by the way. I noticed that it stopped working when I added an additional column and param. I tried going back to an older version that doesn't have that column and param, but it still crashes. Maybe another file got corrupted? 

• • Reply
  

  Sir Codalot

  Member

  7 Points

  10 Posts

  Re: An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection

  May 04, 2012 07:53 PM|LINK

  I replaced the App_Data folder with an older version and things started working again. So, I'm not sure what was going on, but it's fixed now. Maybe the .sdf got jacked up. Who knows.

  • Marked as answer by Sir Codalot on May 04, 2012 07:53 PM