In this article we will see how we can validate or limit the length of the user input string.

One way is sitting the max length property of the textbox, but that will only validate the max no of characters that can be entered in the textbox. But what is we need to do a validation like the password should be minimum of 6 characters in length and should not exceed 15 characters.

 Is it possible, yes it is, we can use java script to validate this. The following is the script.

[code]

function checkCharLen()
{
if (form1.text1.value.length >=6 && form1.text1.value.length <=15)
          { return true; }
else
          { alert(“Should be 6 to 15 characters”); 
             form1.text1.focus(); return false;
          }
}

[/code]

We need to call this function in the OnBlur event of the textbox.  OnBlur event will fire when the focus is lost.

This article will teach you about exporting a data to excel.

In most of the application we need to export the data to excel. We need to do a lot of formatting to the data (tabular form, seeting the widht, height of the cell etc). Formattign the data using excel automation is a tedious job.

 The method which i am going to tell you now is going to be very simple. Excel can also be compared with HTML table, Excel work sheet is a matrix of rows and columns.

Generating an excel from aspx is very simple we need to format the html table with data and the change the contect type alone and print the table on the page using response.write. For changing the content type use the following code.

[code]
  Response.ClearContent()
  Response.ClearHeaders()
  Response.ContentType = “application/vnd.ms-excel”
[/code]

 Again most of us fail to format the data propoerly in a HTML table. But we all know how to work with the datagrid and also that the final rendered out put of the datagrid is HTML tags that is a table.

 So all we need is bind the dataset to the datagrid, format the datagird, then get the HTML output (render the html output) and print it. Use the following code for that.

 [code]
   Dim dg As New DataGrid
   Dim sw As New System.IO.StringWriter
   Dim htw As New System.Web.UI.HtmlTextWriter(sw)
   dg.RenderControl(htw)
   Response.Write(sw.ToString)
   Response.End()
[/code]

Is it not simple

Compare Validator

In some case we may have to do a validation based on some of the underlying values/control data, for example.,

Now we need to check whether the Password and confrim password in the new user form are the same. In these cases compare validators can be used.

Lets develop a page which has two textboxes Password and confirm password and do a validation whether the password and Re-Enter password are the same.

Create a new Web form and place two textboxes (name them as txtPassword & txtConfirmPassword) and a button as shown in the picture below.

Now add a compare validator next to the confirm password textbox box, The select the validator control and open the property window Set the following properties.

1. ErrorMessage = “Password doesn’t match”
2. ControlToValidate = “txtConfirmpassword”
3. ControlToCompare = “txtPassword”

CompareValidator Tag should belike this.

[code]

<asp:CompareValidator id=”cmpPassword” runat=”server” ErrorMessage=”Passwords doesn’t match” ControlToValidate=”txtConfirmPassword” ControlToCompare=”txtPassword”></asp:CompareValidator>

[/code]

Now let us see another example, to compare the text entered with some value. In most of the websites now we have some thing called us Picture verification, where in some random pictures are generated with some characters in it. A textbox will be given for the user to enter the characters displayed in the pic.

In this example, let us create similar one.

Design a page as shown below,

Add a text box and name it as txtString and add a compare validator, and set the following properties.

1. ErrorMessage = “Entered String does not match”
2. ControlToValidate = txtString
3. ValueToCompare = “ZKdul”

[code]

<asp:CompareValidator id=”Comparevalidator1″ runat=”server” ErrorMessage=”Entered string is not correct” ControlToValidate=”txtString” ValueToCompare=”ZKdul”></asp:CompareValidator>

[/code]

Working with validators

In any application, validating the user input is very important; it is process of the identifying what is incorrect in the user inputs. It is used to identify the errors in the user input before carrying out the business process.

Take for example; we are going to create a new email-using Yahoo. Where in the user id has to be given before submitting the form. This is one kind of validation. There are several other cases like the password should not exceed 10 characters or Date of birth entered should not exceed the current date or the age should be in the between a range say (10 to 50 or above 10 etc) or even the format in which the data are given.

