USE [ReportServer]
GO

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO

CREATEFUNCTION [dbo].[fnParseParameter]
(
    -- Add the parameters for the function here
    @paramText varchar(1000), @paramName varchar(50)
)
RETURNS varchar(50)
AS
BEGIN


    -- Declare the return variable here
    DECLARE @paramValue varchar(50)
    declare @paramPrefix varchar(55)
    declare @lenPrefix int, @startPos int

    -- parameter name incl. equals char.
    select @paramPrefix = @paramName + '='
    select @lenPrefix = len(@paramPrefix)

    -- move cursor to end of parameter prefix
    select @startPos = charindex(@paramPrefix, @paramText) + @lenPrefix
    select @paramValue = substring(@paramText, @startPos, len(@paramText))

    -- cut off at the first '&' unless we're at end of parameter string
    if(charindex('&', @paramValue, 0) > 0)
    begin
        select @paramValue = substring(@paramValue, 0, charindex('&', @paramValue, 0))
    end
    
    RETURN @paramValue

END

1. change the day that the week started on based on a stored text value
2. select all days from the current week from today's date back to and including the starting day of the week (whatever that was configured to)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebCalendar

{
public partial class _Default
: System.Web.UI.Page

{
protectedvoid Page_Load(object sender,
EventArgs e)

{

_cal.SelectionMode = CalendarSelectionMode.DayWeek;

//
setup today's date values

DateTime dtCurrent = DateTime.Now;

DayOfWeek dowCurrent = dtCurrent.DayOfWeek;

//
setup day to start week
string strDayToStartWeek ="Thursday"; //
test text value - TODO: from database

DayOfWeek dowDayToStartWeek = (DayOfWeek)Enum.Parse(typeof(DayOfWeek),
strDayToStartWeek);

FirstDayOfWeek configLeadingDOW = (FirstDayOfWeek)Enum.Parse(typeof(FirstDayOfWeek),
strDayToStartWeek);

//
set calendar to start week on stored preference

_cal.FirstDayOfWeek = configLeadingDOW;

//
select the dates

SelectDatesForWeeklyReport(dtCurrent, dowDayToStartWeek);

}

///
<summary>
///
Select all the days from today back to the day which is nominated as the beginning
of the week
///
</summary>
///
<param name="dtCurrent">Today's date</param>
///
<param name="dowDayToStartWeek">Day which is nominated as the beginning of the
week</param>
privatevoid SelectDatesForWeeklyReport(DateTime
dtCurrent, DayOfWeek dowDayToStartWeek)

{

DateTime dtSelectedDate = dtCurrent;

bool exitLoop =true;

while (exitLoop)

{
if (dtSelectedDate.DayOfWeek
== dowDayToStartWeek)

{
//
select the start of the week

_cal.SelectedDates.Add(dtSelectedDate);

exitLoop =false;

}
else

{

_cal.SelectedDates.Add(dtSelectedDate);

}

dtSelectedDate = dtSelectedDate.AddDays(-1);

}

}

}

}

Review of incorrect answers:

Edited 19/04/2010

The use of RaiseError in SQL Server will only generate a SqlException when the severity level is > 10. For message of severity 10 or less you must subscribe to the SqlConnection's InfoMessage event. This exposes an object of type SqlInfoMessageEventArgs which holds a SqlErrorCollection on its .Errors property. This allows developers to foreach per SqlError raised and show related error information.

The selection of Data Provider to access certain databases (especially when you have 2 databases and only the choice of 1 provider) requires a bit of memorising of the matrix provided on MSDN. The interesting content is under the title "Choosing a .NET Framework Data Provider".

Connecting to Sql Server Express versions have their own peculiarities. Specifically, there is the possibility of configuring User Instances on a per user basis. The implications for connection strings are detailed on MSDN - refer to User Instance and AttachDBFileName and their possible values.