401 Unauthorized Request workaround when using HttpWebRequest

If you ever encountered the dreaded “401 unauthorized” error when trying to get the HTML code from a URL on the same domain due to the user from the application pool being different from the user logged in (windows authentication), this short snippet will show you how to work around this issue:

System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
                request.Credentials = System.Net.CredentialCache.DefaultCredentials;
                request.AuthenticationLevel = System.Net.Security.AuthenticationLevel.MutualAuthRequested;
                System.Net.HttpWebResponse response  = (System.Net.HttpWebResponse)request.GetResponse();

                if (response.StatusCode == System.Net.HttpStatusCode.OK)
                {
                    System.IO.Stream receiveStream = response.GetResponseStream();
                    System.IO.StreamReader readStream = null;

                    if (response.CharacterSet == null)
                    {
                        readStream = new System.IO.StreamReader(receiveStream);
                    }
                    else
                    {
                        readStream = new System.IO.StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));
                    }

                    string data = readStream.ReadToEnd();
                }

"The file you are trying to open, '[filename]', is in a different format" Excel Error

On a recent project, the client asked me to allow some reports to be exported to Excel (XLS.) I’ve had such a request 1000 times over the years and quickly went to work rendering the grid as HTML, sending its HTML as a response and setting the Response.Header to the Excel MIME type… Simple.

Or so I thought. Turns out that Office 2007 doesn’t like that much. When you open such a spreadsheet in Excel 2007 you get an error like:

“The file you are trying to open, ‘[filename]’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
(Yes | No | Help)

After searching for hours, I finally ran into this document from MS that essentially says this is a “feature” of the new Excel and no matter how much everyone hates it, they won’t fix it. Here’s an excerpt from that document:

“The current design does not allow you to open HTML content from a web site in Excel… So ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert… If you use an HTML MIME type, then the web browser will open the content instead of Excel. So there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific. You can add your own MIME type if you control both the web server and the client desktops that need access to it, but otherwise the best option is to use a different file format or alert your users of the warning and tell them to select Yes to the dialog.” [Emphasis added]

In other words, give up because there’s no good solution. Here’s some bad solutions you could try, though:

  • Display a message that says something like, “If you are using Office 2007, please select “Yes” from the resulting dialog.”
  • Include a registry script that the user can optionally run to change their HKCUSoftwareMicrosoftOffice12.0ExcelSecurityExtensionHardening DWord to 0, disabling this useless prompt
  • Only export to CSV, not Excel. Sure, it’s not as pretty but it’ll work.
  • Instead of using this much simpler spreadsheet generation method, instead open a template spreadsheet on the server as a data-source, write to it (using SQL), and save it with a unique file name for the user to download. Of course you’ll then have to do stuff like ensuring your saved files have unique file names (perhaps using GUID) and deleting the old spreadsheets from the File System.

How to insert data into an Excel File using VB.NET

excel-logo

Declaring the connection

If you are using Excel 2007 and above, create the file where you would like to save your data and add the following to your web.config

<appSettings>
<add value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:File.xlsx;Extended Properties='Excel 12.0;ReadOnly=False;HDR=YES;'" key="ExcelLink" />
</appSettings>

Read from an Excel file

First, we initialize the connection to the Excel File:

'connection details
Dim _connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ExcelLink")
'get last value of the id in excel file
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim strErrorsFound As New StringBuilder
Dim blnErrorFound As Boolean = False
Using connection As DbConnection = factory.CreateConnection()
connection.ConnectionString = _connectionString

Then, we create the command that will select information from the sheet:

Using command As DbCommand = connection.CreateCommand()
'Get the RefNo for the latest entry
command.CommandText = "SELECT TOP 1 ID FROM [Feedback$] ORDER BY ID DESC"

We will put our code in a Try-Catch block to prevent any errors bleeding into the Client-Side.

Try
connection.Open()

Using dr As DbDataReader = command.ExecuteReader()
If dr.HasRows Then

While dr.Read()
‘store last ref_number value
lastID = If(dr(“ID”).ToString() <> “”, Int32.Parse(dr(“ID”).ToString()), 0)
End While
End If
End Using

‘clean up- close first connection to the excel file
connection.Dispose()
connection.Close()

Catch ex As Exception
PanelServerError.Visible = True
Exit Sub
End Try

Don’t forget to close the Using Tags

End Using
End Using

Get data from SQL Server and save it into Excel

Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CN").ConnectionString)

