SharePoint: Integrate ASP.net controls with Forms – Part 5: Force Selection within Drop Down List

Pt.1: Use an SP Datasource to push values to a drop-down menu control
Pt2: Adding a DVWP Form to the page
Pt3: Update the DVWP Form using a Drop-Down
Pt4: Trimming Drop Down List results using CAML
Part 5: Force Selection within Drop Down List
Pt6: Bonus: Embed documents to page

So Close!

You may have noticed the Drop Down control has a bug. When you visit the page, an item is already selected but the EditForm isn't displayed for that item. The best I can tell is the value for the Drop Down is set before the EditForm is ready to accept a connection. Don't worry though; we can fix it with just a few tweaks to the control.
First we must select/click/highlight (pick your pleasure) the Drop Down List so we can open the Tag Properties for this control. With anything you set out to do, there is always more than one way to get it done. You can select the control and then Click Task Panes, Tag Properties; or you can simply double click on the Drop Down List.

The settings we are interested in are:

• AutoPostBack
• ToolTip
• AppendDataBoundItems
• DataSourceID
• DataTextField
• DataValueField
• Misc – Items

You may have noticed already, some of these properties are highlighted in blue. This happens when you customize a setting for this control. Remember checking the AutoPostBack option in Step 1? Since some of these properties have already been set, we don't need to worry about them. I've listed them above so you when you want to deep dive into the inner workings, you'll have a good base to start from.

Ready, Set, Config!

Let's set the ToolTip first. This gives a visual indicator to the user of the page. When you hover over the Drop Down List, a notification will appear. These are extremely useful when trying to give simple tips to your users. For this property, we are allowed to type our message directly into the box to the right.

You'll notice some properties work differently. Some only give you a True/False selection. By using these controls, you'll become more familiar with these properties and how each one works. AppendDataBoundItems is a prime example of why it's necessary to become familiar with these controls. It's difficult to decipher at first glance what this really does. From trial and error, I've found out that it actually solves our problem. Let's set it to True. By doing that, the items that we add in the next step will be appended to the Drop Down control. If that's still confusing, after you complete all of the steps and get the page working, go back and set this property to False. The last property we have to set is under the Misc heading and it's called Items. This allows us to manually add items to select from our Drop Down List. Click the ellipses on the right of Items, so we can add an item.

This brings up a rather odd looking dialog, but once you know what it's doing for you, you'll get that raise from your boss! Click Add and then configure this item to:

• Enabled:True
• Selected:True
• Text:Select a document…
• Value:Select a document…

It's important to note, the property Value plays a very important part here. Remember in Step 3, we configured the parameter to filter our EditForm when the ID equals the ID of the SPDatasource. Since we've set the Value here to: Select a document…, there's no possible way the Drop Down List will filter to an EditForm. With this in mind, it's definitely possible to *NOT* have your Drop Down bound to an SPDatasource and manually insert items with values. You could then filter based of off whatever you'd like to. Do I recommend it? No, because it's just as easy to bind this control to a list and update the values there. It'll keep you from having to dip into SPD just to add more functionality down the road.

HOORAY!

Guess what? We've done enough to merit saving the page. While we are at it, go ahead and open this page. Do you notice anything different? At this point, you should have: Select a document…, as your initial selection. With this in place, your users are now forced to make a selection and aren't stuck wondering why your Drop Down isn't working when the page is initially loaded.

If you’ve followed along from the beginning, we have just finished building a Drop Down List that’s automatically populated with Items the current user has created.  Upon selecting an Item, an EditForm appears and allows for easy manipulation of metadata.  There’s a nagging question to all of this:  What else can I do with these techniques? That's an awesome question because using this techniques; you are able to add functionality to your page that you simply can't find anywhere else in SharePoint. I'll leave it up to you to post comments on how you are using this or how it could be used. I definitely have some ideas for follow-up posts and have a project coming up that I'm going to be using this extensively. I'd still love to hear from you guys though, so don't be shy in the comments!

Technorati Tags: dvWP,moSS 2007,spDatasource,sharepoint,wSS 3.0

VBA – String Manipulation

Sometimes you just have to not accept what is given to you.  I came across something interesting building a form within Excel today.  I have 7 combo boxes on this form and I needed to assign values to all of the choices.  So when a description of 20A Receptacle was chosen, I needed to assign a value of O to it.  The end result would be a string of these variables concatenated together to form one long string that resembled: (O X G OS S X S).

A few lines of code and were off:

