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

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

  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


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]

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 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 .