Tuesday, June 26, 2012

SqlCeException: The specified table does not exist [xxxxx]

Today I spent almost 2 hours just to find out what is the cause of the issue about keep getting SqlCeException: The specified table does not exist [xxxxx]
Here is story.

I intended to create a local database for my phone application, hence I am following the MSDN guide. http://msdn.microsoft.com/en-us/library/hh202876(v=vs.92).aspx#BKMK_CreatingtheDatabase

I was reading the guide while writing my own application with some modification until everything are ready for testing.

Then, when the code run until:
var watchListInDB = WatchDB.WatchTable.Select(x => x);
Watches = new ObservableCollection<Watch>(watchListInDB);

Note: WatchDB is my Database Context.

The error hit at the LINQ Select extension method. I re-read the whole guide again and again just to make sure nothing is missed out. So, here are the things you need to take care of if you are running into the same problem too.

1. Database context had been created in App.xaml.
using (WatchListDataContext db = new WatchListDataContext(WatchListDataContext.DBConnectionString))
{
  
if (db.DatabaseExists() == false)
   {
     db.CreateDatabase();
   }
}
2.  In your database  context, you have your table property declared.
public class WatchListDataContext : DataContext
{

   public static string DBConnectionString = "Data Source=isostore:/Database.sdf";

   public WatchListDataContext(string connectionString) : base(connectionString)
   { }

   public Table<Watch> WatchTable;

}
3.  In your entity object, do not use unsupported data type in the class property as table column. This is my actual root cause of problem. My WatchList property/table column is a List<int> type which is not a valid data type. The SqlCeException message saying the specified table does not exist is not related to incorrect declared data type for my table column here!!! For the list of supported data type, refer to this: More Detail

Wrong example. generic list is not supported data type:
[Table]
public class Watch : INotifyPropertyChanged, INotifyPropertyChanging
{
  private List<int> _watchList;
  [Column]
  public List<int> WatchList
  {
    get
    {
      return _watchList;
    }
    set
    {
      if (_watchList != value)
      {
        NotifyPropertyChanging(
"WatchList");
        _watchList = value;
        NotifyPropertyChanged("WatchList");
      }

    }
  }
}

4.  Final thing, clean your project and then rebuild! It makes a difference!!!

1 comment:

  1. Thank you so much. I have been stuck with this problem for a WEEK. I appreciate the assistance. But if I may ask, do you know why cleaning the solution solves the problem?

    ReplyDelete

Send Transactional SMS with API

This post cover how to send transactional SMS using the Alibaba Cloud Short Message Service API. Transactional SMS usually come with One Tim...