BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) when Excel is opened

For Excel Connection
'HDR=NO; = HEADER
'IMEX = 1; = type= text
'IMEX = 2; = type= Mixed
'change the registry...
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0 - it will check the data type for every field
'but at this momment we can not change using MAXSCANROWS=0 - it doesn't work
Dim excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileFullName & ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"

When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet that is open in Excel, a memory leak occurs in the Excel process. Repeated queries may eventually cause Excel to run out of memory and raise an error, or cause Excel to stop responding.

The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel.

If possible, query the Excel worksheet only while the file is not open in Excel.

If the worksheet must remain open (for example, to allow dynamic recalculation of worksheet values on an ongoing basis) use one of the following methods to work around the behavior:

Method 1

•

Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel data to a new worksheet. For additional information about using the SELECT INTO syntax to export data, click the following article number to view the article in the Microsoft Knowledge Base: 295646 (http://support.microsoft.com/kb/295646/) How To Transfer Data from ADO Data Source to Excel with ADO

Method 2

•	Use the SaveCopyAs method of the Workbook object in the Excel object model to programmatically save the open Excel file under a new name. You can then query the copy of the file that you previously saved under a new name from the ADO application.

Method 3

save as Excel using Excel.Application . SaveAs to Excel CSV file and Read that CSV file using OLEDB Connection

Provider=Microsoft.Jet.OLEDB.4.0;Data Source='FOLDER PATH';Extended Properties='text;HDR=Yes;IMEX=1;FMT=Delimited'


and using select * from fileName.

Adding Permission to Excel COM Object

If you run the service using Network Service Account then you need to add permission to Microsoft Excel COM Object because by default Microsoft Excel as a COM object can only activated by the following accounts:

• Administrator
• System
• Interactive

Configure DCOM

• Go to the Start-Run menu item.
• Type in "DCOMCNFG" and hit enter.
• This should load the "Component Services" MMC (you can also load from Administrative Tools - Component Services"
• Expand "Component Services"
• Expand "Computers"
• Expand "My Computer"
• Select the "DCOM Config" item
• Select the "Microsoft Excel Application" item.
• Right click and select Properties
• Add Permission to network service or ASP Net account
  

EventLog.WriteEvent in Windows Service or ASPNET

If you get this error in writting EventLog.WriteEvent in Windows Service or ASP.Net.
"The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security."

You need to create your source manually. because the process can not created for u.

To add your source manually,
Open Regedit and add your source inside.
"HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application".

After that you will be able to Write event log freely.

How to debug your Javascript in .net 1.1 / 2.0

To debug your javascript code...

* Using Internet Explorer
Make sure that u untick Disable Script Debuging options.

* for Firefox.. There is extension plugin

*Configure your web config .. to enable debug...

* Press F5 for build with debugging instead of Shift + F5.


* You may need to separate your javascript into other file... It can not be embedded into your .aspx file because at the runtime.. your *.aspx content will dynamically converted into html...

However in VS 2005.. You can debug your embeded javascript...
But you can not directly put a break point into that line of code...
You need to Run first (F5) and go to DEBUG > WINDOWS > Script Explorer... You will see.. the merge javascript with your aspx which has been converted into html... and you can put the break point in there.


* USE ASP.NET AJax Extender...

You can use
Sys.Debug.trace("Whatever you want to trace - will appear in output window");
and
Sys.Debug.fail("It will stop in this point.. and you may start debugging");

Using AjaxPro2.dll from Michael Schwarz

*Add web Config
inside configuration/configSections

inside configuration/system.web/httpHandlers



*add reference AjaxPro2.dll into the bin folder
using AjaxPro;

* in page load (postback and not) - add Utility.RegisterTypeForAjax(typeof(_Default));

*On client ajax method add attribute
[AjaxMethod(HttpSessionStateRequirement.ReadWrite)] public string MyAjaxMethod(string username)
{
return "Halo" + UserName;
}


*call in client javascript
_Default.MyAjaxMethod("Kurniawan").value

Thread and Delegate Issue

Run Thread with Parameters
----------------------------------------------
*** But it is only limited to 1 object...
Thread t = new Thread(new ParameterizedThreadStart(Run));
t.Start("an Object");

or using Thread Pool
-----
ThreadPool.QueueUserWorkItem(new WaitCallback(Run), "an Object");
-----

----------------------------------------------------------
*** Use Class method...
public class UrlFetcher

{
  string url

  public UrlFetcher (string url)
  {
      this.url = url;
  }

  public void Fetch()
  {
      // use Any Attribute here
  }
}

UrlFetcher fetcher = new UrlFetcher (myUrl);// Set All the input
new Thread (new ThreadStart (fetcher.Fetch)).Start();

-------------------------------
or Call Delegate Asyn

delegate void MyDel(object a, object b);

          MyDel d = new MyDel(Fetch);
          d.BeginInvoke(a,b,new AsyncCallback(AfterRun),"this is state");

====================================

===================================================
CALL DELEGATE Dynamically

void Force(Delegate method, object[] args)
{
method.DynamicInvoke(args)
}

Coding Issue

Finalize VS Descructor


Finalize and (~) Desctructor is the same

in VB - use Finalize
but
in C# - u can not use Finalize - must be desctructor (~)

Protected Overrides Sub Finalize()
' Desctructor code to free unmanaged resources
MyBase.Finalize()
End Sub

~ ClassName(){}


***Desctructor will be called by the garbage collector - if you call
GC.Collect();





-------------------------------------
GC.Collect VS GC.SuppressFinalize(Object)
Collect - will run Garbage Collector - which run your Desctructor / finalize
- However I realize It will run before you create another object.

SuppressFinalize - will not run.. your desctructor on the object



-------------------------------------
Dispose() Vs Descructor

Desctructor - will guarantee run by Garbage Collector - But It may time Lag even you put GC.Collect(); - it Collect.. after you create another object

Dispose - If you need to explicitly clean up without waiting GC...
- But It will not be run by GC - You need to call
YourObject.Dispose();

or using using Keyword in CS
Using(Object obj = new Object);

Note you call the Dispose method yourself and in Desctructor there is .Dispose as well...
, you should suppress garbage collection on the object once it's gone, as we'll do here.

*** My preference is use Desctructor... to eliminate the risk - and called GC.Collect();


-------------------------------------
ROUNDING Decimal 2 Digit
Math.round(123.23123 * 100) / 100
ROUNDING Decimal 3 Digit
Math.round(123.23123 * 1000) / 1000

-------------------------------------
REF and OUT in C#
Ref to use in and out..
out is for out only... you can not parse put input in it... you need to init inside the method...
--------------------------------------

Web Service Problems

Deserialization
====================
* Namespace is very important when you want to convert your xml to the class that has been provided by web service (WSDL)

* You need to add the namespace from the document
xlns:"www.tempuri.org"

* add namespace in your serialization object
------C#-------
Serialization aSer = new Serialization(typeof(YOUR_OBJECT_NAME), _
doc.DocumentElement.NamespaceURI));

------VB-------
dim aSer as new Serialization(GetType(YOUR_OBJECT_NAME), _
doc.DocumentElement.NamespaceURI))

