Code Project

Link Unit

Saturday, December 29, 2007

C# to VB.NET Converter

Our application development team is experienced in VB.Net. But we get most of the sample on internet developed in C#.

While searching google for C# to VB.Net Convertor, I end up with this

http://www.aspalliance.com/aldotnet/examples/translate.aspx

http://labs.developerfusion.co.uk/convert/csharp-to-vb.aspx

Thursday, December 27, 2007

Finding out Nth Maximum or Nth Minimum from Table's Column


 

Finding out Nth Maximum or Nth Minimum from Table's Column

For Finding Nth Maximun

Declare @ int

Set @n=5

  1. select qty from tmpJSales T1 where @n = (select count(distinct qty) from tmpJSales where qty >= T1.qty )

    Purpose: To find out the nth highest number in a column. E.g.: Second highest salary from the salaries table

    The following SQL statement is taken from

    http://vyaskn.tripod.com/code/nth.txt

  2. SELECT MAX(qty) from tmpJSales WHERE qty NOT IN ( SELECT TOP @n-1 qty FROM tmpJSales group by qty ORDER BY qty DESC )

    Although this statement is efficient than the First one. But first one is more manageable like what about finding minimum.

    In case of (1) simply change >= into <= that's it.

    In case of (2) change Max into Min and DESC with space

    And more expandable too. like what about this query find out the details of maximum nth sale/salary etc.

    In case of (1) Replace qty of outer query with *

    In case of (2) Put (2) as subquery as follow

    select * from tablename where qty = ( (Query2) )

    select * from tablename where qty in ( (Query2) )


 

Thursday, December 20, 2007

Ad hoc access to OLE DB provider has been denied

Solution


In SQL server 2000 I was trying to import an xls file into a table.


SELECT * into TempData FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Data1.xls', [Sheet1$])


It worked with sa login, but with other users/login it gave error

"Ad hoc access to OLE DB provider [Microsoft.Jet.OLEDB.4.0] has been denied.
You must access this provider through a linked server"

This problem can be solved by
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers \Microsoft.Jet.OLEDB.4.0]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000000

The last line does the trick. If DisallowAdhocAccess is absent, it defaults to 1.

Tuesday, December 18, 2007

Using HttpUtility.UrlEncode

Perhaps the most popular way to pass data between web-pages is by using querystring. This is used to both pass data to a new pop-up window, as well as to navigate between pages.
Querystring passes data in name value pair , where names are separated by &. So querystring would be something similar to
name1=value1&name2=value2 etc . Now if the value itself contains & ,then wrong values would be extracted
Go through this link http://www.blooberry.com/indexdot/html/topics/urlencoding.htm to know why we need urlencoding.
This method is good for passing simple alpha-numeric data, but it can be a problem to pass special characters in the URL, especially in different browsers.
· An ampersand would split the name-value pairs. (If you want to pass the value "Johnson&Johnson baby", but the & indicates a new name-value pair, then the value will be truncated to just " Johnson ". For example, in "id= Johnson&Johnson baby ", getting the querystring "id" will return just " Johnson ", and "Johnson baby" will be interpreted as its own key.
· Apostrophes, greater than or less than signs may be interpreted as a cross-site scripting attack by some security plug-ins. As a result, these plug-ins may block the entire page.
· Other special characters (like slash or space) may be lost or distorted when sending them into a url.
Fortunately there is a solution to handling special characters. .Net provides us the ability to Encode and Decode the URL using System.Web.HttpUtility.UrlEncode and HttpUtility.UrlDecode
(note that HtmlEncode, which encodes html, and won't affect the &.). This replaces problematic characters with URL-friendly equivalents.


Update on gridview produces ORA-01036: illegal variable name/number

While using SQLdatasource to access and update an Oracle table using UpdateCommand , error ORA-01036 occurs if we by mistake use @ in parameter name ,some missing parameter or missing providername.

Solution :

  1. For update statement to work in case of Oracle all the parameters must be there and in exact order.
  2. ProviderName = "System.Data.OracleClient" in SQLDataSource
  3. : is to be used in place of @

I hope it help fellow programmers .

Tuesday, November 06, 2007

Redirect URI cannot contain newline characters

I was facing this problem while going through development of ASP.Net page.I was redirecting to an error page with an error message generated passed in query string. It is clear from the error that string cannot contain newline character. So I simply used System.HttpUtility.URLEncode(errString) and then passed it through querystring.

Failed to access IIS metabase problem

Possible Cause:-
When you install IIS AFTER .NET 2.0 framework, the rights of the ASPNET user had not been set correctly.

Resolution
Repair (Uninstall if repair does not work for you) .NET Framework 2.0
Simply run the following from command line to reset the IIS registry settings for aspnet user. Usually framework directory for .Net Framework 2.0 resides under %WindowsDir%\Microsoft.NET\Framework\v2.0.50727

%WindowsDir%\Microsoft.NET\Framework\v2.0.50727 > aspnet_regiis -i

Tuesday, October 23, 2007

DBLink Problem : TNS COULD NOT RESOLVE SERVICE NAME.

I was also facing the same problem when i create the DB Link as

CREATE PUBLIC DATABASE LINK TestService USING 'TestService';


TNS COULD NOT RESOLVE SERVICE NAME.

This is basically the problem with the TNSNAME.ora file, the 'Using' clause not able to find the service name in the TNSNAMES.ora

This is how i solved: I just copy paste the TestService`s Description part as

CODE

alter system set global_names=false
/
CREATE PUBLIC DATABASE LINK TestService
using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TestHost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TestService)
)
)
'
/
here my destination server host is TestHost & service name is TestService .

NOTE: If I had set the "GLOBAL_NAME" parameter to "TRUE", then the DBLink name need to be same as that of DBName .

Unrecongnised Section ConnectionStrings

When one of my client was deploying the web application he faced this issue. The following Steps helped me. Hope they help you too.

By default, when you create a website in IIS, the framework is 1.1

ASP.NET has introduced the new section in the web.config which will not be recognized in the .NET 1.1 framework.

1) Open IIS and browse to the website under computer_name > Web Sites > Default Web Site > website_name.
Where website_name is the actual name of the website, in this case the MySite website.
2) Right click website_name.
3) Click Properties.
4) Click the ASP.NET Tab
5) From the ASP.NET Version dropdown, select 2.0... version and Click OK.
Now, if you visit your application main page, it should work !

Wednesday, July 11, 2007

Delete Duplicate Rows

You may like to try these methods.
1. Add an idenity Column to the table
Alter table yourtable Add myseq int identity(1,1)

2. Delete all the rows where rows are duplicate based on the condition
Delete from yourTable where Exists ( Select 1 from YourTable B where Yourtable.column1=B.column1 and Yourtable.column2=B.column2 and Yourtable.myseq < B.myseq )

Or simple
1. Select distinct * into newTable from YourTable
2. delete from Yourtable
or
truncate table Yourtable
3. insert into Yourtable select * from newTable