How to get Comma Separated Values (CSV) from Table Column

This is another very common requirement while developing sql code for the applications.
And again this can be done in various ways...one of them is here...

Example:

create table user_reports
(id int identity, username varchar(20),report_name varchar(20), report_available bit)
go
insert into user_reports(username,report_name,report_available)
select 'Ajay', 'report1',1
union all
select 'Ajay', 'report2',1
union all
select 'Ajay', 'report3',1
union all
select 'Ajay', 'report4',1
union all
select 'John', 'report5',0
union all
select 'John', 'report6',1
union all
select 'John', 'report2',0
union all
select 'Tony', 'report1',1
union all
select 'Tony', 'report7',1
union all
select 'Ram', 'report3',1
union all
select 'Ram', 'report4',0
union all
select 'Ram', 'report8',1


-- CSV report list for all users
select stuff(
(select ',' + ur.report_name
from user_reports ur
order by ur.report_name
for xml path('')),1,1,'') as 'Report List'
go

-- CSV report list for a given user
select stuff(
(select ',' + ur.report_name
from user_reports ur where username = 'Ajay'
order by ur.report_name
for xml path('')),1,1,'') as 'Report List'
go

-- CSV report list user wise
select ur1.username,stuff(
(select ',' + ur.report_name
from user_reports ur where ur.username=ur1.username
order by ur.report_name
for xml path('')),1,1,'') as 'Report List'
from user_reports ur1 group by ur1.username
go

How to aggregate BIT Columns with Group By in SQL Server

This is a very common requirement in sql when you are deveploping applications.
You can achive this with very simple query, using cast and min sql functions.

Example:

create table user_reports
(id int identity, username varchar(20),report_name varchar(20), report_available bit)
go
insert into user_reports(username,report_name,report_available)
select 'Ajay', 'report1',1
union all
select 'Ajay', 'report2',1
union all
select 'Ajay', 'report3',1
union all
select 'Ajay', 'report4',1
union all
select 'John', 'report5',0
union all
select 'John', 'report6',1
union all
select 'John', 'report2',0
union all
select 'Tony', 'report1',1
union all
select 'Tony', 'report7',1
union all
select 'Ram', 'report3',1
union all
select 'Ram', 'report4',0
union all
select 'Ram', 'report8',1


-- This simple sql query will return user wise aggregated report_available column value
select username as UserName, cast(min(cast(report_available as int)) as bit) as 'Is Report Available'
from user_reports group by username

SSIS Error : Failed to save package file, Element not found.

There could be many reasons that can cause this error but in my case the reason was that MSXML6.dll was not registered on my machine. So I just registered this dll to resolve this error.

To register MSXML6.dll on your machine, follow these steps:
1. From the Start menu, click Run.
2. Enter the following command: regsvr32 %windir%\system32\msxml6.dll.
3. Click OK.

Note - Replace %windir% with the path of windows folder on your machine. I my case it was - c:\windows

How to handle errors in APS.NET

There are three ways to handle errors in asp.net application.

1. Using Page class Error even:

You can handle error on every page by define error handler for Page class Error event.
Like-

void Page_Error(object sender, EventArgs e)
{
Response.Redirect("Errorpage.htm");
}

2. By defining error handler in global.asax file

This is better approach than the first one because its a centralize approach to handle errors. Here instead of writing error handler in every web page, you will need to define error handler at application level in the global.asax file.
Like:

void Application_Error(object sender, EventArgs e)
{
Response.Redirect("Errorpage.htm");
}

Note:- Whenever an error occurs application look for error handler at page level and then at application level. So if you have defined error handler in the web pages then it will handle the error not the one defined in global.asax file.

3. By adding customError tag in the web.config file

This is another centralize approach to error handling in ASP.NET. In fact you can redirect to different custom error pages for different error status codes.
Like:

< customErrors defaultRedirect="error.htm" mode="On" >
< error statusCode="404" redirect="notfound.htm"/ >
< /customErrors >

Error: possible soap version mismatch

Sometime you may encounter below mentioned error when you try to access web service in your .net application.

Possible SOAP version mismatch: Envelope namespace http://schemas.xmlsoap.org/wsdl/ was unexpected. Expecting http://schemas.xmlsoap.org/soap/envelope/.


