Microsoft Access SQL Query Bug

I'm getting crazy, found that the TOP # in the Microsoft Access Database is having a bug!

While I'm trying to do this for one access database, it gives me 18 records!!

SELECT TOP 10 Right([FromYear],4) AS FromYear, Right([ToYear],4) AS ToYear, 'spm' + mid([Reference No],1,4) + '-' + mid([Reference No],5,2) + '-' + mid([Reference No],7,2) + '-' + mid([Reference No],9,3) + '.jpg' AS PicRefNo, [Reference No] AS AccessionNo, [Item] FROM tblStamps WHERE 1=1 AND [Description] LIKE '%king%' AND [Reference No] NOT IN (SELECT TOP 10 [Reference No] FROM tblStamps WHERE 1=1 AND [Description] LIKE '%king%' ORDER BY FromYear, ToYear ) ORDER BY FromYear, ToYear

I believe it is the ORDER BY which confused up the TOP, and both of them can't be used together (not compatible).

regards,
choongseng

Office 2000 PIAs??

We've a problem in office today, while I was encouraging them to use office interop, it turned up that the package that ship w/o PIAs works on Office 2000, XP, 2003 and the one the w/o PIAs, works only on Office 2003.

Subsequently, I've consulted Dr.Google and he refer me to Mr.MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2k3ta/html/OfficePrimaryInteropAssembliesFAQ.asp

Quote from the address:

Using the Office XP PIAs and the Office 2003 PIAs

PIAs are version specific, so you should use the same version of the PIAs as the application version. For example, if you want to migrate your Microsoft Office XP managed code solution to Microsoft Office 2003, you should recompile the Office XP managed code solution with Office 2003 PIAs.

You should also have two different setup packages—one for Office XP and another for Office 2003—if you intend for the same solution to work in both Office XP and Office 2003. Deployment of PIAs for Office XP is very different from that for Office 2003. For further information about these differences, see the ReadMe file included in the Office XP PIAs download.

Wonder if anyone have similiar experience with building apps that writes to office documents?
Just wonder if there's Office 2000 interop or shall I conclude that working w/o interop will yield the best compability?

regards,
choongseng

Releasing of unmanaged code (COM) from managed code (.NET) calls

While most of the complicated components are still implemented in unmanged code (it will take some long time for us to have a entire managed environment though), we uses Runtime Callable Wrapper (RCW) to wrap up COM objects to be callable from managed code.

However, these has to be used carefully, as the garbage collection won't automatically clear and release the memory used by the COM objects. This problem arise when my colleague asking me about how to solve it, as she observed that there's many EXCEL.EXE running in the taskbar after she run her program which creates excel spreadsheet. While I've read about it, but have never get a chance to use it, thanks again to Dr.Google which always faithfully give me the answers within seconds.

Releasing of unmanaged code (COM) from managed code (.NET) calls:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

Below are quoted from the site for easy reference:

To make sure that the Office application quits, make sure that your automation code meets the following criteria:
• Declare each object as a new variable. For example, change the following line of code

oBook = oExcel.Workbooks.Add()


to the following:

dim oBooks as Excel.Workbooks
oBooks = oExcel.Workbooks
oBook = oBooks.Add()


• Use System.Runtime.InteropServices.Marshal.ReleaseComObject when you have finished using an object. This decrements the reference count of the RCW.
• To release the reference to the variable, set the variable equal to Nothing or Null.
• Use the Quit method of the Office application object to tell the server to shut down.

Client-side XML

Wow!... finally I got the keyword and found this crazy API:

Sarissa to the Rescue
http://www.xml.com/pub/a/2005/02/23/sarissa.html

sarissa
http://sarissa.sourceforge.net/doc/

I've been cracking my head for 2 days to came up with a XML parse with regular expression. Which I've came up with it, but it is limited and have rooms to improve.

regards,
choongseng

Regular Expressions

Resources:

http://www.regexp.com/
http://www.regular-expression.info/

4GuysFromRolla - Regular Expressions
http://www.4guysfromrolla.com/webtech/RegularExpressions.shtml

Greg Reinacker's Weblog
Nested Constructs in Regular Expressions (using .NET)
http://www.rassoc.com/gregr/weblog/archive.aspx?post=590

Using a Regular Expression to Match HTML
http://haacked.com/archive/2004/10/25/1471.aspx

Tools:

Dela's Ramblings
Regular Expression Library Builder
http://briandela.com/blog/archive/2005/06/03/284.aspx

XML Validation and Parsing with Regular Expression

Anyone have any idea on regular expression to validate XML and parse it?
I've been trying very hard today to build a XML parser myself using javascript, and just didn't manage to figure out how to do about it using regular expression.

Along the way, I've found some cool tools for regular expression:
http://www.weitz.de/regex-coach/