Dim myCommand As New SqlCommand("[Your SP]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Try

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader()

While myReader.Read()

IDNum = myReader.Item("ID")
contactName = myReader.Item("ContactName")
contactCompany = myReader.Item("ContactCompany")
contactPosition = myReader.Item("ContactPosition")
contactEmail = myReader.Item("ContactEmail")
project = myReader.Item("Project")

'insert into excel file
Dim factory2 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

Dim connection2 As DbConnection = factory2.CreateConnection()
connection2.ConnectionString = _connectionString

Dim command2 As DbCommand = connection2.CreateCommand()

'Build SQL string
Dim SQLQuery As New StringBuilder()
SQLQuery.Append("INSERT INTO [Feedback$] ")
SQLQuery.Append("(ID, Name, Company, Position_, Email, Project, ")

SQLQuery.Append("VALUES ('" & IDNum & "', '" & contactName & "','" & contactCompany & "','" & contactPosition & "','" & contactEmail & "','" & project & "','")

Try
connection2.Open()

'insert data into excel spreadsheet
command2.CommandText = SQLQuery.ToString()
command2.ExecuteNonQuery()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Line / ID " + IDNum)
strErrorsFound.AppendLine(ex.Message)
strErrorsFound.AppendLine("Query: " + SQLQuery.ToString())
strErrorsFound.AppendLine("******************************************************************************")
Finally
connection2.Dispose()
connection2.Close()
End Try

'****************excel insert code *******************

End While

myReader.Close()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Generic error: " + ex.Message)
strErrorsFound.AppendLine("******************************************************************************")

Finally

'clean up
myConnection.Close()
myConnection.Dispose()
End Try

Errors

Operation must use an updateable query.

Resolution

Granting access to the file being written to (IISUSR User or the IIS App Pool User) solved this issue for me.

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Resolution

While importing from an Excel spread sheet you see the following error message “[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data”. This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet..
Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….
A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let’s say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let’s say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.
A column in a spreadsheet has numeric strings for the first 8 records, For example “123”, “456” etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example “Hello World”. The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing “hello world” is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.
In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.

Generate Word documents from ASP.NET

Overview

Ever needed to generate Word documents from your ASP.NET applications? There are a couple of components which will help to generate Word documents, but using these components may have some overhead such as Component Registration, setting permissions, licenses, etc., Sometimes, it may even become difficult to understand their features and use them. Generating word document in ASP.NET with HTML is incredibly easy.

The following sample demonstrates how to place Heading and Table dynamically in the word document in ASP.NET web applications using HTML.

Requirements

Microsoft Visual Web Developer 2005+ Express Edition

Create the Web Application project

Add the new Web Site Application project (with name as Generate Word Document) and place the button (with name as btnGenerateDocument) in the Default.aspx web form as shown below.

Figure 1

Double click the highlighted code behind file from the Solution Explorer and add the following namespaces.

Listing 1

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;

Finally, paste the following code in the button click event

Listing 2

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset ="";

HttpContext.Current.Response.ContentType ="application/msword";

string strFileName = "GenerateDocument"+ ".doc";
HttpContext.Current.Response.AddHeader("Content-Disposition",
  "inline;filename=" + strFileName);

StringBuilder strHTMLContent = newStringBuilder();

strHTMLContent.Append(
  " <h1 title='Heading' align='Center'
  style='font-family: verdana; font -size: 80 % ;
 color: black'><u>Document Heading</u> <  / h1 > ".ToString());

strHTMLContent.Append("<br>".ToString()); strHTMLContent.Append(
  "<table align='Center'>".ToString());

// Row with Column headers
strHTMLContent.Append("<tr>".ToString()); strHTMLContent.Append(
  "<td style='width:100px; background:
# 99CC00'><b>Column
1 <  / b >  <  / td >".ToString());

strHTMLContent.Append("<td style='width:100px;background:
# 99CC00'><b>Column
2 <  / b >  <  / td >".ToString());

strHTMLContent.Append("<tdstyle='width:100px; background:
# 99CC00'><b>Column 3</b>
 <  / td >".ToString());strHTMLContent.Append(" <  / tr > ".ToString());

// First Row Data
strHTMLContent.Append("<tr>".ToString()); strHTMLContent.Append(
  "<td style='width:100px'>a</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>b</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>c</td>" .ToString());strHTMLContent.Append("</tr>"
  .ToString());

// Second Row Data
strHTMLContent.Append("<tr>".ToString()); strHTMLContent.Append(
  "<td style='width:100px'>d</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>e</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>f</td>" .ToString()); strHTMLContent.Append("</tr>"
  .ToString());

strHTMLContent.Append("</table>".ToString());

strHTMLContent.Append("<br><br>".ToString()); strHTMLContent.Append(
  "<p align='Center'> Note : This is adynamically generated 
  word document  </p> ".ToString());
HttpContext.Current.Response.Write(strHTMLContent);
HttpContext.Current.Response.End();
HttpContext.Current.Response.Flush();

In the above listing, first the Response object is cleared,charset is set to empty and then Content type is set to MS Word.

Filename is defined for the document which will be generated through this code and this is set to the “Content-Disposition” header of the Response object.

Now to the StringBuilder variable — it is an HTML representation of the heading and table with three columns and two rows along with column headers and data appended. This content will be displayed in the
Word document when it is generated.

Once the contents to be displayed in the document are set, the Response object’s Write, End and Flush methods send the output to the browser, where the user’s copy of Microsoft Word will open the content and render it as a Word document.

Run the Web Application

Once the application is run, the following screen is displayed in the browser.

Figure 2

Click the Generate Document button in the above screen upon which the following prompt will be displayed to open or save the document.

Figure 3

Now save the document to some location in the system and then open it to see the contents in the document. Document contents will be as follows:

Figure 4

Downloads

[Download Sample]

Conclusion

In this article, we have seen how easy it is to dynamically generate Word reports in ASP.NET using HTML.

How to search a website with Microsoft Indexing services

Providing search capabilities requires two steps:

  • First, you must create an index of the site’s contents. An index of a website is synonymous to the index in a book. If you want to read about a particular topic in the book you can quickly find the page(s) through the index, as opposed to having to read through the book’s entire contents.
  • Once an index has been created you need to be able to search through the index. Essentially, a user will enter a search string and you’ll need to find the matching, relevant results in the index, displaying them to the user.

Unfortunately, building a search engine for your site is not as straightforward and simple as we’d like. Writing your own indexer, indexing the content, and building code to search the index is definitely possible, but requires a good deal of work. Fortunately there exist a number of indexers out there that you can leverage for your site. Some indexers include commercial products like EasySearchASP.NET and Building31.Search, which are products designed to specifically search an ASP.NET website. Additionally, Microsoft provides its own indexer, Microsoft Index Services.

This article examines using Microsoft Index Services for your site’s search functionality.
With Index Services you can specify a specific group of documents or HTML pages to be indexed, and then create an ASP.NET page that can query this index.

We’ll build a simple, fast, and extensible search tool using .NET and Microsoft Indexing Services along with the Adobe IFilter plug-in, which allows MS Indexing Services to index PDF documents and display them in your search results.

Configuring Microsoft Indexing Services

The first step in creating an index for your search application is to configure Indexing Services on the IIS server that your Web application will be running. To do this you need access to the Web server itself. Open the Microsoft management console by clicking Start, then Run; type mmc and click Ok. Next, to open the Indexing Services snap-in, you must:

  • Click file,
  • Click Add/Remove Snap-In,
  • Click Add,
  • Select the Indexing Service Snap-In,
  • Click Add,
  • Click Finish,
  • Close the dialog

After following these steps you should see something akin to the screenshot below.

To create a new catalog – which is the vernacular Microsoft uses for an index – right-click on the Indexing Service node, click New and then Catalog. You then need to choose a location to store the catalog file. Once you’ve done that, expand the catalog that you just created and click on the directories icon. Right-click on the directories folder, click new directory, and add the directory or directories that contain the content that you want to search. These directories can reside anywhere that the host computer can access, virtual directories and even UNC paths (Servershare) may be used. However, each directory that is indexed must either reside physically, or be included as a virtual directory, in the root of the website that you are indexing. If a directory is specified that is not in the web root via a physical folder or virtual directory, the results will be displayed in your search, but they will return broken links.


Indexing Services will effectively index HTML, Word, and, once properly configured, PDF documents. To ensure that your required directories will be indexed you should verify that the index flag is properly set on the files and folders. You can verify this setting by right clicking on any folder or file and selecting properties. Click the “Advanced button” and make sure that the “For fast searching, allow indexing services to index this folder” checkbox is checked, as shown in the screenshot to the right.

Next, you want to set the properties of this catalog so that the HTML paths can be used, and so that Indexing Services will generate abstracts for the documents as they are indexed. To do this right-click on the catalog you just created and select Properties. On the tracking tab, you’ll need to make sure that the “WWW Server:” field is set to the website that your application will be running from. This ensures that the html paths work as they should when you get to building the front-end for the search. If you want to display a short bit of each article along with your search results, then go to the Generation tab, uncheck “inherit above settings from service,” then check generate abstracts and set the number of characters you wish to have displayed in each abstract.

If you want your search to include PDF documents, then you must install the Adobe IFilter extension. You can download this free of charge from Adobe: http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611.
This plug-in is a standard windows installer and requires no additional configuration. After the plug-in has been installed, PDF documents will automatically be included in the search results as they are indexed without any user intervention or configuration required.

When you navigate to the Directories folder in the catalog that you’ve created, you may notice that there one or more directories appear in addition to the ones you added in the previous step. These are website shares added automatically by Indexing Services, they and need to be excluded from indexing if you don’t want your search to include them. To exclude these directories, you must find them in the file system via windows explorer. Next, right click the folder and choose Properties. From the dialog that appears click advanced and uncheck the box that says “For fast searching, allow index services to index this folder.” (See the screenshot above) This will exclude the folder from your search results. The configuration of indexing services is now complete.

As you can see, an index may include as little as one folder of documents or as much as an entire website or group of websites. It’s up to you to determine the breadth of the index. However, since Index Services does not crawl links like a spider, it will only catalog file system objects. Thus, the results from this search will include static files such as HTML pages, Word documents, and PDF documents, but not any dynamically generated pages. Changes made to these static documents will be picked up by Indexing Services and will very quickly be reflected in your search results.

Searching the Index

Once the index has been created, the next step is to build a search page that allows the website visitor to search through the index. To do this, you need, at minimum, a TextBox Web control for the end user to enter search terms, a Button Web control to initiate the search, and a Repeater control to display the results. The following markup shows the bare minimum markup for a simple search page:

Enter your search query:
<asp:TextBox id="txtSearch" runat="server"/>

<asp:Button
      id="btnSearch"
      runat="server"
      Text="Search"
      OnCommand="btnSearch_Click"
      CommandName="search"
/>

<hr>

<asp:Repeater id="searchResults" runat="server">
    <HeaderTemplate></HeaderTemplate>

    <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "File") %> <br>
        <%# DataBinder.Eval(Container.DataItem, "FileAbstract") %> <br>

    </ItemTemplate>

    <SeparatorTemplate><br></SeparatorTemplate>
</asp:Repeater>


This results page will display a list of results with a line for the document title followed by the abstract, which is generated by indexing services. Let’s take a look at the code-behind class.

In the code-behind page, an OleDbConnection is attached to the Indexing Services catalog that we set up earlier. Once connected, the catalog can be searched using a variety of query languages, including SQL syntax. You can read about each of the language options here: Query Languages for Indexing Services. For this example, I’m going to use the IS Query Language to perform a freetext search which allows for natural language search capabilities, but you can modify your search to use Boolean, phrase, or any of the query types that indexing services support.

To set up the connection to the indexing services catalog you need to set up a OleDB connection as follows: 


// create a connection object and command object, to connect the Index Server

System.Data.OleDb.OleDbConnection odbSearch = new System.Data.OleDb.OleDbConnection( "Provider="MSIDXS";Data Source="docSearch";");

System.Data.OleDb.OleDbCommand cmdSearch = new System.Data.OleDb.OleDbCommand();

// assign connection to command object cmdSearch
cmdSearch.Connection = odbSearch;
// Execute the query using freetext searching and sort by relevance ranking
//Query to search a free text string in the contents of the indexed documents in the catalog
string searchText = txtSearch.Text.Replace(“‘”,”””);

cmdSearch.CommandText = “select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, ‘”+ searchText +”‘) order by rank desc “;

The fields returned from querying the index include:

  • Doctitle: The title of document, which is the text between the <title> tags in an HTML document or the text in the title field of a word or PDF document.
  • Filename: The physical name of the file that the result was returned from.
  • Vpath: The virtual path of the file where the result was returned from. This is the field you use to specify an HTML link to the file.
  • Rank: The relevance of the returned result.
  • Characterization: The abstract for the document, usually the first 320 characters.

Tying it All Together

While there are a number of ways in which you can display the results from your search, a Repeater is likely the most efficient and gives you the greatest control on how your results are formatted. The sample application that is attached demonstrates how to bind the results of your search to a Repeater control. It also adds paging functionality to the results that will make the results easier to use, as shown in the screenshot below. The results can easily be modified to show paths to documents or display the rankings of each result.

Conclusion

This search tool is small, fast, and simple enough to deploy for searching individual folders of specific content in your intranet or Internet site. However, it can easily be used to search entire sites composed of thousands of documents. Due to the power of Microsoft Indexing Services, all that you will need to do is alter the scope of the Indexing Services catalog to include any folders you want indexed. Adding new documents to these folders or modifying the existing documents will automatically be picked up by Indexing Services. 

For more information about Indexing Services, be sure to read the following resources:

Windows Azure Security Whitepaper Released

After a lot of waiting, the Windows Azure Security Whitepaper has been released.

To download the latest release, check out the link below:

Windows Azure Network Security Whitepaper – FINAL

If you would like to keep your server/application secure, make sure you follow these guidelines and these Security Best Practices for Windows Azure Apps.

 

Windows Azure Security Layers
Windows Azure Security Layers