March 28, 2004

Check this... more Microsoft Project

Output from a test for data on all the tables listed in the PJOLEDB.HTM discussed earlier leads me to believe that the documentation may be out of date.  It is quite late at night but I think that some of the tables listed in the documentation are not available.  I also have noticed some of the columns missing, I will try to see about getting a list of the missing columns as well.  I hope that someone else out there will someday be looking for some information on programmatically reading a Microsoft Project file :)

Test Output:

SELECT * FROM AssignmentTimephased
Invalid Table Name 'AssignmentTimephased'

Invalid Table Name 'OutlineCodes'

SELECT * FROM ResourceTimephased
Invalid Table Name 'ResourceTimephased'

SELECT * FROM TaskTimephased
Invalid Table Name 'TaskTimephased'


OutlineCodes is the only missing table, not sure why :(  As previously mentioned, it was late last night, I had all the table names typed wrong, the other tables aren't even in the docs.

March 27, 2004

MS Datashaping Example for Project 2003

Here is an MS Datashaping example that goes along with the previous few posts:

ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
conn.CommandTimeout = 30;
conn.Open("Provider=MSDataShape.1; Extended Properties='Project Name=d:\\Sample Microsoft Project.mpp';Persist Security Info=False; Data Source=''; User ID='';Initial Catalog=d:\\Sample Microsoft Project.mpp; Data Provider=MICROSOFT.PROJECT.OLEDB.11.0", null, null, 0);
rs.Open("SHAPE {SELECT Project FROM Project} APPEND ({SELECT Project, TaskUniqueId FROM Tasks} AS childRows RELATE 'Project' TO 'Project')", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int) ADODB.CommandTypeEnum.adCmdText);
for(int x = 0; x < rs.Fields.Count; x ++)
Debug.Write(rs.Fields[x].Name.ToString() + " : ");
if(rs.Fields[x].Name.ToString() == "childRows")
ADODB._Recordset rs2 = (ADODB._Recordset) rs.Fields[x].Value;
for(int i = 0; i < rs2.Fields.Count; i ++)
Debug.Write(rs2.Fields[i].Name.ToString() + " : ");

MS Project OLE DB in C#

So I'm working on a project and I'm supposed to make a component that reads from a Microsoft Project 2003 file and then use the data in my component to do some amazing and mind boggling stuff.  Now you know my motivations for beating my head against a wall when it would be a lot easier to just open up Project and read the data like everyone else (I do not want to be like everyone else... I'm unique... really).

The immediate problem that I noticed is a lack of documentation. If I wouldn't have had my paycheck motivating me to figure this out I would have given up after about the first 15 minutes of looking and not finding a single bit of information regarding programmatically retrieving information from a MPP file.  After searching Google about 17,032 times (sometimes if you type the same thing in enough times...) I was able to find some information about automating MS Project.

Automating an Office application server side is not a good idea, for any reason and the Microsoft documentation does not hold back in telling you this: "Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component", emphasis not added and article can be found at INFO: Considerations for Server-Side Automation of Office.

I read the warning but I did not heed it, I could not for the life of me find information on another way of reading the information out of an MPP file.  I knew that I could save the MPP file into a different format and then it was trivial to read the data, but I did not want to require our customer to save the project file into a format that MS Project did not default to. Side note: Why like Word and Excel and other MS products (?) has project not gone to a native XML file format?

So I automated MS Project on the server.  This task required some server side configuration so that Project would run under a specific user account and then since the files that it was required to load could be somewhere on the net where it didn't necessarily have default access I had to write a small utility that would detect the username and password prompt and automatically fill it in using win32 API calls (screenshot of utility below).  After I wrote all the different functions and straightened out all the different references I needed to use I was able to successfully read from a MS Project file.  The only problem was that it took about 15 - 30 seconds to read the data from the file. This only happened the first time the user loaded the file as I cached the data in memory, it was still really slow and far from a solid solution to the problem.  I quickly figured out that this whole automating thing wasn't going to be good for a long term solution and at this point the project was put on hold for unrelated reasons and I was pulled to other things.

win32 API username/password filler
Auto username/password filler

Fast forward about a month and a half and my boss asks me if I can get my project ready for the client to review ASAP, I tell him "No problem, should have it done in a day or two"... at this point I had read about the Microsoft OLE DB drivers for MS Project and had read the file located on my hard drive at C:\Program Files\Microsoft Office\OFFICE11\1033\PJOLEDB.HTM. Rant: all the MVP's in Usenet seem to point everyone to this file whenever an OLE DB questions comes up and I think it's damn annoying, give the people some code samples.  I just feel that pointing people to that file is not an MS Project 200? catch all. End Rant.  I thought it would be easy to load the Project file using the OLE drivers, then just hit it with an SQL command and boom get all the data that I needed.  It was probably going to take me about 35 minutes to write all the functions I had done to automate office over again using the OLE stuff, I was pretty happy because the product was going to work much better for the customer and I was going to have no problems fixing it up for them.  It was a win win situation... those are always the best.

Problems arose when I wrote up a function that used an OleDbDataAdapter to fill the data from an OleDbCommand using an OleDbConnection :) into a DataTable.  For some reason this failed whenever I tried to read from the Tasks or Project table in the DB.  This was pretty frustrating since it worked for some of the tables in the DB but not all of them and the exception was informative, like an officer is helpful when your in a hurry.  I then tried to use the code detailed here, the OleDbDataReader worked better then the OleDbDataAdapter, it would load the data without throwing an exception.  When I tried to read the data from the OleDbDataReader it would throw exceptions on some of the columns on some of the rows in the Tasks table and it would throw exceptions on every column in the Project table.  At this point I hit the web looking for an answer and could not come up with anything after a lot of time spent pouring over documents and USENET posts.  So I figured that the code samples found in the previously mentioned PJOLEDB.HTM file must work, so I was going to test them out in VB6 which I didn't have installed and if that worked I could make a VB6 COM that would retrieve the information and feed it to my .NET component for display to the customer.