Devices = Array(cmb1.Value, cmb2.Value, cmb3.Value, cmb4.Value, cmb5.Value, cmb6.Value, cmb7.Value)
NumOfDevices = 0
'Find all values for combobox
For i = 0 To UBound(Devices)
If Not Devices(i) = "" Then NumOfDevices = NumOfDevices + 1
'Match Description to Ganged
Select Case True
Case Devices(i) = "20A RECEPTACLE"
Ganged = Ganged & "O "
Case Devices(i) = "20A GFI"
Ganged = Ganged & "G "
Case Devices(i) = "20A GFI PROTECT DS"
Ganged = Ganged & "4G "
Case Devices(i) = "20A HALF SWITCHED DUPLEX"
Ganged = Ganged & "OS "
Case Devices(i) = "SINGLE POLE"
Ganged = Ganged & "S "
Case Else
Ganged = Ganged & "X "
End Select
Next i

Now that we have all of our variables assigned to the description picked, a problem has arose with my approach.  Sometimes, there are empty slots within these boxes that still need to be designated by an X, but only if they are contained within the string.  For example: (O X G OS S X S).  This is valid, but my code will assign X’s to every combo box that is blank or equal to “”.  I need this to happen to accommodate for the empty slots within the box.  The challenge comes when the box has less than 7 devices.  The string could look like this: (S X G X X X X).  Thinking outside of the box, I came up with this solution:

Do While UCase(Left(Right(Trim(Ganged), 1), 1)) = "X"
GangedLength = Len(Ganged)
Ganged = Left(Right(Trim(Ganged), GangedLength), GangedLength - 1)
Loop
Ganged = "(" & Trim(Ganged) & ")"

Seems to be working just fine now.  Any of these strings that end with X get cut off until I’m left with a value that doesn’t equal X.

SharePoint: Integrate ASP.net controls with Forms – Pt4: Trimming Drop Down List results using CAML

Pt.1: Use an SP Datasource to push values to a drop-down menu control
Pt2: Adding a DVWP Form to the page
Pt3: Update the DVWP Form using a Drop-Down
Pt4: Trimming Drop Down List results using CAML
Part 5: Force Selection within Drop Down List
Pt6: Bonus: Embed documents to page

ACK!!! CODE!!!

I’ve been writing these posts by using a *real* no code approach, sort of.  I have a very different view of what no code means, but that’s a different story altogether.  However, this article I’d like to have you peer into the dark-side.  I know all too well myself, viewing code can make your eyes bug out, but I do feel it’s necessary at some point to learn.  I’ve taught myself most of the programming skills I possess today, so I know you can too.  I understand trying to learn these languages on your own can be a real burden, so if you don’t understand how it works, just try it anyway.  The worst that will happen is the solution will not work.  There’s no shame in trying and if it does work, then you can impress your boss!

DVWP & CAML

We should probably look at how these two are related.  CAML (Collaborative Application Markup Language) queries are the instructions within the DVWP or SPDatasource that controls what data is retrieved from the database.  The CAML query is hidden from you unless you know what you are looking for.  Here’s a sample line of code that contains a CAML query:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;/View&gt;" UseInternalName="True" ID="dataformwebpart2">

Within this line of code is a SelectCommand.  This command combined with the CAML: "<View></View>" will control what information is displayed to us in the browser.  I wont get into all of the details here, but I thought it would be important to show you what’s happening under the hood.

Okay CAML, I sort of get it.  Now what?

What we want to do is trim the results of the Drop Down List to only show what items have been created by the currently logged in user.  To do that, we need to update the SelectCommand for our SPDatasource with our own CAML.  What I like to do is search for the SelectCommand by clicking into the Code View window, and then clicking on Edit, Find.  Remember to click into the code view at the top of the page.
 
If you’ve followed along from Step 1, then the first SelectCommand that you’ll find is the one we’ll need to update.  The code should look similar to this:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;/View&gt;" id="spdatasource1">

<SelectParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</SelectParameters>

<DeleteParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</UpdateParameters>

<InsertParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</InsertParameters>

</SharePoint:SPDataSource>

We’ll need to only update the SelectCommand with our custom CAML query:

<View><Query><Where><Eq><FieldRef Name="Author"/><Value Type="Integer"><UserID/></Value></Eq></Where></Query></View>

The final result should look similar to this:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;Author&quot;/&gt;&lt;Value Type=&quot;Integer&quot;&gt;&lt;UserID/&gt;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" id="spdatasource1">

<SelectParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</SelectParameters>

<DeleteParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</UpdateParameters>

<InsertParameters>

<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>

</InsertParameters>

</SharePoint:SPDataSource>

Now would be a good time to save your work!  When we view the page now, all you will see in the Drop Down is the documents you’ve created.  When a different user views this page, the same will hold true.  They’ll only view the documents that they’ve created.  With this tiny piece of CAML, you can see how easy it can be to change the interaction within the page.  Don’t lock yourself into a box either, CAML will allow you to filter anything within the list.  So, if I wanted to, I could change the CAML to retrieve the only the documents I’ve created and also  if the DocCategory equals: I’ve been slacking.  Try to be as creative as you can, I think you’ll be impressed with what you can do with this.

CAML is tough to learn.  Is there an easier way?

Absolutely!  There are several ways of learning CAML.  First you *could* decipher the official documentation for CAML on MSDN or you can try out these 2 different approaches.  First up is to crack open your Document Library and create a view.  Let’s filter this library for all Docs created by [Me] AND when the DocCategory equals: I’ve been slacking.

Once you’ve created this view, you’ll notice in SPD, the view appears as an .aspx page.  If it doesn’t, refresh SPD by pressing F5 and it’ll be there.

We need to open this page and convert this Web Part into an XSLT View.  I do that by right clicking on the Web Part and selecting; you guessed it: Convert To XSLT Data View.  Once this has been converted, we can search through the code for our SelectCommand.  Yep, we just used SharePoint Designer as a CAML code generator.  Pretty cool eh?  I use this approach when I know my filters are going to be more complex than normal.  I can take it as far as I can before I have to tweak it manually.  So that’s one way to start learning CAML. 
Next up is to use the free CAML Builders that are on the internet.  These two are the best I’ve found: U2U CAML Query Builder & Stramit CAML Viewer.  These work a little differently than the first method, but you can build your code all the same.  I suggest downloading both of them and experimenting with the capabilities of both.  I’m still learning the nuances of CAML myself, so I always use these different methods to generate my code.  Once you’ve generated the code, learning the language will take you to the next level and allow you to produce some very cool results. 
You’ll also notice when you use these two tools, the code is in this format:

<View>

<Query>

<Where>

<Eq>

<FieldRef Name="Author"/>

<Value Type="Integer">

<UserID/>

</Value>

</Eq>

</Where>

</Query>

</View>

You’ll need to escape this code in order to work well with your DVWP or SPDatasource.  I’ve wrote a PowerShell script to do just that or you can use the XML Escape Tool.  Both of these will convert all of the characters that aren’t allowed in the SelectCommand into characters that are allowed.  I’ve listed them below for you:

Original Character	Escaped
‘	'
“	"
&	&
<	&lt;
>	&gt;

Note: The CAML query cannot contain any white space.  The code must also be in one continuous line. It sounds like a lot of steps, but once you get the hang of it, you’ll be flying through this stuff.   Post a comment and let me know which method you like the best.

What’s next?

This article wasn’t supposed to be this long, but in the end, I felt it was necessary.  Hopefully it was clear enough to follow.  There’s still one more tweak I’d like to show you.  With the next article, I’ll show you how to force a selection within the Drop Down List.  This will make the page a bit more user friendly, so that’s always worth the extra effort.  I promise, we’ll jump out of code view and start clicking around more: Screenshots and all…  Are you still interested?

Technorati Tags: DVWP,CAML,MOSS 2007,WSS 3.0,Sharepoint,List Views,Data View Web Part,SPDatasource

Block sites using the HOSTS file

While I know this isn’t anything new, I have been asked 3 times this week how to easily block sites.  It’s really simple.  It revolves around DNS and it’s basic principles.  There are ways to subvert this technique, but it should suffice for parents trying to lock down a certain website on a child’s laptop.  It’s is better to look at your other options though.  I’d suggest using Parental Controls for a more robust solution.

Why not show the Parental Controls?

I’d then have to have it installed…  I don’t need it!  Like I said, this is for a quick easy win.  Let’s jump into it.  Open Windows Explorer and navigate to this folder: C:\Windows\System32\drivers\etc  or for you geeks: “%windir%\System32\drivers\etc”.  Within this folder is a file called hosts.

Right click the hosts file and select Open.  This will prompt you to choose which program to use.  Simply select Notepad from the list and press OK.

