Tuesday, April 30, 2013

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Incorrect syntax near ''

I have received this ‘Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Incorrect syntax near '<some Keyword>' error when I migrated my application from SQL server 2005 to SQL server 2008.

What happened is after migration, database compatibility was still set to previous version. So when we ran any SQL script which doesn’t support previous version will throw error.

To resolve this, you just need to login to the SQL server and change the compatibility level.

If you are getting this error while migrating the database from SQL 2000 to SQL 2005 then you may select the option “SQL Server 2005 (90)”

If you are getting this error while migrating the database from SQL 2005 to SQL 2008 then you may select the option “SQL Server 2008 (100)”

If you are a database admin then you can directly open the SQL query analyzer and execute below SQL query,

EXEC sp_dbcmptlevel 'DatabaseName', 90 ---This is for SQL 2005 Migration
EXEC sp_dbcmptlevel 'DatabaseName', 100 --–This is for SQL 2008 Migration

Steps to follow if you are doing it directly on server,

  • Login to the database server.
  • Locate the database you are getting this error
  • Right click on the database and select Properties
  • Go to “Options” Page
  • Here you will find the option to change the “Compatibility Level”

Below screen print will give you the clear idea of all the options and what you need to change.

Change Compatibility Level

You are always welcome to post your comments below.

Saturday, April 20, 2013

How to read PDF content using iTextSharp in .NET

How to read PDF content using .NET?” is one of the very common questions you normally found in almost all Microsoft forum. Since I have been answering this question with sample code most of the time in I thought I will write a short article with detailed explanation.

Here I am going to use iTextSharp.dll to read the PDF file. iTextSharp is a C# port of iText, and open source Java library for PDF generation and manipulation. You can download the DLL from sourceforge.net using this download iTextSharp link.

Now we will start the .NET coding part to use the iTextSharp.

As this is a sample programe I am going to add only 3 controls. One FileUpload Control to locate/browse the PDF file, one button to show the content in a label and finally a label display the PDF content.

First we will see the PDF file and it’s content we are going to read.

PDF Content To read using .NET

No we will design our .ASPX page, as I mentioned above we have only three controls.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Sample_2012_Web_App.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <asp:Label ID="Label1" runat="server" Text="Please select the PDF File"></asp:Label>
&nbsp;<asp:FileUpload ID="PDFFileUpload" runat="server" />
        <br />
        <br />
        <asp:Button ID="btnShowContent" runat="server" OnClick="btnShowContent_Click" Text="Show PDF Content" />
        <br />
        <br />
        <asp:Label ID="lblPdfContent" runat="server"></asp:Label>
    </form>
</body>
</html>

Below image shows you the interface we have created,

.NET Interface to read PDF Content

Now we will see the C# code to read the PDF content. Before start writing the code we need to add reference to the iTextSharp.dll. So from your solution explorer right click on the Reference and click on Browse button to locate the DLL file you have stored from the downloaded source code.

Once you add the reference we have to add the namespaces like below,

using iTextSharp.text.pdf;
using iTextSharp.text.pdf.parser;

Now we will see the complete source code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using iTextSharp.text.pdf;
using iTextSharp.text.pdf.parser;
using System.Text;
namespace Sample_2012_Web_App
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void btnShowContent_Click(object sender, EventArgs e)
        {
            if (PDFFileUpload.HasFile)
            {
                string strPDFFile = PDFFileUpload.FileName;
                PDFFileUpload.SaveAs(Server.MapPath(strPDFFile));
                StringBuilder strPdfContent = new StringBuilder();
                PdfReader reader = new PdfReader(Server.MapPath(strPDFFile));
                for (int i = 1; i <= reader.NumberOfPages; i++)
                {
                    ITextExtractionStrategy objExtractStrategy = new SimpleTextExtractionStrategy();
                    string strLineText = PdfTextExtractor.GetTextFromPage(reader, i, objExtractStrategy);
                    strLineText = Encoding.UTF8.GetString(ASCIIEncoding.Convert(Encoding.Default, Encoding.UTF8, Encoding.Default.GetBytes(strLineText)));
                    strPdfContent.Append(strLineText);
                    reader.Close();
                    strPdfContent.Append("<br/>");
                }
                lblPdfContent.Text = strPdfContent.ToString();
            }
        }
    }
}

Finally we will see the output.

How to read PDF Content using .NET output

As usual you are always welcome to post your comment below.