After installing VB6 I tested the sample code and it worked like a charm, I was quite happy to have something finally working on every table so I spent about 2 hours doing about 30 minutes of work (I haven't written anything in VB6 for about 3 years) writing a VB6 COM.  It was working like a champ when I used another VB6 app to test out its functionality, it was .NET time.  I loaded the COM in .NET as a reference and when I did it automatically loaded the ADODB reference (screen shot below) as well.  I hadn't occurred to me to use the old ADODB drivers in .NET at this point, as soon as I saw the reference I immediately thought "Hey, maybe I don't need this VB COM".  I knew at this point that the VB6 COM was working and I had already written a bunch of code for it so for testing purposes it would be faster to test the functionality using my VB6 COM.

VS .NET reference screen
Yes, ADODB is the second reference in the list and yes, I didn't think of using it in the first place :(

The COM appeared to be working fine, no exceptions were thrown.  So I initiated an OleDbDataAdapter and used the Fill method to fill the ADODB RecordSet into a DataSet as a table.  I hit F5 and guess what popped up... one of those damn exceptions that gave me no clue to what could be wrong with my inputs into the OleDbAdapter.  I wasn't going to give up at this point so I tested looping through the ADODB RecordSet and seeing if it would throw an exception just by accessing some of the containing data.  It read and outputted the data fine so I knew that all the data was accessible and I figured that there is just something that the OleDbDataAdapter doesn't like about the ADODB RecordSets I was passing to it.  After learning that all the data was intact I wrote a function that parses an ADODB RecordSet and saves its contents to an identical DataTable (code below).  I wasn't sure if this was going to work because of the problems I had using the OleDbDataAdapter, it did work and it worked like a champ so that's what brings you this lengthy post on this beautiful Saturday evening.

Code (do not forget the ADODB reference):

DataTable dt = new DataTable();
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
conn.CommandTimeout = 30;
conn.Open("Provider=Microsoft.Project.OLEDB.11.0;PROJECT NAME=" + textBox1.Text, null, null, 0);
rs.Open(textBox2.Text, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int) ADODB.CommandTypeEnum.adCmdText);
for(int x = 0; x < rs.Fields.Count; x ++)
dt.Columns.Add(new DataColumn(rs.Fields[x].Name, rs.Fields[x].Value.GetType()));
Debug.WriteLine("(" + rs.Fields[x].Name + ") " + rs.Fields[x].Value.GetType().ToString());
DataRow dr = dt.NewRow();
for(int x = 0; x < rs.Fields.Count; x ++)
dr[rs.Fields[x].Name] = rs.Fields[x].Value;
dataGrid1.DataSource = dt;
this.Text = "Eric brings you the examples that you will not find in the MS docs :)";
catch(Exception ex)
this.Text = "Error, run in debug mode to see error in output window.";