This will open up the hosts file and allow you to make your changes.  If you have never seen this file before, let me explain it a bit.  This file used to be the backbone of the internet.  The domain name you’d type into your browser would check this host file and know where to go in order to find the web site.  When the internet became too large a better solution was needed, hence DNS.  What some people don’t know, is your computer checks this file first before querying any DNS server for an IP address.  So a quick edit here, will allow you to redirect all traffic to, let’s say: Facebook.com. 

Nice!  What do I type?

When you first open the hosts file, it should look just like this:

What is nice, is there are some examples on how to use the hosts file.  All of these lines are already commented by: #  So as long as that line has an #, any text will not have any effect.  What you need to do is type an IP address that isn’t capable of internet traffic.  I typically always use: 127.0.0.1.  I’ll leave it up to you to find out why I use that address.  So all we have to do is add this line:

127.0.0.1         facebook.com

As soon as you save this hosts file, all traffic bound for facebook.com, will now be routed to 127.0.0.1.  It’s just that easy…  Here’s a screenshot of what the file should look like after the edits:

Technorati Tags: hosts file,security,dns

SharePoint: Integrate ASP.net controls with Forms – Pt3: Update the DVWP Form using a Drop-Down

Pt.1: Use an SP Datasource to push values to a drop-down menu control
Pt2: Adding a DVWP Form to the page
Pt3: Update the DVWP Form using a Drop-Down
Pt4: Trimming Drop Down List results using CAML
Part 5: Force Selection within Drop Down List
Pt6: Bonus: Embed documents to page

Now that we have a good base, we can move this form along into something useful. Essentially what we are going to do is have the Drop Down List display the correct Edit Form for that document. Let’s get to it, shall we?

Create a parameter

First we need to open up the DVWP’s Common Data View Tasks, by clicking on the chevron again and then clicking on Parameters…

This will open up a confusing dialog box, if you’ve never used this before. Here’s a screenshot:

I’ve found that generally, there will always be a ListID already setup for you. Sometimes, there are others, but I haven’t seen consistency with other parameters. I leave these alone. There’s no need to disturb Mother SharePoint. She knows best! What we need to do is create a New Parameter. After clicking on New Parameter, you get an opportunity to name the parameter. I always name the parameter something I can instantly recognize. I’ve named mine: ddlSharedDocs. The prefix: ddl stands for Drop Down List and since this parameter is going to be used for the Shared Documents library, it makes sense to use SharedDocs. The next piece is to set the Parameter Source to Control. After doing that another selection appears called: Control ID.
I click the what, with the what, and then…. What does all of this actually do? If you remember, we configured the SPDatasource to display the LinkFileName (code for the column: Name) and to have a value of ID. That simply means display all of the names, but when you select a name the value equals the ID related to the document. You’ll see why this is so important in the next step.

Sweet, still no code!

If you save the page and open it, the control STILL doesn’t do anything when you select a new value… Yep, that’s right, one final step. We need to wire up the Parameter to the ID of the DVWP Form. Don’t worry; it’s not as hard as it sounds. Let’s dive into the Common Data View Tasks dialog again and click on Filter. Now all we have to do is select ID and make it Equal to ddlSharedDocs. Here’s a screenshot of what it should look like:

Press OK and now let’s see what this page does. Now within this page, we have a Drop Down List that’s populated from an SPDatasource. This data source is pulling the Names of all of the documents within the Shared Documents Library. When you click on a name within the Drop Down List, the DVWP Form for that document appears because it’s filtered by ID. Now we can easily modify metadata that relates to these documents. Here’s a screenshot of what it looks like:

Sure there’s datasheet view for bulk updating records, but I’m showing a simple example of what is possible. There are many, many cool things you can do with parameters. This only covers a very small portion. Here’s a great link that gives ideas on how you *could* use them: http://www.sharepointnutsandbolts.com/2008/07/spdatasource-every-developer-friend.html. It’s ok if this look like Japanese to you. I’m still learning new tricks from this page myself and refer to it all the time. When you start using Parameters, Controls, and SPDatasources, it’s really opening up a whole new set of tools; “Middle-Tier Power Tools”. I invite you to learn more about how to use them. We can trade notes…

But wait, there’s more?

Of course there is, there’s always something more you *could* do… Within these articles, we’ve not touched code one bit. Now it’s time to drizzle a few tiny tweaks into the mix. Don’t be shy, not only will you impress your users, you may impress your boss enough to get a raise. That’s a true story by the way. In the next article, I’ll show you how to trim the Drop Down List to only show documents created by the user viewing the page. I’ll also show you how to configure the Drop Down List to force a selection. As always, post a comment if you’d like for me to explore something we *could* do.

