Feeds:
Posts
Comments

So today I installed MS Office 2010 on my SharePoint 2010 box (though this issue probably happens with MOSS 2007, too). Then I created various documents (Word, Excel, PowerPoint, Visio) and uploaded them to a document library as Admin. When I tried to open all but the Visio doc via the SharePoint interface, I received this error message from each program: “The file is corrupt and cannot be loaded.”

This is a classic misleading Microsoft error message.  When I opened the same documents from Explorer, they opened fine.  So what gives?

Don’t despair – the problem is not SharePoint.  It’s Office 2010, which has default security settings to inhibit the opening of any document originating from online – like your SharePoint site!  This probably happens on a .NET site, too, but I haven’t confirmed.

To resolve this, open each program, such as MS Word.  Then go to File >> Options >> Trust Center >> Trust Center Settings… >> Protected View.  Uncheck all four of the boxes there and try again.  Voila!  Problem solved.

Please note that your users may experience the same issue.

Today I tried something I thought would be simple: add a validation summary control to my .NET 3.5 screen, inside an update panel.  I soon discovered that the validation summary never appeared no matter how much tinkering with standard properties I did on either the validation summary or the range validator I was using.

The solution was adding “onblur=”Page_ClientValidate(‘All’)”" to the control I want to evaluate.  In this case, I am validating a text box for a date to be within a certain range, and this text box is the one that needs this code snippet added.

Toward the top of my screen, inside the update panel, is my validation summary.  In this case, I wanted a pop-up window instead of the summary, so I set those properties.  The only specific thing here to fix my issue is the ValidationGroup of “All”:

<asp:ValidationSummary runat=”server” ID=”valSummary” HeaderText=”Please fix the following errors:” ShowMessageBox=”true” ShowSummary=”false” ValidationGroup=”All”/>

Farther down screen is my textbox.  Note the “onblur” property and that the group is set to “All”.  Below that control is a calendar extender I happen to be using, and finally, the range validator, which is also set to the ValidationGroup=”All”:

<asp:TextBox runat=”server” ID=”txtAddressVerifiedDate” CausesValidation=”true” onblur=”Page_ClientValidate(‘All’)” ></asp:TextBox>

<cc1:CalendarExtender ID=”CalendarExtender3″ runat=”server” TargetControlID=”txtAddressVerifiedDate”></cc1:CalendarExtender>

<asp:RangeValidator ID=”rvAddressVerified” runat=”server” ControlToValidate=”txtAddressVerifiedDate” ErrorMessage=”Make sure the date entered is not more than 1 year from the current date nor less than 1/1/1993.” MinimumValue=”01-01-2009″ Text=”*” Type=”Date” Visible=”true” ValidationGroup=”All”></asp:RangeValidator> 

As a side note, I need my RangeValidator.MaximumValue to be dynamic, so I set that in the Page_Load event like this:

rvAddressVerified.MaximumValue = DateTime.Now.AddYears(1).ToShortDateString();

Also note that the RangeValidator.ErrorMessage is what will appear in the Validation Summary, while the Text property of * will appear next to the actual text box. This not only saves on screen real estate at the textbox, but prevents my carefully laid out HTML from redrawing into an ugly layout due to the large size of the error message.

Recently I needed to set focus to a button control on page load and used a simple javascript function for this:

