SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

There are conditions where we have our development environment on a SQL express (vs 2005) machine and we are trying to get the same code to run on a SQL Server 2005 machine. But, we cannot connect to the ASP.NET provider db even after registering a sql db in asp_net_regsql or something like that. By default, it uses whatever settings are in the machine.config file. So, that file usually has the settings that point to using a LocalSqlServer connection string, which is also defined in the machine.config as a user instance DB in the DataDirectory.

There are 2 ways to make it works:

1. Removing the LocalSqlServer connection string and then adding one to your point to your DB in web.config. The connection strings section of your web.config file should look something like this:

   <connectionStrings>
   <remove name=”LocalSqlServer”/>
   <add name=”LocalSqlServer”
   connectionString=”Data Source=Server\Instance; Integrated Security=SSPI; Initial Catalog=database;”
   providerName=”System.Data.SqlClient”/>
   </connectionStrings>

2. Provide your own <membership> section and specify the specific connection & provider type to use for membership.

   <configuration>
   <connectionStrings>
   <add name=”MySqlConnection” connectionString=”Data
   Source=MySqlServer;Initial Catalog=aspnetdb;Integrated
   Security=SSPI;” />
   </connectionStrings>
   <system.web>
   <membership defaultProvider=”SqlProvider” userIsOnlineTimeWindow=”15″>
   <providers>
   <clear />
   <add
   name=”SqlProvider”
   type=”System.Web.Security.SqlMembershipProvider”
   connectionStringName=”MySqlConnection”
   applicationName=”MyApplication”
   enablePasswordRetrieval=”false”
   enablePasswordReset=”true”
   requiresQuestionAndAnswer=”true”
   requiresUniqueEmail=”true”
   passwordFormat=”Hashed” />
   </providers>
   </membership>
   </system.web>
   </configuration>

* Thanks to Shawn Cicoria and George McKee for the solution.

I come accross some problems when adding new item to a SPList. The error i got is “Value does not fall within the expected range. ” This would only happen if you target list is empty. After do some googling, this seems is MS known bugs. Luckly there is workarround to solve this issue. Simple rules is

1. Add a new item, assign “Title” item and save it

2. Get the newly created item and update it with remaining items.

Here are the codes:

SPListItem newListItem = spList.Items.Add();

newListItem["Title"] = “The title”;

newListItem.Update();

SPListItem existingListItem = spList.GetItemByID(newListItem.ID);

existingListItem[2] = “Category 1″;

existingListItem.Update();

P/s: You only need to add new item as in this way for the empty SPList. Once u have at least one item.. the error will gone.

Please visit the original article: http://blog.buro9.com/2007/02/26/sharepoint-splistitem-quirks for more information…