.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

CR2008 hanging

There was a case when my Crystal Reports 2008 was hanging after opening (can't even open a file or do any other thing), so here's what you can do to fix it.

Go to Registry (Start -> Run -> Regedit)
Locate: HKEY_LOCAL_MACHINE\SOFTWARE\business objects\suite12.0\crystal reports
and look for DisableCheckForUpdates and change it to a "1".

It works for me!
Source: here

Control-M Variables

To set date -x  in control-M  variable to be in DD/MM/YYYY format

%%SET %%PREVDD = -1
%%SET %%YEST = %%$CALCDATE %%$DATE %%PREVDD
%%SET %%YYYY=%%SUBSTR %%YEST 1 4
%%SET %%MM=%%SUBSTR %%YEST 5 2
%%SET %%DD=%%SUBSTR %%YEST 7 2
%%SET %%YESTERDAY=%%DD/%%MM/%%YYYY

This will give today's date in DD/MM/YYYY format
%%SET %%TODAY = %%DAY/%%MONTH/%%$YEAR

For more default variable click here.

Black box on Word 2010

After combining documents that contained track changes and reviewer comments, heading numbers for levels five became blacked out.

 

This is definitely a bug. To resolve this, please do the following:
1. Put your cursor on the heading just right of the black box
2. Use the left arrow key on your keyboard to move left until the black box turns grey
3. Use the keyboard combination ctrl+shift+s, the dialog "Apply Styles" should appear
4. In this box, click "reapply"

This fixed the issue for me. Good luck.

Reference: here

Show formula in excel spreadsheet

To do this, simple, just press Ctrl + ` key.

Reference: here

Float rounding issue involving sum

Try this in MS SQL:
create table #TEST (val1 float, val2 float)
insert into #TEST(val1, val2) values(0.0002, 0.0048)
select ROUND(val1 + val2, 2), val1+val2, ROUND(0.005,2)  from #TEST
drop table #TEST

val1 = 0.0002
val2 = 0.0048

The above query will return:
ROUND(val1 + val2, 2) = 0
val1 + val2 = 0.005
ROUND(0.005,2) = 0.010


It seems not just sum and rounding, even the multiplication itself will have issue


Dunno how to solve this yet, if u know, pls let me know.
Thanks.

Find a string within stored procedure

To find string content inside stored procedure, you can use this query:

Declare @StringToSearch varchar(100)
SET @StringToSearch = '%something%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type <> 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name

Different types in sysobjects:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

source: here

To find the column names from tables, you may use this query

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%colname%'
ORDER BY schema_name, table_name;

or use this query:

SELECT Table_Schema, Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_name in ( select name from sysobjects
where xtype = 'U' )
and column_name like '%colname%'
order by table_schema, table_name

.net timeout

.net default timeout when querying database (eg. calling stored procedure etc) is 30 seconds!
Please refer here.