Friday, April 24, 2009

Trigger JavaScript Events When a Choice (Radio Button) SharePoint Field Type is Clicked

Brad has provided a workaround on how to use a JavaScript technique to manipulate SharePoint Form Field using SharePoint Designer 2007. The code in his blog supported the following SharePoint Field Types, but NOT Choice (Radio Buttons) field type:

  • Single Line of Text
  • Multiple Lines of Text
  • Number
  • Currency
  • Choice (dropdown)
  • Lookup (single)
  • Lookup (multiple)
  • Yes/No

Let say you have a requirement from a customer to develop a SharePoint list for storing user credentials which has the following UI mock-up:

UserCredential1

The requirement stated that when ‘Anonymous’ authentication mode radio button is selected, hide Username and Password textbox else display Username and Password textbox.

The following are steps required to add JavaScript to the SharePoint Form List page (NewForm.aspx and EditForm.aspx):

  • Open NewForm.aspx using Microsoft SharePoint Designer 2007 (i.e. http://<<sharepoint_servername:port_number/Lists/User Credential/NewForm.aspx)
  • Locate the <asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server"> tag and paste the following JavaScript just below the tag:

<script language="javascript" type="text/javascript">

    _spBodyOnLoadFunctionNames.push("selectedAuthenticationMode");

    function selectedAuthenticationMode() {
        // to get the array for radio buttons
        var myRadioButtonsArray = getTagFromIdentifierAndTitle("input", "RadioButtons", "Authentication_x0020_Mode");
        for (var x = 0; x < myRadioButtonsArray.length; x++) {
            if (myRadioButtonsArray[x].value == 'ctl00') // per-user static authentication mode
            {
                // create client event handler - onclick
                myRadioButtonsArray[x].parentElement.onclick = function() {
                    // display username and password textbox                   
                    var controlUserName = findacontrol("Username");
                    controlUserName.parentNode.parentNode.style.display = "";
                    var controlPassword = findacontrol("Password");
                    controlPassword.parentNode.parentNode.style.display = "";
                };
            }
            else // anonymous authentication mode
            {
                // create client event handler - onclick
                myRadioButtonsArray[x].parentElement.onclick = function() {
                    // unhide username and password field                   
                    var controlUserName = findacontrol("Username");
                    controlUserName.parentNode.parentNode.style.display = "none";
                    var controlPassword = findacontrol("Password");
                    controlPassword.parentNode.parentNode.style.display = "none";
                };
            }
        }
    }

    function getTagFromIdentifierAndTitle(tagName, identifier, title, option) {
        var len = identifier.length;
        var tags = document.getElementsByTagName(tagName);
        for (var i = 0; i < tags.length; i++) {
            var idString = tags[i].id;
            var nameString = tags[i].name;
            // get selected radio button value only
            if (option == "value" && tags[i].type == "radio" && (identifier == "RadioButtons" && nameString.indexOf(identifier) == nameString.length - len)) {
                var tagParentHTML = tags[i].parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.innerHTML;
                if (tagParentHTML.indexOf('FieldInternalName="' + title + '"') > -1) {
                    var radioButtons = document.getElementsByName(nameString);
                    var radioValue = "";
                    for (var x = 0; x < radioButtons.length; x++) {
                        if (radioButtons[x].checked) {
                            radioValue = radioButtons[x].parentElement.title;
                            break;
                        }
                    }
                    var o = document.createElement("INPUT");
                    o.type = "hidden";
                    o.value = radioValue;
                    return o;
                }
            }
            // get radio buttons group
            if (tags[i].type == "radio" && (identifier == "RadioButtons" && nameString.indexOf(identifier) == nameString.length - len)) {
                var tagParentHTML = tags[i].parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.innerHTML;
                if (tagParentHTML.indexOf('FieldInternalName="' + title + '"') > -1) {
                    return document.getElementsByName(nameString);
                }
            }
            // all other input or select type
            else if (tags[i].title == title && (identifier == "" || idString.indexOf(identifier) == idString.length - len)) {
                return tags[i];
            }
        }
        return null;
    }

    function findacontrol(FieldName) {
        var arr = document.getElementsByTagName("!");
        // get all comments
        for (var i = 0; i < arr.length; i++) {
            // now match the field name
            if (arr[i].innerHTML.indexOf('FieldInternalName=\"' + FieldName + '\"') > -1) {
                return arr[i];
            }
        }
    }
</script>

  • Save NewForm.aspx and preview in the Browser
  • Repeat the steps for EditForm.aspx

Instead of having to create a new custom Web Part or a XSLT List Form page for a SharePoint List, this approach provides uncomplicated customization and the best thing is the out-of-the-box functionalities of the List Form View for a SharePoint List are still intact.

Sample Screenshot

  • User chooses ‘Anonymous’ authentication mode radio button:

UserCredential2

  • User chooses ‘Per-User Static’ authentication mode radio button:

UserCredential1

Saturday, April 11, 2009

Close Current Connection to a Database Using T-SQL

If you delete a database object using MS SQL2008 Server Management Studio, you'll be prompted with options as shown below:

DeleteDatabasePopupUI

By selecting these check boxes, MS SQL2008 Server Management Studio deletes the database and ensures the backup and restore history for the subject database to be deleted from the MSDB database, as well as terminates connections to the subject database.

But, if you use T-SQL to delete a database (as shown below), sometimes you'll get error message "Cannot drop database "DATABASE_NAME" because it is currently in use. (.Net SqlClient Data Provider)".

USE [master]
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DATABASE_NAME')
BEGIN
    DROP DATABASE [DATABASE_NAME]
END
GO

This is because the selected database currently used by another database user.

So the question here is how to write T-SQL that able to delete database and break the existing connection to the database? The following T-SQL show you how to do that:

USE [master]
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N' DATABASE_NAME')
BEGIN
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N' DATABASE_NAME'
    ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE DATABASE_NAME SET SINGLE_USER
    DROP DATABASE [DATABASE_NAME]
END
GO

Note:

  • "ALTER DATABASE < DATABASE_NAME > SET SINGLE_USER" will allow current connection to finish their business and commit their work.
  • "ALTER DATABASE < DATABASE_NAME > SET SINGLE_USER WITH ROLLBACK_IMMEDIATE" option immediately disconnects all open connections and rolls back any changes from unfinished transactions.