Validators in ASP.NET

In this article lets discuss about the validators and its kinds in ASP.Net.

There are five different kinds of validators available in .net, they are as follows,

• Required Field validators
• Compare Validators
• Range Validators
• RegularExpression validators
• Custom Validators

Let us first see what each validators is meant for.

Required Field Validators

The name itself describes it, yes it is the validator used to check whether the user has entered/selected a value or not.

[code]

<asp:RequiredFieldValidator id="valReq" runat="server" ControlToValidate=”"

    ErrorMessage="* You must enter a value" Display="dynamic">

</asp:RequiredFieldValidator>

[/code]

Compare Validators

In some case we may have to do a validation based on some of the underlying values/control data, for example.,

Now we need to check whether the Password and Re-Enter password in the new user form are the same. In these cases compare validators can be used.

[code]

<asp:CompareValidator id="valCom" runat="server"

    ControlToValidate="textbox1" ControlToCompare="textbox2"

    Operator="Equals"

    ErrorMessage=”"

    Display="dynamic">*

</asp:CompareValidator>[/code]  

Range Validator Control

In most of the case we may have to check whether the data entered is between a specified range. For example, the age should be minimum 10 and maximum 100.

In those cases range validators can be used.

[code]

<asp:RangeValidator id="valRange" runat="server"

    ControlToValidate="age"

    MaximumValue="100"

    MinimumValue="10"

    Type="integer"

    ErrorMessage=“* The age must be between 10 and 100″ Display=“static”></asp:RangeValidator> [/code] 

Regular Expression validator

It is one of important validators available in Asp.net it is used to compare the data entered against specific format. For example date has to be entered in dd/MM/yyyy format only.

[code]

<asp:RegularExpressionValidator id="valReg" runat="server"

    ControlToValidate=""

    ValidationExpression=""

    ErrorMessage=""

    display="dynamic">

</asp:RegularExpressionValidator>

[/code]

 
Custom Validators 

In all the cases the above validators cannot be used, that is some validations cannot be done on the page, it has to come for round trips to the server validate the data in the server against some condition or some data base values and then display error messages based on that.

For example, checking whether the user name already exists or not.

<asp:CustomValidator id="valCustom" runat="server"

    ControlToValidate="textbox1"

    ClientValidationFunction="ClientValidate"

    OnServerValidate="ServerValidate"

    ErrorMessage=" " dispaly="dynamic">*

</asp:CustomValidator>

We will see in depth of these validators in our next chapters.

Now lets see what are the attributes are methods available in common

 Common Properties and Methods of validators. 

ControlToValidate –

Take a string input, name of the control to be validated.

ErrorMessage

The error message that needs to be displayed in case if the validations fails (some error in the data)

IsValid

Returns true if the control has valid date, else returns false.

Validate

Method to validate the control against the validation rule and to update the Isvalid property.

Display

There are three different ways of presenting the data or error message to the user.

• None – Validation message will not be displayed at all
• Static – Allocate space on the page to display the error message.
• Dynamic – Space will not be allocated on the page if there is no error. Space will be dynamically added only when the validation fails.

Let me take a report sample and lets go step by step in creating the report.

Create the following data structure.

1. Student Table

2. Class Table

3. ClassSubjects Table

4. StudentMarks table

5. Exam table

Now lets see how we are going to design the report card for the students. Requirements are as follows,

·        Each student report should be printed in a different page

·        Should display the students mark details in the following format

Steps to create the report based on the above requirement. 

Step1: Create the excel template as shown below.

·        Create a black Excel document

·        Delete the Sheet2 and Sheet3

·        Select the cells A2 to G2 and Merge them

·        Do the remaining as shown in the above pic.

·        Select cells from A1 to G22, Select File menu è Print Area è Set Print Area

·        Print Area will be defined.

·        We are not going to write any data from sp in this section. This is the section were the data is going to displayed. Or this is the section that will be printed.

