Saturday, December 11, 2010

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

Wednesday, June 2, 2010

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 >

Monday, May 17, 2010

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.

Tuesday, May 11, 2010

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.

Thursday, May 6, 2010

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.