Actually if you are setting URL property in your .net application with wrong web service url than most likely you may get this error. The general mistake is that people assign same url which they used to see the description of web service (e.g. http://localhost/servlet/webservices?ver=1.1?wsdl) to the URL property of the proxy class instead of the web service url(e.g. http://localhost/servlet/webservices?ver=1.1). So assign the web service url and see if you are able to resolve this error.
If still you are not able to resolve this error then make sure the url you are assigning is correct.

Note: - I have noticed that in case web service is developed in .Net and is consumed by a .Net client then assigning description url (e.g. http://localhost/webservices/service.asmx?wsdl) does not generate this error. But if the web service is developed with non .net technology and generates WSDL that is somewhat different from what .Net clients expect then this error will most likely come up in that case try above given solution.

Error: A semi colon character was expected. Error processing resource

Very often people encounter this error when they try to open xml file in IE or try to load it in their code. The most common reason for this error is that your xml file may be containing "&" character. Actually IE misunderstands "&" character with some HTML code that starts with "&" and ends with ";" character like-& nbsp ; means non-breaking space and rendered as a white space.

For Example: here is the content of an xml file which will give this error due to "&" character in the url.

< ?xml version="1.0" encoding="utf-8" ? >
<>
<>
< name="WSACCPURL">
<>http://localhost/Test/webservices?ver=1.1&wsdlxml< /value >
< /setting >
< /appSettings >
< /configuration >

So to resolve this error I replaced the "&" character with HTML code phrase "& amp ;"
Note:- Ignore the space between & and ; there should not be any space. Here I had to give space to avoid their special meaning by IE.

Now the updated xml file is:

< ?xml version="1.0" encoding="utf-8" ? >
<>
<>
< name="WSACCPURL">
<>http://localhost/Test/webservices?ver=1.1& amp ;wsdlxml< /value >
< /setting >
< /appSettings >
< /configuration >


Now when this file is opened in IE the & amp ; is rendered as & character.

Consuming web service in classic ASP application using .Net component

It’s fairly simple to consume web service in your classic ASP application using wrapper component developed in .net world.

So first create a wrapper component that will consume the web service.
Follow below given steps:

1. Open visual studio and create a Class Library project in C#.
2. Name the project WebServiceWrapper and the class as Wrapper.
3. Paste below code in the Wrapper.cs class window.

-----------------------------------------
using System;

namespace WebServiceWrapper
{

public class Wrapper
{

MyWebService.Service1 svr;

public Wrapper()
{
}

public void Initialize(String url)
{
svr=new WebServiceWrapper.MyWebService.Service1();
svr.Url=url;
}

public String URL
{
get{return svr.Url;}
set{svr.Url=value;}
}

public String HelloWorld(String str)
{
return svr.HelloWorld(str);
}

public String HelloWorld(String str, String url)
{
svr=new WebServiceWrapper.MyWebService.Service1();
svr.Url=url;
return svr.HelloWorld(str);
}

}
}
-----------------------------------------

4. Right click on the project and choose Add Web Reference option.
5. In the Add Web Reference window browse your web service and give the web reference name: MyWebService and click on Add Reference button.

Note:- In the above code web service in question(Service1) is hosted on local machine and has a method named HelloWorld that takes string param and return string value but you have to replace this code with your web service name and web method accordingly.

6. Build the solution that will create WebServiceWrapper.dll
7. Now you have to create type library of your .net component and register that to make it available in your VB COM component. For that use following command at VS .Net command prompt window.

regasm.exe < path of WebServiceWrapper.dll > /tlb /codebase

It will create a type library named WebServiceWrapper.tlb at the dll location and register the same as well. Ignore any warning message shown.

You are done with creating your .NET wrapper component. Now time to use it in your VB COM component.
Here I assume that you would have gone through my previous post "How to create ASP application using ASP Object Library" becasue here I will tell you what changes are needed to do in your ASPComp component that you would have created earlier.

So Open your ASPComp project and add following code in the clsASP class.
-----------------------------------------
Public Sub CallWrapper_HelloWorld()

Dim oclient
Dim strg As String
On Error GoTo ErrorHandler
Set oclient = CreateObject("WebServiceWrapper.Wrapper")
oclient.Initialize "http://localhost/WebService1/Service1.asmx?wsdl"

strg = oclient.HelloWorld("Sanjay")
oResponse.Write strg & vbCrLf

Exit Sub

ErrorHandler:

oResponse.Write "Invalid Web Service Request. Please check the Web Service URL: " & Err.Description

End Sub
----------------------------------------
Note:- Replace the web service url with your web servce url.

Now go to Project-> reference menu and add reference of WebServiceWrapper Type Library that you had created earlier by browsing to its location.

Now re-make ASPComp.dll to incorporate new changes in the COM component.

Note:- You will have to re-register the ASPComp.dll

After completing the COM component changes, open your asp page in the notepad and replace the method call with the newly created method.

So your MyASP.asp page will look like:

< %
Set oExample = Server.CreateObject("ASPComp.clsASP")
oExample.CallWrapper_HelloWorld
% >

No time to test user efforts. So browse http://localhost/ASPTest/MyASP.asp in the IE window.

How to consume web service in classic ASP application using Soap 3.0

It's fairly simple to consume web service using Microsoft Soap Type Library 3.0 in your classic ASP application.
Here I assume that you would have gone through my previous post "How to create ASP application using ASP Object Library" becasue here I will tell you what changes are needed to do in your ASPComp component that you would have created earlier.

So Open your ASPComp project and add following code in the clsASP class.
-------------------------------------
Public Sub Call_HelloWorld()

Dim oclient
Dim strg As String
On Error GoTo ErrorHandler
Set oclient = CreateObject("MSSOAP.SoapClient30")
oclient.MSSoapInit "http://localhost/WebService1/Service1.asmx?wsdl"

strg = oclient.HelloWorld("Sanjay")
oResponse.Write strg & vbCrLf

Exit Sub

ErrorHandler:

oResponse.Write "Invalid Web Service Request. Please check the Web Service URL: " & Err.Description

End Sub
--------------------------------------
Note:- In the above code web service in question is hosted on local machine and has a method named HelloWorld that takes string param and return string value but you have to replace this code with your web service and web method.

Now go to Project-> reference menu and add reference of Microsoft Soap Type Library 3.0

Generally it is installed with Office 2003 but if its not installed on your machine then you can download it from here and install it on your machine.

Now you will have to re-make ASPComp.dll

Note:- You will have to re-register the ASPComp.dll

After completing the component changes, open your asp page in the notepad and replace the method call with the newly created method.

So your MyASP.asp page will look like:

< %
Set oExample = Server.CreateObject("ASPComp.clsASP")
oExample.Call_HelloWorld
% >

You are done with the changes. So browse http://localhost/ASPTest/MyASP.asp in the IE window.

Reference: http://www.aspfree.com/c/a/VB.NET/Calling-a-Web-Service-using-VB6-with-SOAP-30/1/

How to create ASP application using ASP Object Library

Microsoft ASP Object Library provides five ASP Intrinsic Objects i.e. Request, Response, Server, Session, and Application that can be used in the COM component to develop re-usable code components that can be used in other ASP applications.

Here I am going to show you how you can create ASP application that uses COM component developed in VB that is using ASP object library.

Below are the steps to create very-very simple ASP application.

1. Open the MS Visual Basic 6.0 and create ActiveX DLL project.
2. Rename the project1 as ASPComp and class1 as clsASP.
3. Copy below code in the clsASP class window.

Private oContext As ObjectContext
Private oResponse As Response

Implements ObjectControl

Private Sub ObjectControl_Activate()
Set oContext = GetObjectContext()
Set oResponse = oContext("Response")
End Sub

Private Sub ObjectControl_Deactivate()
Set oContext = Nothing
Set oResponse = Nothing
End Sub

Private Function ObjectControl_CanBePooled() As Boolean
ObjectControl_CanBePooled = False
End Function

Public Sub HelloWorld()
oResponse.Write "Hello World!
" & vbCrLf
End Sub

4. Now open project-> references window from menu and add reference of following items.
Microsoft Active Server Pages Object Library.
COM+ 1.0 Admin Type Library
COM+ Services Type Library


5. Now go to file menu and make the ASPComp.dll

You are done with creating COM component and now you have to create an ASP page that will comsume this component.

So create a folder named ASPTest in the in wwwroot folder and then create an asp page named MyASP.asp in it.

Open this page in notepad and copy following code in it and save it.

< %
Set oExample = Server.CreateObject("ASPComp.clsASP")
oExample.HelloWorld
% >

Now one last thing to be done is to register the component in the COM+ services. You can do it by setting the class's MTSTransactionMode property = 1 - NoTransaction or through steps given below:

1. Open the Component Services Window and right click on the COM+ Applications
2. From the context menu select new->application
3. A Wizard will open, click on next button.
4. Choose Create Empty application and enter the application name - ASPComp, leave other settings as is and click on next button.
5. Again leave the settings as is and click on next button.
6. Click on Finish button.
7. Now you can see ASPComp in the list of COM+ Applications. Double click on it.
8. Now right click on component and choose new->component
9. A wizard will open, click on the next button.
10. Choose the install new component and browse and select the ASPComp.dll and click on open button.
11. Click on Finish button.
12. Now right click on ASPComp and click on start option.

You are done with the development now time to test it.

Open the IE and type following address in the address bar.

http://localhost/ASPTest/MyASP.asp

You should see - "Hello World" on the page.


Reference: http://www.stardeveloper.com/articles/display.html?article=2000041401&page=1

ASP.NET Coding Model- Inline Code, Code-Behind & Code-Beside

Inline Code:
It’s an old style of coding model, where your business logic code appears in
< script runat=”server”> < /script > block in the .aspx page itself.

Example:

< %@ Page Language=”C#” % >
< script runat=”server”>
void Page_Load(object sender, eventArgs e)
{
Label1.Text=”Hello World”;
}
< /script>
< html >
< body >
< form id=”form1” runat=”server” >
< asp:Label ID=”Label1” Runat=”server” / >
< /form >
< /body >
< /html >

When first time this page loads,it is compiled into a class that resides in an assembly stored in a subfolder of the “C:\WINDOWS\Microsoft.NET\Framework\v2.x.xxxx\Temporary AST.NET files” folder. This compiled version of the page is good until the .aspx file changes or the application is restarted, at that time it will have to be recompiled.
Note- By default web page inherits from the System.Web.UI.Page class, so if you want your page inherits from some other class (which in turn must inherit from Page class), add “inherits” attribute to the Page directive.

Code-Behind: Non-Compiled

This coding style was used by the people, who wanted to change code on-the-fly without having to build their projects, or who wanted to separate code from HTML but didn’t have Visual Studio tool.

Example:

default.aspx file:

< %@ Page Language=”C#” Src=”default.aspx.cs” Inherits=”CodeBehindClass” % >
< html >
< body >
< form id=”form1” runat=”server” >
< asp:Label ID=”Label1” Runat=”server” / >
< /form >
< /body >
< /html >

Default.aspx.cs file:

using System;
using System.Web;
public class CodeBehindClass : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
public void Page_Load(object sender, eventArgs e)
{
Label1.Text=”Hello World”;
}
}

In the above cod, the .aspx page looks just like the inline page, except the code has been moved to the code-behind file and two more attributes has been added to the Page directive. The Src attribute points to the physical file where the code behind class is located. The Inherits attributes tells us which class in that code-behind file the page should inherit because there technically could be any number of classed in that file.
The code-behind class must declare all the controls on the page as protected member of the class.
When the first time this page is requested, it will be compiled and cached only as the inline code version, using the code-behind file as the source for its base class. It will remain cached until the .aspx or code-behind file has been changed or the application is restated.

Code-Behind: Compiled

This coding style is same as the previous one for the coding point of view except in the Page directive “Src” attribute is replaced with “Codebehind”.
e.g.-
< %@ Page Language=”C#” Codebehind=”default.aspx.cs” Inherits=”CodeBehindClass” % >

Rest of the code will be same as shown in the Code-Behind Non-Compiled coding style.
By the way this coding style was used only by Visual Studio 2002 and 2003. Codebehind attribute was used to let the designer know where the code resides. Because no Src was specified, the class indicated in the Inherits attribute had to be compiled in an assembly located in the /bin folder. Actually when the project was built, all the .cs (or .vb) files in the project were compiled into one assembly. When the first time page was requested, it would be compiled in much the same way as the previous method, except that it would inherit from a class compiled in an assembly found in the /bin folder.
This style is obsolete and not supported in Visual Studio 2005 as it required constant synchronization between the page and code files that would generate lots of code. It also required a build every time page is tested.

Code-Beside:

This coding style uses the concept of partial class to separate the code files from the .aspx page.

Example:

default.aspx file:

< %@ Page Language=”C#” Codefile=”default.aspx.cs” Inherits=”Default_aspx” % >
< html >
< body >
< form id=”form1” runat=”server” >
< asp:Label ID=”Label1” Runat=”server” / >
< /form >
< /body >
< /html >

Default.aspx.cs file:

using System;
using System.Web;
public class Default_aspx: System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
public void Page_Load(object sender, eventArgs e)
{
Label1.Text=”Hello World”;
}
}