Download Link: Microsoft Project OLE DB C# Example

Project File Explorer SUCCESS!!

Thank you Lord!

Here is a preview (click for a larger version):
Project Explorer Preview

I will try to find sometime this weekend to write something up regarding this thing and to package it up so others can benefit from it.  Later all, I'm going to go work out :)

March 26, 2004

MS Project OLE DB woes

Figured out a work around, kind of interesting so I will post later with details... probably tomorrow actually :)

Apple Tech Support

Machine: Blah blah blah... what product would you like help with?
Me: WebObjects 5.2
Machine: Did you say (Something about Images) version 2?
Me: No
Machine: Was the computer purchased for an educational institution?
Me: What the hell?
Machine: Connecting you with Apple Server product support.
(Hold Music)

At least once it connected me to someone they were helpful, and spoke fluent English, and they didn't transfer me a single time.  They are supposed to call me back though in an hour or so... I might be on lunch :(

Hopefully they leave a call back number.

Interesting way to create buzz

From Marc's Outlook on Productivity:

Axosoft is offering bloggers a free 3-user version of their .NET & SQL based OnTime defect tracking software (bug tracking software).  For more information, visit

March 25, 2004

Accessing an MS Project 2003 file

Can an MVP or someone please help me with this problem I am having? I don't quite understand it and I have spent a lot of time searching Google, USENET and the MSDN for the answer.

The problem is that I can read from most tables described in C:\Program Files\Microsoft Office\OFFICE11\1033\PJOLEDB.HTM but I can not access the Project table at all and I can not access some of the columns in the Tasks table. I have been able to access the Assignments, Calendar, Predecessors, and WBS tables. I havn't tried the others. I am wondering why I am unable to access some of the data.

C# Function that I am using to test the MPP access (I have also tried using an OleDbDataAdapter):

OleDbConnection projConn = new OleDbConnection("Provider=Microsoft.Project.OLEDB.11.0;Project Name=C:\\Program Files\\Microsoft Office\\Visio11\\Samples\\1033\\TIMELINE.MPP");
OleDbDataReader projReader;
OleDbCommand cmd = new OleDbCommand("SELECT * FROM Tasks", projConn);
projReader = cmd.ExecuteReader();
int fieldCount = projReader.FieldCount;
Debug.WriteLine("-------------------------Start Item!");
string unableToRead = "";
for(int x = 0; x < fieldCount; x ++)
Debug.Write(projReader[x].GetType().ToString() + " (" + x + ") : ");
unableToRead += x.ToString() + " : ";
Debug.WriteLine("Unable to read: " + unableToRead);
Debug.WriteLine("-------------------------End Item!");
catch(Exception ex)

I can get the information I am trying to obtain by using the automation tools that are built into Office, but that is truely a pain in the ass and MS says in their docs that it is not a good idea, so I'm trying to be a good citizen and avoid it.

grrrrr........... Why hasn't Project gone to the XML formats that Word and other MS Office products have gone to?

Microsoft Office Project 2003 OLE DB Provider Reference

I wish I could figure this thing out, works sometimes, but not most of the time :(

Will write something nice about it when I figure it out.  My blog sucks and it's the third link when you look the title of this post up in Google... horrible I tell you :(

That's two unhappy faces in one post, that sucks. crashes Word

You can ussually drag hyperlinks from a web page to a Word document and it works fine, but Word crashes when you drag the following link into your document (and any other generated using hugeurl).  I'm not sure why this behavior occurs, I atleast expect an error message in which Word explains itself.  "WinWord application error" or whatever it says, is not enough :)

Here is the link:
Really Long Address

moxie: required reading for all who criticize bush

moxie: required reading for all who criticize bush

I agree with this post.

Why didn't Bush do something about the terrorist organizations pre-9/11? Why is Bush attacking terrorists preemptively?

Sounds hypocritical to me :)

March 24, 2004

Yahoo! News - Israelis Stop Teen Wearing Bomb Vest

Yahoo! News - Israelis Stop Teen Wearing Bomb Vest