=====================================

.Net 1.1 migrating to .Net 2.0 issue

Problem: Your directory will be included in the projects

Discussion: Some times.. you need to backup 1 of your UI design in .NET 1.1
because you need to experiment a new design...

in .NET 1.1, It will be fine because... unless you include that file, it won't included into your project...

However.. in .NET 2.0... your project will automatically include them into your folder... and as a result when you compile,, it will raise an error... because there is duplicate.. aspx which refer to the same dll...


Solution: Right click >Exclude from projects

****************************************


Problem: You have several plugins in the previous version

Discussion:
This is what happen to my previous projects...
We have several plugins that enhanced our systems.

In .net 1.1 we just build several plugins in different project because we have several developers without a version controls... so we can shared the workload in our teams easily.

and After the plugins finished we just copy the dll into the bin folder and copy the aspx file into the project...

However, We can not do this anymore... because in .NET 2.0. all the dll.. will be compiled into dynamic name such as webApp_XXXXX.dll.

So if there is update on 1 of our plugins, we need to copy the aspx as well...
How about if it builds with the same name?

I tried to rename the .dll name and the aspx @page directive to refer to that dll... But It didn't work anymore..

You can try to publish using static name... (default.aspx.dll)

but it will make a dll for each aspx page...
And It will cause alot of problems...


Solution: One of the solutions is you need to combine all your plugins into 1 project except.. library which you and add the static reference.

Because .Net 2.0 Now has support multi languange into the same projects.


****************************************

Oracle Issues

Problems : Oracle DSN Connection with IIS
[IM004] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

when trying to open DSN Connection from IIS

Possible Solution:
* Give permission to
IUSR_mycomputername and IWAM_mycomputername to read oracle/Bin folder

---

**************

---

Problems : Java Gone after install Oracle
Can not compile your Java Code anymore after Install Oracle
* It caused by oracle has been change the classpath which used by your java compiler

Possible Solution:
* Delete the oracle Classpath from System Path from System/EnvironmentVariable

TIFF 2 PDF Error

Problem: GDI+ Error
It usually happens when Tiff2PDF.dll trying to convert Tiff image to PDF

Solution:
Make sure directory has the write permission
* Documents Folder in filewiseWeb must have ASPNET and IIS_WP permission to write

---

**************


Problem: Can not find dll

Solution:
*Register tiff2pdf.dll
*Put t2pdf.h into system32
*Install TiffDll50

---