When a request for the page is made for the first time, the page combines itself with the partial class to become the functional equivalent of the inline code. This compiled page remains valid until one of the involved files is changed.

Database Normalization and Normal Forms

Database can be designed with following approaches:

1. Top-down approach

The Entity-Relationship (E/R) modelling technique is the top-down approach. It involves identifying entities, relationships and attributes, drawing E/R diagram and mapping the diagram to tables.
Generally tables derived from E/R diagram conforms to at least first normal form. This approach is the best for validation of existing designs.

2. Bottom-up approach

Normalizing the database is the bottom-up approach. Normalization is a step by step decomposition of complex tables into simple tables. It reduces redundancy using the principle of non-loss decomposition. Non-loss decomposition is a process of breaking large/complex tables into smaller/simple tables without loss of information.
Redundancy is the unnecessary repetition of data that can lead to inconsistencies in data and update anomalies.
Generally, normalization follows the process of drawing E/R diagrams.

Note: A fully normalized record consist set of attributes that describe the entity and a primary key that identifies the entity.

Functional Dependency

Normalization theory is based on the theory of functional dependency.
Functional dependency can be explained like –

Given a relation R, attribute A is functionally dependent on B if each value of A in R is associated with precisely one value of B.

OR

Given a relation R, attribute A is functionally dependent on B only if whenever two tuples of R agree on their B value, they must agree in their A value as well.