Completely nuts :(

Glad they caught him before he did anything stupid.

Direct link to video of Boy wearing suicide bomb vest

SharePoint User Control Web Parts

Download Web Part User Control Loader
Here is the line you will need to add to your web.config to get this Web Part registered as safe:
<SafeControl Assembly="AnyControl, Version=, Culture=Neutral, PublicKeyToken=72a8e2097fd71d02" Namespace="AnyControl" TypeName="*" Safe="True" />

Web references that mention using User Controls for Web Parts:

I have recently changed my ways and am now almost entirely using User Controls to power the Web Parts that I build.  What I've done for you today is build a Web Part that will auto detect any ASCX files in the SPS 2003 portal root and in the tool pane provide the user with a drop down box to choose which User Control to load.

I see it as specifically being a powerful tool in my testing and developing of new Web Parts.  The nice thing about it is that you don't have to include the User Controls into the same assembly as the Web Part I have built; make sure that the Web Part User Control Loader is being properly placed on your page, drop the ASCX files in the root directory of your portal, drop the DLL for your ASCX controls in the bin folder in the root directory of your portal, use the Web Part User Control Loader to load the User Controls you just placed.  No need to mark you User Control assemblies as safe in the web.config or to create a DWP file.

Some ideas for extending what I have done so far:

  • Use reflection to autodetect the public properties/methods of the loaded User Control, then building an interface for the user to modify the detected properties.
  • Fix the bug where after selecting a User Control you have to reload the page to get it to display the new control, there is an issue with when it updates the property of the Web Part and when the CreateChildControls() function in the Web Part runs :)
  • Fix up the ToolPart so that you can browse for User Control using a sexier interface

If you find this Web Part useful I would like it if you let me know and provide me with a copy of any modifications done.  I'm always interested in seeing how others interpret the possibilities of a piece of code.

Initial view and Web Part properties.

Web Part #1

Web Part #2

PS.  This is not the prettiest tool in the world but was simply a proof of concept I wanted to check out, thought I would share it with everyone.

March 22, 2004

Some interesting stuff for the night

Facts vs Values
I appreciate a lot of what Amanda writes, specifically in this post regarding Facts vs Values.  She is sometimes more Libertarian than I tend to be but I appreciate her views and her style.

NCAA Bracket Help
Some of my coworkers should have read this... after this weekend all of their brackets are completely annihilated and one of the wives that picked a random bracket is going to end up winning the pot :)  Found via Marc's Outlook on Productivity

Technical Careers @ Microsoft
I have helped a few people write resumes and they always wonder how it should be layed out.  It should be clean, concise and to the point, this post is a perfect example.

I'm off to see Starsky and Hutch, I hope it is funny.

SharePoint: List to Navigation

Credits: The flyout menu I use for this demo is from Twin Helix

What is it?: A WSS demonstration of using a Data View to generate an unordered list and then passing that unordered list to a 3rd party JavaScript and generating a navigation solution that is editable by the user.

Files: Sample WSS site with working demo

Directions: In FrontPage 2003 create a blank WSS site and then use the Tools/Server/Restore Web Site... menu item to load the Sample WSS site to the blank site you just made.  By taking a look at the scripts in FrontPage 2003 and the functionality in your browser you should be able to get a good feel  for how everything works.  Please feel free to leave a comment or drop me an email if you need some help.


View of rendered menu
Sample site; rendered menu.

View of unrendered menu
Sample site; the unrendered menu.

View of unrendered menu
Sample site; menu Administration.

Still working

Menu is done, but no time to post because I still have a few mods to make on this demo I am working on.  Sorry if anyone is interested, I will try to do it tomorrow sometime :(

March 21, 2004


Wanted to do this since I signed up a couple weeks ago.

There are two options for those that don't like it; syndicate or write a stylesheet that will render the blog how you would like it to show up and I will set up a javascript that saves in cookies your preference. Anyone will then be able to choose their favorite stylesheet and view the site however they want.  I purposely copied the design of CSS Zen Garden in hopes that others would contribute to this blog by writing stylesheets.

This design is purely experimentation on my part and won't be changed no matter how much anyone hates it unless they either do it for me or I get bored of it and somehow find time to make a new one.  The latter of these two will probably not happen, the only reason that I did a new design in the first place was so that I could test some stuff that I have been working on for work :)

PS: Hopefully I will be uploading the SharePoint menu that I posted about last week... I still havn't forgotten.