A pre-wedding post

3 days from now (actually 2 days 22 hours and 30 minutes) I'll be a married man. This can at least partially explain the lack of writing.
Last week I tried to find a method to write an Escel file from C#. This thing appears to be a bigger problem than can be expected mainly because the direct method, using OLE, is SLOW. I used it for reading the file, 10 sheets of 4 columns and 600 lines. It takes approximately 10 minutes using the OLE. That's a NO-GO! So we started digging and found that Excel readers are not so rare. Though not so common as one would think they ought to be. We took one and voila, it takes 5 seconds to load and display. Yep, displaying such a large dataset takes a bit of time.
As for the writing, we're still searching. We've found a tool to convert the XMLs of the office2007 to the Excel 2000 we use. But writing the XMLs is problematic, it appears that M$ keeps things as nasty as possible even in places they can't be.
So at this moment we're still looking for solutions.
BTW there are commercial components that do the job, but we want the control over the resulting format, after a minor error with the reader we have all the reasons to dig into the source-code in the writing component.

That's for now, and I'll write again after the marriage, probably at the beginning of 2008:)

"Date/Time" field in Access

There are times when you need a small DB which is a single file. Usually I prefer the FireBird, but, when the boss says "Niet" you have to obey. So I had to create the DB using Access, actually it consists of 3 tables and 2 foreign keys. Easy enough for Access to handle.
Next thing I had to connect to it, that's also an easy job:
const DB_LOCATION = "c:\Data\TimesDB.mdb"
Dim conn : Set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_LOCATION & ";Persist Security Info=False"

So far there's nothing new or any problems. But the troubles begin once "Date/Time" field is involved.
We write the dates as "dd/MM/yy" or "dd/MM/yyyy". The server on the other hand is configured to use dates made by an unknown nature (if it was "MM/dd/yyyy" it would be fine). The result is that every time I've inserted a field the value was "computer-dependant". Obviously it's not the way things are meant to be.

And for this reason I made myself a small useful function:
function parseDate(tmpDate)
if instr(1, tmpDate, "/")>0 then
dim tmpDay
dim tmpMonth
dim tmpYear
dim i
i = instr(1, tmpDate, "/")
tmpDay = mid(tmpDate, 1, i - 1)
tmpDate = mid(tmpDate, i + 1)
i = instr(1, tmpDate, "/")
tmpMonth = mid(tmpDate, 1, i - 1)
tmpYear = mid(tmpDate, i + 1)
if len(tmpYear)=2 then tmpYear = "20" & tmpYear
parseDate = tmpYear & "-" & tmpMonth & "-" & tmpDay
else
parseDate = tmpDate
end if
end function

Note the returned value is in the ISO format (yyyy-MM-dd) this format is a well known format, thus making it the best choice.

P.S. This function is useful in FireBird too, though it's not a must when you can actually "force" the DB to use formats in the creation script. A thing that is not recomended when using Access.