·        The remaining cells will be used to write the required data for the report they act as the report data source.

·        Lets define areas, where we are going to dump the data.

o       Select the cells J4 to M4

o       Insert è Name è Define (to define a new area)

o       “Define Name” dialog opens and name the region as “StudentInfo”

o       Similarly the cells J7 to K7 and name the region as “Marks”

o       We will be dumping the data in the regions only from the Stored procedure.

·        Lets see how we are going to set the data in the report print section

·        Printing the Header “Progress Report of the XXXXXXX exam – YYYY(Year)”

·        This should be achieved using the macro. Exam name and the Year of the exam should be obtained from the StudentInfo region

·        Lets define the Macro, Select “Tools è Macros è Visual Basic Editor” or press “ALT + F11”

·        VBA Window will open. Select the “Sheet1” node in the project explore, Right Click and Select “View Code”

·        Code window opens

·        Select “WorkSheet” object and select the Activate event.

·        Write the following code.

Cells(2, 1) = “Progress Report of the ” & Cells(5, 13) & ” - ” & Format(“YYYY”, Cells(5, 12))

·        We need to fill the Name, Exam Date, Class

·        Lets use the formula to fill these data

·        Select the cell “C4” in the formula bar type “=J5” and the select the cell c4, set the necessary format by right clicking on the cell and formatting the cell.

·        Now lets populates the marks.

o       Select the cell “B9” and in the formula type “=J9” repeat the same for other rows till “B14” assign each row with the corresponding J cells

o       Select the cell “C9” and in the formula type “=K9” repeat the same for the other rows till C14 assign each row with the corresponding K cells

o       Select the cell “D9” and in the formula type “=L9” repeat the same for the other rows till D14 assign each row with the corresponding L cells

o       Select the cell E16  and type the formula as “=SUM(E9:F13)” which will calculate the Total.

o       To print the grade lets create a macro.

If CInt(Cells(9, 12)) < 40 Or CInt(Cells(10, 12)) < 40 Or CInt(Cells(11, 12)) < 40 Or CInt(Cells(12, 13)) < 40 Or CInt(Cells(13, 13)) < 40 Then

    Cells(17, 5) = “F”

ElseIf CInt(Cells(16, 5)) > 400 Then

Cells(17, 5) = “O”

ElseIf CInt(Cells(16, 5)) > 300 Then

Cells(17, 5) = “A”

ElseIf CInt(Cells(16, 5)) > 200 Then

Cells(17, 5) = “B”

End If

·        Now we are all set with the report template

Step3: Create a page in ASP.NET as shown below.

Step3: populate the class drop down from the class table

Step4: populate the exam drop down from the exam table.

Step5: Next step is creating excel sheets (copy of the template created in step 1) for each student in the class, Also writing an sp which will dump the data in the excel sheets. The merge the excel sheet and display it as one excel sheet using java script.

This we will see in our next chapter.

Use the following SQL Script to generate the tables.

 if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[Class]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Class]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[ClassSubject]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[ClassSubject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[Exam]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Exam]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[Student]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Student]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[StudentMarks]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[StudentMarks]
GO