For references on regular expression:
http://www.regular-expression.info/

regards,
choongseng

Writing Object Oriented Javascript Part 1

Discover this article from CodeProject:

ASP.NET and Visual Studio 7.0 are making important contributions to the improvement of the web development experience. Unfortunately, there is also a tendency created among developers to limit their interaction with JavaScript. Clearly JavaScript is valuable for adding client-side functionality to web pages. However ASP.NET programming models suggest that developers produce page layout while emitting client-side JavaScript from ASP.NET controls. As a consequence, this model tends to limit JavaScript to procedural adjuncts. This is rather unfortunate because it severely limits the power of an object-oriented scripting language that developers can use to write rich and reusable client-side components....
http://www.codeproject.com/aspnet/JsOOP1.asp

Very good article! I didn't know that javascript is indeed that powerful...

regards,
choongseng

VS 2005 Beta 2 Bug

Reported one more bug today:

Cross page posting will not work for DropDownList and further more as you investigate on, you'll discover that the cross post back actually process the previous page, which is not desired.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=da563451-b319-4984-a0fe-fe4dbdd68675

XML Serialization in .NET

Tired of writing XML Document and tags? and even more tired when you have to code exactly the reverse thing when reading back from XML document?

The solution is to make use of the XML Serialization in .NET!

This example shall give you an overall idea of the XML serialization function of .NET.

--> No more codes for XML Writing
--> No more codes for XML Reading
--> 1000% higher productivity in writing codes

The codes sample is a prototype example for my company, which will be used as a file format eventually for data exchange.

Imports System.Xml.Serialization
Public Class PSF_XMLSerializer_PrototypeForm
    Private Sub PSF_XMLSerializer_PrototypeForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       Dim newPSF As New PortStatusFile
       newPSF.FileVersion = "123"
       newPSF.RevisionDate = "111"
       newPSF.TS.Vessel_Code = "ABC"
       newPSF.TS.Vessel_Oper = "XCL"
       newPSF.CS.SMD.Add(New SMDetails("One"))
       newPSF.CS.SMD.Add(New SMDetails("Two"))
       newPSF.CS.SMD.Add(New SMDetails("Three"))

       Dim xmlsel As New XmlSerializer(GetType(PortStatusFile))
       Dim xmlWriter As New System.IO.StreamWriter("C:\test.txt")
       xmlsel.Serialize(xmlWriter, newPSF)
       xmlWriter.Close()
       Dim xmlReader As New System.IO.StreamReader("C:\test.txt")
       Dim anotherPSF As PortStatusFile = CType(xmlsel.Deserialize(xmlReader), PortStatusFile)
       anotherPSF.RevisionDate = "9999"
       anotherPSF.TS.Line = "SEA"

       Dim xmlW2 As New System.IO.StreamWriter("C:\test2.txt")
       xmlsel.Serialize(xmlW2, anotherPSF)
 End Sub
End Class

<XmlRootAttribute("PSF", Namespace:="Seacon.eBiz", IsNullable:=False)> _
Public Class PortStatusFile
   <XmlAttributeAttribute("version")> _
   Public FileVersion As String
   <XmlAttributeAttribute("Rev_Date")> _
   Public RevisionDate As String
   <XmlAttributeAttribute("SID")> _
   Public SID As String
   <XmlAttributeAttribute("format_datetime")> _
   Private DateTimeFormat As String
   Public TS As TerminalDetails
   Public CS As CargoDetails
   Public Sub New()
       Me.TS = New TerminalDetails
       Me.CS = New CargoDetails
   End Sub
End Class
<XmlRoot("TDR", IsNullable:=False)> _
Public Class TerminalDetails
   Public Vessel_Code As String
   Public Vessel_Name As String
   Public Vessel_Oper As String
   Public Voyage As String
   Public Leg As String
   Public Line As String
   Public Port_Code As String
End Class

<XmlRoot("SM", IsNullable:=False)> _
Public Class CargoDetails

   'Multiple SMD
   <XmlArrayItem(ElementName:="SMD_ITEM", _
         IsNullable:=True, _
         Type:=GetType(SMDetails)), _
      XmlArray(ElementName:="SMD")> _
    Public SMD As ArrayList
   Public Sub New()
       Me.SMD = New ArrayList
   End Sub
End Class
<XmlRoot("SMD", IsNullable:=False)> _
Public Class SMDetails
   Public Cntr_Num As String
   Public Sub New()
   End Sub
   Public Sub New(ByVal cntrNum As String)
       Me.Cntr_Num = cntrNum
   End Sub
End Class

If you wanted to add stylesheet to your serialized object, check this out:
http://www.tkachenko.com/blog/archives/000246.html

New MCP Program

Happen to see this from sgdotnet.org, new MCP program:

http://www.mcpmag.com/news/article.asp?EditorialsID=821