{"id":1170,"date":"2011-02-02T14:39:45","date_gmt":"2011-02-02T19:39:45","guid":{"rendered":"http:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1170"},"modified":"2011-02-02T14:39:45","modified_gmt":"2011-02-02T19:39:45","slug":"webmatrix-and-sql-server-compact-4-0-table-editing-workaround","status":"publish","type":"post","link":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1170","title":{"rendered":"WebMatrix and SQL Server Compact 4.0 Table Editing workaround"},"content":{"rendered":"

Occasionally, I encounter the following error when editing a  SQL Server Compact 4.0<\/a> database table within WebMatrix<\/a>.<\/p>\n

\n

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.<\/p>\n<\/blockquote>\n

\"image\"<\/p>\n

The issue arises when adding a new column to an existing table, and specifying that the column should not allow Nulls (Allow Nulls<\/strong> = False).<\/p>\n

The fix isn\u2019t as obvious as I\u2019d like, but it\u2019s simple. In this case, the table does not have any data, which added further confusion. I understand that if I were to try to add a column to a table that had data, and the column was set to require a value, that it doesn\u2019t make much sense (if the column is required, what value would be associated with the column for existing rows?).<\/p>\n

However, the table is empty. There is a simple work around however.<\/p>\n

    \n
  1. Set Allow Nulls <\/strong>to False<\/strong>.<\/li>\n
  2. Put in a Default Value<\/strong>, appropriate for the Data Type specified.<\/li>\n
  3. Save the table (CTRL+S)<\/li>\n
  4. Remove the Default Value<\/strong>.<\/li>\n
  5. Save the table (CTRL+S)<\/li>\n<\/ol>\n

    (I\u2019m using WebMatrix until VS 2010 includes non-beta<\/strong> support for editing SQL Server Compact 4.0 databases)<\/p>\n","protected":false},"excerpt":{"rendered":"

    Occasionally, I encounter the following error when editing a  SQL Server Compact 4.0 database table within WebMatrix. Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values. The issue arises when adding a new column to an […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[4],"tags":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pd5QIe-iS","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1597,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1597","url_meta":{"origin":1170,"position":0},"title":"Paging Data with SQL Server Compact","date":"April 1, 2012","format":false,"excerpt":"If you\u2019re using SQL Server Compact Editition version 4 or higher, there\u2019s finally a decent and efficient way to do data paging: var streets = db.Query(@\"SELECT * FROM StreetNames ORDER BY Id OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY\", new { offset = group * GroupSize, rows = GroupSize\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"image","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2012\/04\/image9.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1926,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1926","url_meta":{"origin":1170,"position":1},"title":"Using Sqlite and C# to determine if a specified table exists","date":"August 12, 2013","format":false,"excerpt":"I had need of a bit of code in C# to determine whether several tables in a Sqlite database had been created, so \u2026 public static class DbExtensions { \/\/\/

    \/\/\/ Determines whether the table exists \/\/\/ <\/summary> \/\/\/ Existing, opened, database connection<\/param> \/\/\/ The name of\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1442,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1442","url_meta":{"origin":1170,"position":2},"title":"Nest Thermostat API\/Protocol","date":"January 8, 2012","format":false,"excerpt":"While Nest Labs hasn\u2019t released a formal (documented & supported) API, I thought I\u2019d do a bit of digging to see how they\u2019re using the network and what might be achievable. A few things are going on, the majority as you\u2019d probably expect. The web interface is using a long\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"image","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2012\/01\/image_thumb7.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1705,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1705","url_meta":{"origin":1170,"position":3},"title":"WinRT\/Xaml\/AKA Metro DataTemplate selection based on Data Types","date":"August 20, 2012","format":false,"excerpt":"You may have noticed that WinRT does not have automatic resolution of a DataTemplate based on the data type of object added to an ItemsControl. While unfortunate as this behavior is quite handy, it\u2019s not too difficult to replicate the functionality using a DataTemplateSelector. WPF for example, could do something\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":568,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/568","url_meta":{"origin":1170,"position":4},"title":"Data Binding and Tooltips in Silverlight","date":"September 19, 2008","format":false,"excerpt":"Have you ever wanted to databind a tooltip in Silverlight (or WPF for that matter), and found that the DataContext isn't available for tooltips (the datacontext is null)? It's very annoying. Tooltips, unfortunately, aren't connected to their parents in anyway when they're created, so they loose the ability to connect\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":388,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/388","url_meta":{"origin":1170,"position":5},"title":"Silverlight Weather Demonstration","date":"June 26, 2008","format":false,"excerpt":"Demonstration available here. (You'll need to wait for a moment while it loads the first time). I've created a reasonably simple, yet multi-technology (and discipline) demonstration using Silverlight for the user interface and ASP.NET as the back-end. The demonstration uses: Silverlight 2.0 Data binding Delayed downloading of images \"Web services\"\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2008\/06\/image21.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1170"}],"collection":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/comments?post=1170"}],"version-history":[{"count":0,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1170\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/media?parent=1170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/categories?post=1170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/tags?post=1170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}