<script type=”text/javascript”>
window.onload =document.getElementById(
{document.getElementById(‘btnLogin’).focus();}
</script

One thing I’ve discovered about SharePoint data is that it often has information appended to the beginning of it.  If the field shows someone’s name, such as “Mark Smith”, the data as retrieved from SharePoint actually reads “1;#Mark Smith”.

To get rid of this, I use a simple C# function that returns only the name in this case:

String GetStringFromListString(object sItem)
{
string sItemString= sItem.ToString();
return sItemString.Substring(sItemString.LastIndexOf(“#”) + 1);
}
 

The function is called in the usual fashion for such methods, and that is to use the function name followed by the parameter:

string sAuthor = GetStringFromListString(oTask["Author"].ToString());

This code assumes you already know how to use the SPList and SPListItem objects to find the SharePoint list you wish to manipulate. If not, I have other posts here in the SharePoint section of the blog.

Even worse, when there are multiple items in the list, it can look like this: “1;#Mark Smith;#2;#John Doe”. I haven’t needed to do more than grab the final name so far, in which case my function works, but if needing to grab each of the entries, a new function will be needed. I’ll post it if and when I ever need it myself.

Happy coding!

Like many ASP .NET developers, I’ve bound many data sources to a dropdown control over the years, using a database as a source, but I recently needed to do the same thing using a SharePoint list instead. Below is the C# code used to do so, with each line of code simply coming after the previous one.

First we need to connect to our SharePoint site using the SPSite object:

SPSite site = new SPSite(“http://ComputerName/SiteName”);

SPWeb web = site.OpenWeb(); 

Then we use the SPSiteDataQuery object to query the list using a CAML query.  In this case, the list we want is called “Customers”, so we store this in the query’s list property.

SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists =
string.Format(“<Lists><List ID=\”{0}\” /></Lists>”, web.Lists["Customers"].ID);

Now we’re going to indicate the fields we want to return. 

query.ViewFields =“<FieldRef Name=\”Title\” /><FieldRef Name=\”ID\” />”; 

Next we declare a DataTable and execute the query:

DataTable dt = web.GetSiteData(query);

Now all that’s left to do is bind the result to a droplist:

ddlCustomer.DataTextField =“Title”;
ddlCustomer.DataValueField =
“ID”;
ddlCustomer.DataSource = dt;
ddlCustomer.DataBind();

If we wanted to restrict the rowset further with criteria, we could also use the query’s query property to add an XML formatted criteria. For example:

query.Query =“<Where><Eq><FieldRef Name=’ID’ /><Value Type=’Number’>” + sID + “</Value></Eq></Where>”;

In this case, we’re passing the ID of a list item record in as a parameter, resulting in only one row being returned. This query line simply needs to be added to the query object prior to execution.  For example, to alter the code from earlier, we just drop it in between the other lines we’ve already written:

query.Lists = string.Format(“<Lists><List ID=\”{0}\” /></Lists>”, web.Lists["Customers"].ID);
query.Query = “<Where><Eq><FieldRef Name=’ID’ /><Value Type=’Number’>” + sID + “</Value></Eq></Where>”;
query.ViewFields = “<FieldRef Name=\”Title\” /><FieldRef Name=\”ID\” />”;

Happy coding!

Creating a custom error handling class for logging errors in ASP .NET can reduce the amount of code needed to log errors in each of your pages or functions. By using my custom class, I reduced the code needed in each event handler or method to a single line of code.

The class writes logs to the system EventLog, a database, a text file, or any combination of the three, depending on how it’s configured. Let’s dive in and see what’s needed to make this work.

Global.asax

I wanted this class to be usable in any web application, regardless of where that app was deployed. However, I may not always be able to write to the EventLog, or maybe I won’t have a database for an application.  I may not have file permissions to write to a file, either. Instead of commenting out code, I used boolean constants to turn on and off each logging routine. These constants are defined and set in the Global file like this:

public static boolLogErrorToDatabase = true;
public static boolLogErrorToFile = false;
public static bool LogErrorToEventLog = false;

The Custom Error Class

The following class is attached for download and license-free usage: error-custom-class-code. Since you can view it in its entirety there, I’ll only post the most relevant text here to save space.

To access the EventLog, you need to use the System.Diagnostics namespace by placing a using statement at the top of the class. We also need the System.IO one for writing a text file:

using System.Diagnostics;
using System.IO;
Next comes the actual method we’ll provide to the rest of the application. To do so, we make it public and use the void keyword to indicate the method returns nothing.

We’re also accepting 4 parameters. First is the Exception followed by the screen and event or method where the error occurred. Finally we’re saving the user’s name. These can be changed or added to in order to fit your own business needs. 

public voidLogError(Exception ex, stringsScreen, stringsEvent, stringsUsername)
{
//code discussed below
}

Logging the Error

Inside the LogError method, we’re going to provide three separate functions: one each for logging to the EventLog, the database, and to a file.  Since any combination of the three may be desired, we’re evaluating the global variables we set earlier.

Writing to the EventLog

if (Global.LogErrorToEventLog == true)

// Write error to log file.
stringsAppName = sScreen + ” :: “+ sEvent;  

EventLogoEventLog = new EventLog();  

if (!EventLog.SourceExists(sAppName))
{
EventLog.CreateEventSource(sAppName, “Ellefson Consulting Log”); }  string sMessage = ex.ToString();  

//log the entry
oEventLog.Source = sAppName;
oEventLog.WriteEntry(sMessage,
EventLogEntryType.Error);

} 

Looking at the code, we can see that we’re combining the sScreen and sEvent parameters into the sAppName variable that will be used in the EventLog.  We then use this to see if the EventSource is registered in the computer’s registry and if not, register it. This is not something you otherwise need to worry about, but this check must be done to avoid an error in the event the source is not registered.

The log we want to write to will be called “Ellefson Consulting Log”, which will be created if it doesn’t exist.  The rest is self-explantory.

When you go to Computer Management and view the Event Log, this is what you will see:

 

Error Log Image

Error Log Image

Writing to a Database

After the EventLog code, we have another block that writes to a database, which is SQL Server 2005 in this case. Here I’m using the SQLHelper file to encapsulate my data access object usage, but any data connection code will work.  In my database is a stored procedure called “uspLogError”, which accepts the same parameters as the other code here, writing the results to a table.

if (Global.LogErrorToDatabase == true)
{
intiErrorID = Convert.ToInt32(SqlHelper.ExecuteScalar(Global.ConnectionString, “uspLogError”, ex.ToString(), sScreen, sEvent, sUsername));
}

Writing to a File

Finally, the code writes to a text file. In this sample, the file is located in the C directory but you may want to put it somewhere else. The “true” option indicates we want to append to the file if it exists so we don’t lose the record of past errors. Without this the file would be overwritten. I’ve separated the parameters onto their own lines to make the text file more readable.

if (Global.LogErrorToFile == true)
{
// create a writer and open the file
TextWritertw = new StreamWriter(“C:\\ErrorLog.txt”, true); 

// write a line of text to the file
tw.WriteLine(“Error: “+ ex.ToString());
tw.WriteLine(
“Error location: “+ sScreen + sEvent);
tw.WriteLine(
“Username: “ + sUsername);
tw.WriteLine(
“Application: “ + Global.ApplicationName);

// close the stream

tw.Close();
}

Using the Class

At the top of my example .aspx page, I need a using statement to reference the custom class I’ve created. Since my application’s namespace is EllefsonConsulting and I’ve created my class in the Business Logic Layer folder I set up, my using statement looks like this:

using EllefsonConsulting.BLL;

Now I need to create an object from my class to be used throughout my .aspx page.  This is done just above the Page_Load event that should already be part of your page by default:

ErrorscError = new Errors();

The last step is to use the cError object in my try-catch block:

try
{
//some code
}
catch (Exception ex)
{
//some code to handle the error

//then log the error
cError.LogError(ex, Page.ToString(), “grdNews_SelectedIndexChanged”, Convert.ToString(Session["Username"]));
} 

Let’s look at this final line item by item.  First we are refering to the cError object and using its LogError method.  Then we are passing in all the needed parameters.  First is the Exception “ex”, so we know what went wrong.

Next is the Page title.  In this case, the page is my “news.aspx” page, which is what will be written to the log. This let’s me know where the error occured. By using the Page object we don’t have to keep changing this parameter for every method call.

Since the page isn’t enough, I also want to know what method caused the error, so I’ve copied and pasted the event handler title.  In this case, that’s grdNews_SelectedIndexChanged. This will need to be changed for each method.

Finally, I want to know which user experienced the error in case I need to follow up with them and learn exactly what they were trying to do when it ocurred. Here I am copying their name out of a session variable.

Conclusion

With code reuse being one of the big advantages of object oriented programming, having all your error logging handled by an identical routine is smart, consistent, and saves time while also giving you flexibability in how you process the logs. This class can be altered to provide other information as you see fit, but it should give you a good start on logging your errors with only one line of code per method. Happy coding!

Last year I experimented with a test SharePoint site for a company softball team and created some calculated fields, so I’m going to share some formulas as examples. We have several basic fields to make our decisions from:  

The three number fields we want to calculate are:

  • Our Score – numeric
  • Their Score – numeric
  • Forfeit? – Choice Drop-down of string (“Yes”, “No”)

The three number fields we want to calculate are:

  • Win – if Our Score > Their Score OR the other team forfeited
  • Loss – if Our Score < Their Score OR we forfeited
  • Tie – if Our Score = Their Score AND neither team forfeited

These fields are all numeric so that we can total them, so if the game is a win, Win = 1, Loss = 0, and Tie = 0.   As the softball season continues, we end up being able to total these fields to see a Win/Loss/Tie record such as 10/5/1.

Here are the actual calculations:

  • Win: =IF(OR([Our Score]>[Their Score],Forfeit?=”By Opponent”),1,0)
  • Loss: =IF(OR([Our Score]<[Their Score],Forfeit?=”By Us”),1,0)
  • Tie: =IF(AND([Our Score]=[Their Score],Forfeit?=”"),1,0)

There’s one problem with all of this. If I create a View on this list and then, under Totals, try to Sum the Win, Loss, or Tie columns to see what our overall record is, none of those three calculated fields are available.  This largely defeats the purpose of this and the non-technical solution is to convert these calculated fields back to manual data entry.

Despite this I thought I’d share the actual formulas for those looking to get started with such things.

I recently ran into an interesting problem.  Being in the United States, I wanted my SharePoint 2007 site to show time in the usual format.  For example, 7:00 PM appears as 7:00.  The alternative would be military time, so instead of 7:00, it would show 19:00.  I had set the site’s time property accordingly, and it worked everywhere just fine.

The problem came when I tried to create a new field for a calendar control, using a function.  I wanted it to concatenate the calendar’s Title and Start Date fields so that it would read “Title – 7:00 PM”.  Instead, it showed up as “Title – 19:00”. 

Here was the function at the time: =Week&”-”&GameOrder&”; “&Title&” – “&TEXT([Start Time],”m/d H:mm”)

The solution to the format problem was to change the function to this: =Week&”-”&GameOrder&”; “&Teams&” – “&TEXT([Start Time],”m/d h:mm am/pm”).

The final part of this function is the important one.  By using “m/d h:mm am/pm”, I was able to make the time appear as desired: “Title – 7:00 PM”.

The goal was to create a single field that would help distinguish between two events with identical titles on the same day within a SharePoint calendar.  To tell them apart, I would use the event times.  In this case, the events were company softball games, each with the same title but played back to back at different appointment times. This field was subsequently used by other list items in SharePoint, making the lists more user-friendly and less confusing.  Without this step, users couldn’t tell the difference between two items in a drop list that both said “Event Title”.

Recently I needed to find all users in an Organizational Unit (OU) in Active Directory (AD) and retrieve properties for each.  The goal was to see if these users also existed in the client’s database.  If so, everything was fine.  If not, I needed to log the user’s information in another database table. This was all to be done via a console application that runs nightly.  How to loop through the AD users is the main subject of this article, with examples written in C# ASP.NET.

Using Statements

The first step was to add the necessary using statements at the top:

using System;
using System.Data;
using System.Data.SqlClient;
using System.DirectoryServices; 

Prepare Database Commands

In my example, I needed two SQL Server stored procedures.  Once a SamAccountName (or SAM) was found in Active Directory, I wanted to pass that SAM into a stored procedure as a parameter and see if a matching record was found in the database.  If they were found, nothing further was needed. On the other hand, if they weren’t found in the SQL Server database, I needed to log this fact.

I used a second stored procedure to do this, passing in several other parameters, including the first and last names and company, plus the date of the findings.

These commands were set up prior to the code to manipulate AD and are traditional database manipulation commands.  The first one looks like this:

SqlConnection cn = new SqlConnection(“Server=192.168.1.20;Database=MyDatabase;User ID=MyUserID;Password=Password;Trusted_Connection=True;”);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cn.Open();
cmd.CommandText = “usp_FindUser”;

Prepare for Active Directory Manipulation

The next step was to use the DirectoryEntry and DirectorySearcher objects to manipulate Active Directory.  The DirectoryEntry line below restricts the searching to a single OU containing the employees I’m interested in.  The required properties line specifies which fields I want.  I always want the SAM, but in case the user will not be found in the database, I also want these other fields to help identify the user.  These fields, or properties, are then loaded into the DirectorySearcher in a foreach loop.  The next steop is to load the collection of results from the FindAll() method, which will retrieve every single user in the OU.

//Establish connection to Active Directory for the OU

DirectoryEntry de = new DirectoryEntry(“LDAP://domain.com/ OU=MyOU, DC=domain,DC=com”, “UserID”, “Password”);


DirectorySearcher ds = new DirectorySearcher(de);
ds.SearchScope = SearchScope.Subtree;

string[] requiredProperties = new string[] { “sn”, “company”, “samaccountname”, “givenname” };


foreach (String property in requiredProperties) ds.PropertiesToLoad.Add(property);
SearchResultCollection results = ds.FindAll(); 

Loop Through Active Directory Users

The remaining code has a number of steps.  The foreach loop goes through the result set of users, retrieving the SAM for each and passing it into a stored procedure.  If a record is found, the DataReader will have rows, in which case no further action is needed.  If the user is not found, I write the exception into SQL Server with another procedure. 

After each stored procedure is executed, the parameters must be cleared to prevent them from “doubling up” during the next iteration.  In other words, for the cmd object, on the first iteration there is one parameter called @UserID.  If the cmd.Parameters.Clear() method is not called later, then on the second iteration there will be two @UserID parameters, causing an error on execution.

You can process the rows however you need.  Just remove everything inside the first if statement and replace it with your own processing.

foreach (SearchResult result in results)
{
    if (result.Properties["samaccountname"].Count > 0 )
    {

         //Call a sproc that searches for the name in database and returns it or not
         cmd.Parameters.Add(“@UserID”, SqlDbType.VarChar, 100).Value = result.Properties["samaccountname"][0].ToString();         SqlDataReader DR = cmd.ExecuteReader();

 

         if (!DR.HasRows)
         {
              //Then the record was not found so write an exception
              cmd2.Parameters.Add(“@UserID”, SqlDbType.VarChar, 100).Value = result.Properties["samaccountname"][0].ToString();

              //For each property check whether you can retrieve data or not.  In theory the cast into sLast isn’t needed, but without it the parameter gets added despite an error and then you’ll have too many parameters indicated.
              try
              {
                  string sLast = result.Properties["givenname"][0].ToString();
                  cmd2.Parameters.Add(“@First”, SqlDbType.VarChar, 100).Value = sLast;
              }
              catch
                 { cmd2.Parameters.Add(“@First”, SqlDbType.VarChar, 100).Value = null; } 

              

 

              cmd2.Parameters.Add(“@ExceptionDate”, SqlDbType.DateTime).Value = DateTime.Today;
              cmd2.ExecuteScalar();
              cmd2.Parameters.Clear();
         }
         //otherwise the record was found so all is well and don’t do anything
         cmd.Parameters.Clear();
         DR.Close();
     }
 

cn.Close();
cn.Dispose();
de.Close();  

 

Finally, we clean up by closing out our objects and disposing of others. 

 

Happy coding!

 

When ASP.NET developers push their web application project from development to another environment, like testing or production, it’s useful to have the connection strings change automatically.  This prevents you from needing to manually change them each time you push updates.  It requires some minor coding that is very easy to do.  The basic steps are:

  1. Add each connection string to your Web.config
  2. Add a method to choose the connection string in the Global.asax

Update Your Web.config

You probably already have a database connection string in your Web.config, so all you need to do is add the second.  With two of them the section of your config would look something like this:

<connectionStrings>
<
add name=DevelopmentConn connectionString=Data Source=MyComputerName;Initial Catalog=MyDatabase;Integrated Security=True/>

<
add name=ProductionConn connectionString=Data Source=ProductionComputerName;Initial Catalog=ProductionDB;User ID=MyUserID;Password=MyPassword; providerName=System.Data.SqlClient/>
</
connectionStrings>

You can have as many of these as you need for testing, staging, development, or production.

Add to Global.asax

Now that your connection string is set, you must make some additions to your Global.asax file.  After the class declaration, add and initialize a public string that will be accessible throughout your application. It will look something like this:

public class Global : System.Web.HttpApplication
{
 public static stringConnectionString = “”;
…….
}

Now we’ll create a method to programmtically change the connection string while the application is running, regardless of what environment it is in.  For the method to work, you will need to add the following statement to your “using” section at the top of the Global.asax:

using System.Configuration;

 

///<summary>
/// Database connection string.
/// </summary>
/// <returns>Returns appropriate connection string depending on location of application.</returns>
private string GetConnectionString()
{
stringmyString = “”; 
switch (Environment.MachineName.ToUpper())
{
case “DevelopmentComputerName”:
myString =
Convert.ToString(ConfigurationManager.ConnectionStrings["DevelopmentConn"]);
break; 

default:
myString =
Convert.ToString(ConfigurationManager.ConnectionStrings["ProductionConn"]);
break;
} 
returnmyString;
}

This method reads the environment and get the machine name. Based on this, it will determine which of your database connection strings to use.  It also defaults to the production one, though you can switch this to another connection string.

To run this method, we add a single line to the Application_Start method already provided for you when you added the Global.asax to your application.  This will assign the connection string from the method to the string you created.

protected void Application_Start(object sender, EventArgs e)
{
ConnectionString = GetConnectionString();
}
 

Now when you need to use the connection string for your database processes, you can refer to it with Global.ConnectionString.  For example:

SqlConnection cn = new SqlConnection(Global.ConnectionString); 
 
Now you will never have to manually change your connection strings again.  A Word doc showing the complete Global.asax file is attached Global.asax Example for Connection StringsHappy coding!

Older Posts »

Follow

Get every new post delivered to your Inbox.