CREATE TABLE [dbo].[Class] (
 [ClassID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [ClassName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ClassSubject] (
 [SubjectID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [ClassID] [bigint] NULL ,
 [SubjectName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [SubjectDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Exam] (
 [ExamID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [ExamTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ExamDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Student] (
 [StudID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ClassID] [bigint] NOT NULL ,
 [Age] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StudentMarks] (
 [MarkID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [StudID] [bigint] NOT NULL ,
 [SubjectID] [bigint] NOT NULL ,
 [Mark] [int] NOT NULL ,
 [ExamID] [bigint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Class] ADD
 CONSTRAINT [PK_Class] PRIMARY KEY  CLUSTERED
 (
  [ClassID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClassSubject] ADD
 CONSTRAINT [PK_ClassSubject] PRIMARY KEY  CLUSTERED
 (
  [SubjectID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Exam] ADD
 CONSTRAINT [PK_Exam] PRIMARY KEY  CLUSTERED
 (
  [ExamID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Student] ADD
 CONSTRAINT [PK_Student] PRIMARY KEY  CLUSTERED
 (
  [StudID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[StudentMarks] ADD
 CONSTRAINT [DF_StudentMarks_Mark] DEFAULT (0) FOR [Mark],
 CONSTRAINT [PK_StudentMarks] PRIMARY KEY  CLUSTERED
 (
  [MarkID]
 )  ON [PRIMARY]
GO

Excel Reporting

We will be discussing the following features and will see some examples on how these features can be implemented in .Net.

• Modifying the structure of the report
• Carrying out analysis or calculations on the data available
• Formatting the data.

Advantages of Excel reporting

Microsoft Excel is very friendly with almost all segments of the corporate, even the top management/decision makers are very comfortable with excel, they can play around excel.  Apart from these we have the following advantages,

• Ease of use
• Ease of modification
• Ease of formatting
• Ease of Analysis
• Easy to manage

Excel reporting .NET

The solution, which I am providing now, is not a pure .NET solution, it is a combination of SQL stored procedure, Excel Macros and Java Script. .Net is just used to tie all of these together, generate the report and present it to the End user.

Before getting into the solution, first we should understand how we can write into an excel document.

Writing into an Excel document using SQL

3.2.1.1 Regions in Excel

A data region is an area on a report containing data from a data source. Types of data regions are charts, lists, tables, and matrices.  (Refer http://msdn2.microsoft.com/en-us/library/ms155976.aspx)

We can name a set of columns in Excel, which will be acting as a table inside the Excel sheet.  We can Inserts and updates to these Named Regions.

3.2.1.2Creating a Named region

·        Open a excel sheet

·        Enter the table header

·        Select the Column header as shown above.

·        Select Insert è Name èDefine

·    A window opens as shown above

·        Name it as “employee” and click “Add”

·        Click OK

·        Save the document and close it. (Name the document as Emp.xls and store it in c:\

Writing data into the named region using the stored procedure 

From SQL we can export the data to any database or data stores using DTS packages and Export wizard. Apart from this we can also export data from SQL to any database or datasource using a Query “Insert into OpenRowSet”.

We should all realize that Excel can also store data like access or any other database and Data can be retrieved or written into the excel document.

We are going to use this statement and export the data to excel docoument.

Create the following SP in Northwind database,

Create procedure dbo.ExportEmployeeData(@filePath as varchar(500))

as

begin

            DECLARE @Expr1 VARCHAR(200),@Expr2 VARCHAR(200),@Expr3 VARCHAR(200) 

            Declare @Sql1 AS VARCHAR(800), @Sql as varchar(800)

            SET @Expr1 = ”’Microsoft.Jet.OLEDB.4.0”’

            SET @Expr2 =  ”’Excel 8.0;Database=’ + @filePath + ‘;HDR=YES”’ 

            SET @Expr3 = ”’SELECT * FROM [Employee]”’

            set @Sql1 = @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3

            set @SQL=’insert into OPENROWSET(’+ @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3 + ‘) SELECT EmployeeID, FirstName, LastName, Title from employees

            Exec(@Sql)

end

In this SQL we are taking the excel name with path as the parameter and we are using Microsoft jet OLEDB drives to write the data into the SQL. Where [Employee] in the statement

SET @Expr3 = ”’SELECT * FROM [Employee]”’

Is the region we created in the Excel Sheet.

The statement “set @SQL=’insert into OPENROWSET(’+ @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3 + ‘) SELECT EmployeeID, FirstName, LastName, Title from employees” actually fetched the records from employees table and inserts it into the Excel sheet.

Now, try executing this stored procedure using the following statement,

ExportEmployeeData ‘c:\employee.xls’

 Notes:

1. There should not any data contained in the cells where we will be trying to write the data.

For example, In our case the region is from B4 to E4. and if we are going to insert 10 rows then there should not any data in the cells from B5 to E14.

If any data exists it will throw an error.

2. The excel file should be in the SQL Server system or else the folder should be shared with write permission and file share path should be given.
3. Also note that, SP is not the only way to write into an excel file, the same logic can be implemented in our java or .net code.

Is it not simple to write the data into an excel sheet. Of course it is.

Now, that we know how the data can be written into an excel sheet, the next step is formatting the data (Presentation). This can be achieved using Excel formulas and Macros.

Introduction

Report is a type of document written/created by someone or a group of people. It can also be called as an organized collection of data, prepared for viewing or printing.

What ever application we do either it is a small billing software or a complex Payroll system for a bigger corporate, and the end of the day/quarter the top decisions maker or not going to look at our software/application we developed, they are going to look at the reports which our application generated with some click of buttons. These reports should be,

They are going to use these reports for their organization needs or for an analysis. W should realize the importance of reporting in software development.

In this article, I am going to talk about the key features which can provided in Excel reporting and how .Net applications can generate the report in Excel format.

Excel Reporting

Developer would have come across several reporting methodologies like formatting the data with tabs and spaces and printing them, or using reporting packages like Data report for VB6.0 or Crystal packages.

But the reports generated by these methods, provides very minimal features to the end users.

Lets see some scenarios,

Scenario 1:

The end user generates the report with some clicks of button, he see the report on the screen. Now we wants to show a Graph which conveys some meaning full data or he feels that if a graph is added to this report it will add more meaning to report. So what he needs to do,

• Need to check the agreement with the software vendor
• Need to raise a Change request.
• Series of call with the business analyst to explain how the graph should be generated.
• Report designer starts working on the report completes the report
• Series of testing has to be done
• Then deploy the report

Scenario 2:

The end user is going to send some report to their client or present the report in their board meeting and feels that he should not show all the data available but only a part of it. So what should we do for this case?

• We create two report or en number of reports with the same set of data hiding some columns or making some structural changes to the report

 The report is not flexible to hide some columns or generate report to their immediate requirement.

The Solution

The best solution would be developing a feature, which helps the user to create report on fly.

Report on fly

• Users can given the option, what fields needs to be display
• The type of analysis or calculations they want to do on the data available
• Add some graphical format of the data
• And many more things 

Lets see how we can achieve some of these using excel.

I came accross an interesting and a useful features in Visual Studio.Net called Clip Board Ring.

Most of developers will use the Shortcuts Ctrl + C and Ctrl + V very often to re-use some code snippet. But pasting will work only for the last item that is stored in the click board memory or the last item I copied.

Now what if we want to reuse some set of codes snippets very often. Thats waht we are going to do using the Clip board ring. Most of us would have noticed a tab in the ToolBox called “Clip Board Ring”. 

In this tab we can organise the groups of code snippets which we want to use very often. Now Activate/Select the “Clip Board Ring” tab. Initally you will not have any items listed.

Steps to Add item to the “Clip Borad Ring“.

Step 1:  Open any coding window, I mean a HTML Editor or Class Editor, select the code snippet which you want to add to the Clip Board Ring.

Step 2: Press Ctrl + C (Same old copy shortcut) I hope everyone understands whats happening behind the scenes.

Step 3: You could see a new item called “Markup Fragment” in the Clipboard ring. Thats the default label for the code snippet you have added recently. Rename it to easily identify.

Steps to User the added item.

Its very simple. Just drag and drop what ever item you want into your editor. You could see the item getting added.

Or press Ctrl + Shift + V to iterates through the text snippets contained in the Clipboard Ring. Keep Ctrl and Shift down and press V multiple times to select the snippet you want, and then release all the buttons to have the snippet pasted in the current position.

For more information on this feature…. Please do R&D yourself…. And don’t forget to let me know if you have found anything new.

Please visit