For Example –
There is an entity named CUSTOMER with following attributes: CODE, NAME, ADDRESS and PHONE_NO.

Given a particular value of CODE, there is precisely one corresponding value of NAME, ADDRESS and PHONE_NO. So, the attributes NAME, ADDRESS and PHONE_NO are functionally dependent on the attribute CODE.

Functional dependency does not require an attribute to be the key in order to functionally determine other attributes.

For Example-
In the SALES entity that has attributes: CUSTOMER_CODE, CITY, PRODUCT_CODE, and QUANTITY.

CUSTOMER_CODE functionally determines CITY because city to which the shipment is made is also where the customer is located.

Note: Functional dependency represents many-to-many relationships.

Normal Forms

Normalization results in the formation of tables that satisfied certain specified constraints, and represent certain normal forms. Normal forms are table structures with minimum redundancy.

Some of the most important and widely used normal forms are:

1.First Normal Form (1st NF)
2.Second Normal Form (2nd NF)
3.Third Normal Form (3rd NF)
4.Boyce-Codd Normal Form (BCNF)

Note: A relation that is in the first normal form may also be in second normal form or third formal form.

First Normal Form

A table is said to be in the first normal form (1 NF) when each cell of the table contains precisely one value. To put data in 1 NF, all repeating information has to be removed.