Technorati Tags: DVWP,MOSS,SharePoint,WSS 3.0,SharePoint Designer,ASP.net

PowerShell: Backup Script

I hooked up a quick script to backup important items from a PC.  It’s is a quick and dirty solution that I may revisit sometime in the future with enhancements. 

  1: # http://geekswithblogs.net/Lance/archive/2009/12/29/program-files-environment-variable-in-powershell.aspx
  2: # http://itknowledgeexchange.techtarget.com/powershell/deleting-files-older-than-a-certain-date/
  3: 
  4: $Today = (Get-Date -format "yyyy-MM-dd")
  5: $targetFolder = "\\BackupServer\Share"
  6: $backupDir = "$targetFolder\$env:username\$Today"
  7: 
  8: function Remove-Items
  9: { 
 10:     $testFolderPath = $targetFolder
 11: 
 12:     if (Test-Path -Path $testFolderPath)
 13:     { 
 14:         $fiveDaysAgo = (Get-Date).AddDays(-5)        
 15:         Get-ChildItem -Path $testFolderPath -Recurse | Where-Object { $_.CreationTime -lt $fiveDaysAgo } | Remove-Item -recurse -force
 16:     }
 17:     else 
 18:     {
 19:         Write-Host "$testFolderPath does not exist."
 20:         #Kill Script
 21:         Exit
 22:     }
 23: }
 24: 
 25: function is64bit()
 26: {
 27:   return ([IntPtr]::Size -eq 8)
 28: }
 29: 
 30: function Get-ProgramFilesDir()
 31: {
 32:   if (is64bit -eq $true) {
 33:     (Get-Item "Env:ProgramFiles(x86)").Value
 34:   }
 35:   else {
 36:     (Get-Item "Env:ProgramFiles").Value
 37:   }
 38: }
 39: 
 40: function Create-Folders 
 41: { 
 42:     if (Test-Path -Path $backupDir) 
 43:     { 
 44:         #do nothing. Folders have been setup already
 45:     }
 46:     else 
 47:     {
 48:         md "$backupDir" -force
 49:     }
 50: }
 51: 
 52: # Remove Folders older than $fivedaysago
 53: Remove-Items
 54: 
 55: # Kills Outlook in preparation for backup
 56: if((get-process "OUTLOOK" -ea SilentlyContinue) -eq $Null){ }else{ Stop-Process -processname Outlook }
 57: 
 58: # Start backup operation
 59: Create-Folders
 60: 
 61: #Robocopy /MIR /Z /COPYALL /MT:20 /R:5 /W:2 /NP "$env:appdata\Microsoft\Outlook" "$backupDir\OutlookAppdata" /LOG+:"$backupDir\backup.log"
 62: #Robocopy /MIR /Z /COPYALL /MT:20 /R:5 /W:2 /NP "$env:userprofile\AppData\Local\Microsoft\Outlook" "$backupDir\Outlook" /LOG+:"$backupDir\backup.log"
 63: #Robocopy /MIR /Z /COPYALL /MT:20 /R:5 /W:2 /NP "$env:userprofile\Documents" "$backupDir\Docs" /LOG+:"$backupDir\backup.log"
 64: #Robocopy /MIR /Z /COPYALL /MT:20 /R:5 /W:2 /NP "$env:userprofile\Favorites" "$backupDir\Favorites" /LOG+:"$backupDir\backup.log"
 65: #Robocopy /MIR /Z /COPYALL /MT:20 /R:5 /W:2 /NP "$env:userprofile\Desktop" "$backupDir\Desktop" /LOG+:"$backupDir\backup.log"
 66: 
 67: #Start Outlook
 68: $programfilespath = Get-ProgramFilesDir
 69: 
 70: if (Test-Path -Path "$programfilespath\Microsoft Office\Office12\Outlook.exe") 
 71: {
 72:     Start-Process "$programfilespath\Microsoft Office\Office12\Outlook.exe"
 73: }
 74: else
 75: {
 76:     Start-Process "$programfilespath\Microsoft Office\Office11\Outlook.exe"
 77: }

Please note: This script is designed for Vista and above.  It uses Robocopy to backup.  You can get Robocopy on XP by installing the Resource Kit Tools.  I’ve only tested this on Vista and above though.  I’m also using version 2 of PowerShell.