.NET App does not read columns with blank and non-blank values in excel

My .net application that uploads excel file does not read columns with blank and non-blank values in excel.

In this case, system detects that first row is blank, hence it determines it as string, however for the first non-blank row, system detects it as numbers (different from the previous rows), hence system will not store the fields and show it as Null.

This problem is caused by a limitation of the Excel ISAM driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows and then chooses a datatype that represents the majority of the values in its sampling. (http://support.microsoft.com/kb/194124)

Solution:
1) Add IMEX=1 into your excel connection
http://forums.asp.net/t/1802376.aspx/1
2) Try using .xlsx, it should work with Microsoft.ACE.OLEDB.12.0
3) If you are using .xls, try putting the non-blank values records first before the blank values records in the excel.

Page Cannot Be Found - 404 2 1260

When you got this error 'Page cannot be found' on IIS 6
Resolution is to enable ISAPI and CGI on Web Service Extension folder in IIS.
Reference: here

How do you know that error number 404 2 1260?
It's from the Log files, usually located at C:\Windows\System32\LogFiles\
can be found from the following step:
Go to your website properties -> On 'Web Sites' tab, click the 'Properties ...'
There's Log File name field above the buttons.
The for Default web site will be W3SCV1

From the log files u can see the logs of your IIS website.

.NET Get user name

Here are the different ways to obtain user name in .NET:

-- To obtain from windows logon
Environment.UserName


-- To obtain from Siteminder
Request.ServerVariables["HTTP_SMUSER"]

-- To obtain from authentication
Request.ServerVariables["AUTH_USER"]

-- To obtain from application
HttpContext.Current.User.Identity.Name
Page.User.Identity.Value

If you have other ways, pls add in here.

IIS -> Database

In order to connect into the database from IIS

If you want to use a special account, u can specify in the AppsPool
Here's how to set in IIS6:
- Open AppsPool properties
- Go to Identity
- Select the configurable and put in the user name and password to connect to the database there.

However if you would like to use user's logged in account u need to add the following into your web.config

<identity impersonate="true"/>

Reference: here

Authentication Methods
In order to use Windows authentication, you may set the following in IIS:

• If SQL Server is on the same computer as IIS, select the Integrated Windows authentication check box.
• If SQL Server is a remote server, select the Basic Authentication check box and clear the Integrated Windows authentication check box.

In order to remove the prompt for username and password in IIS6 for Windows Authentication, you need to perform the following:
1) Open IIS
2) Open the website properties
3) Click 'Properties...' button (under Logging) on the Web Sites tab.
4)Take note of the website ID, the number after W3SVC

Default WebSite ID will be 1.
5) Open command prompt
6) Go to directory C:\Inetpub\AdminScripts
7) To check if the NT Authentication Provider is set, type  
cscript adsutil.vbs get w3svc/<WebSiteID>/root/NTAuthenticationProviders  
Note: Please replace the with the website ID from step 4.
8) If it’s not set or if the value is not NTLM, please set the NT Authentication Providers by typing the following cscript adsutil.vbs set w3svc/<WebSiteID>/root/NTAuthenticationProviders "NTLM"

Note: Please replace the with the website ID from step 4.

Once you have set, please perform step 7 to check if it is already “NTLM"

 After you set this, once you open your website (for Integrated Windows Authentication), it will not prompt the username and password.

Enable .net 4 in Windows Server 2003 IIS 6

If you are using .net 4 in Win Server 2003 and you got this 'Page Not Found' error.
In order to enable .net 4 after installation in IIS6, you may check the following:

Open IIS
Go to the Web Service Extension folder
Ensure .NET 4 is Allowed (not Prohibited)!

That's all ^-^
For other checking, you may google it.
Reference: here and here

CTE Max Recursion

Msg 530, Level 16, State 1, Line 3

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

 To prevent infinite recursion, a default value for MAXRECURSION = 100 has been already set.
To change the maximum recursion, use this:

 

DECLARE @Max AS INT = 150

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT * FROM CTE OPTION (MAXRECURSION 150);

Maximum No of recursion is 32767

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

 

And it will throw this error

Msg 310, Level 15, State 1, Line 10

The value 32768 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

Source: here