Second Normal Form

A table is said to be in 2 NF when it is in the 1 NF and every attribute in the record is functionally dependent upon the whole key, and not just part of the key.

Example- A table has following attributes: EMP_CODE, DEPT, PROJ_CODE, and HOURS.

The primary key here is composite(EMP_CODE+PROJ_CODE). But the attributes of this table depend upon only part of the primary key.

• EMP_CODE+PROJ_CODE functionally determines HOURS
• EMP_CODE functionally determines DEPT but it has no dependency on PROJ_CODE.

Solution: Break this table into 2 tables:
Table A with attributes EMP_CODE and DEPT
Table B with attributes EMP_CODE, PROJ_CODE and HOURS.

Third Normal Form

A table is said to be in 3 NF when it is in 2 NF and every non-key attribute is functionally dependent on just the primary key.

Example- A table has following attributes: EMP_CODE, DEPT and DEPT_HEAD_CODE

The primary key here is EMP_CODE. The attribute DEPT is dependent on EMP_CODE and the attribute DEPT_HEAD_CODE is dependent on DEPT. So there is an indirect dependency on primary key.

Solution: Break the table into 2 tables:
Table A with attributes EMP_CODE and DEPT
Table B with attributes DEPT and DEPT_HEAD_CODE
Now each non-key attribute depends on the key, the whole key and nothing but the key

Boyce-Codd Normal Form

A table is said to be in BCNF if every determinate is a candidate key.

The definition of 3 NF is inadequate for the tables with multiple candidate keys where they are composite keys or they are overlapped (has at least one attribute in common)

Example- Table A has following attributes: EMP_CODE, EMP_EMAIL, PROJ_CODE and HOURS
Table B, which is master for table A has following attributes: EMP_CODE, EMP_EMAIL, EMP_PHONE, EMP_FAX etc.

Observations:
• Table A has multiple candidate keys- attributes EMP_CODE and EMP_EMAIL are candidate keys.
• Table B has composite candidate keys- EMP_CODE and PROJ_CODE functionally determine HOURS.
• And EMP_EMAIL and PROJ_CODE functionally determine HOURS as well.
• Composite keys are overlapped – PROJ_CODE is common.

Solution: Break table into 2 tables:
Table B1 with attributes EMP_CODE and EMP_EMAIL
Table B2 with attributes EMP_CODE, PROJ_CODE and HOURS