Thursday, 31 May 2012

Delete a Record / Row from Excel with ADO.NET

 

In a WPF project I’m working on, I’m using an Excel document as a data source, and utilizing ADO.NET to access and manage my data.

 

The benefits of such is I can create DataSet’s that represent my data as well as outline key fields (that really don’t exist in Excel) and relationships (that technically don’t exist in excel… there are calculated columns – but I would advice against using them especially if you want to use ADO.NET) and generate calculated columns during runtime and create custom adapters to maintain my SQL outside of my code.

 

But one super negative is:

YOU CANNOT DELETE A ROW / RECORD IN EXCEL WITH ADO

Microsoft explains that excel has a different meaning of a row and a record, and has some sort of complicated excuse of why they give you this error when you try:

 

Deleting data in a linked table is not supported by this ISAM. 

 

Additionally, and perhaps unrelated. I found even the DataRow.Delete() method acts up with null reference errors when performing such activity during run time, even though I physically didn’t attempt to remove the data. This I can only assume is related as well – however I cannot confirm this.

The Blanking Key Field Workaround

Without the ability to delete a row, a usable workaround would be to use an UPDATE statement to blank out the Key Field and ensuring that the DataAdapter filters out blank key fields when SELECTing the data.

 

Here’s a Walk Through:

 

  1. Right Click on the Table Adapter in the Dataset Designer, and select it’s properties. There Change the “CommandText” of the “SelectCommand”

    DeleteRow1
  2. At the end of the SQL Statement of the SelectCommand add a WHERE clause:

    WHERE (NOT (Key_Field = ''))

    The key field being the field you will use to identify Rows during Updates and Inserts. Be sure to ensure your “UpdateCommand” and “InsertCommand” reflect identification or records via a manually determined Key.

    DeleteRow2
  3. When clicking OK a Dialog window will appear asking if you want to regenerate your Update commands. Say “No” as you should manually rewrite Update commands if needed.

    DeleteRow3
  4. As well, Visual Studio will ask you if want to update alternative Queries you may have entered in the Data Adapter as well. Hit “No” as well, as the filter itself is simply a work around. However, do manually review your other SQL statements for accuracy.

    DeleteRow4
  5. Finally, change or add a “DeleteCommand” removing the SQL from a typical DELETE statement entirely, and changing the Command type to Update

    DeleteRow5
  6. Change “DeleteCommand” to reflect something similar to: 

    UPDATE TABLENAME
    SET    Key_Field = ''
    WHERE (Key_Field = ?)


    DeleteRow6

Now, when you want to delete a field in your Excel through ADO, when you tell the Adapter to delete a record, the result will be a blank cell under the key column you specified, and your ADO layer will never show such record due to your filtering it out upon selection. Thus, it’s an imperfect workaround, but it’s one that works.

 

Resources:

  1. http://support.microsoft.com/kb/257819
  2. http://www.xtremevbtalk.com/showthread.php?p=969632

Please feel free to comment or leave your questions, Sincerely,

Wednesday, 30 May 2012

Facebook: Great Social Network, Terrible Business


One attribute of Facebook that makes it succeed on the Social platform is that the website caters to connecting real users with real users before anything else.

So why is that bad business?

Lets look at possible revenue production Facebook can seek:

Advertising

A real user social network cannot place advertising prior to it’s users, adding a step, a commercial, or interference outside of the real-people we want to connect with will only de-value the social nature of the site.

Granted, seeing that my friend “Likes Guinness” may be something of a word-of-mouth advertisement that Facebook may be able to monetize, the nature of the interest is that it’s my friend, and not Guinness brand itself.

Selling Social

One method Facebook can generate income is charge for facilitating businesses to access social information of it’s users (Either through Facebook Connect, Social Feeds, or sharing contacts). However, each connection to Facebook only adds value to Facebook more-so to the business connecting to it.

For example, the Windows Phone includes all your Facebook contacts on the phone, as well as feeding social data (through Windows Live) accessible by the phone. For the Windows Phone itself this connection to Facebook is seen simply as a feature, where as Facebook benefits doubly by facilitating more interaction between it’s users thorough its network. Essentially it’s more valuable for Facebook than the Windows Phone.

Forcing bidding for such connections will only restrict usage of the social network, and in turn, devalue connecting real-users to real-users.

Enhanced Experience

Offering it’s users for an upgraded experience at a fee would only open the door for competition to offer the free alternative. Facebook knows that there is more “social value” in having a free service than an enhanced service as outlined by their Photo sharing boom where they easily overtook Flikr with lower quality facilities, but, being free – unlimited – and easy while informing their users that they are getting the best they can offer gives Facebook the edge in adding real users to it’s user base.

But the value of social doesn’t essentially translate to business value, as Facebook management should be aware of seeing that their “social” enabled video sharing is no where close to over-taking YouTube. Where Youtube harbours it’s own social environment.

As a result, the enhanced experience will only prevent real users from using their network, and in turn, force users elsewhere.


Win a Monopoly, then tax everyone

Microsoft tried that (and continues to try it) with Internet Explorer. Essentially, the only reason why someone uses Internet Explorer is because they are participating in the monopoly nature of the browser where the browser comes packaged with Windows.

Granted it remains very popular (face it, it does the job) it’s popularity can be easily destroyed simply by charging for it’s use.

Facebook to can be destroyed simply by charging for it’s use.
Or, lose value instantly by charging to use it’s photo-sharing or any particular component.


Social Value being greater than Business Value is an illusion

Granted, this “Shouldn’t be” an illusion, it is. As long as advertising is the driving force of product sales: social will never properly exceed promotional assets. Even though people prefer word-of-mouth recommendations over advertising, The hunter-gatherer nature of individuals takes precedence over the society they participate in – we contribute to society by an accepted not-by-choice taxation/obligation, we hardly pursue such obligations but recognize their necessity.

In turn, Society enables us to mimic friends in hunting and gathering, advertising makes individuals aware of products – and there in an individual prefers to personally be the judge of a product than rely on social recommendations alone.

Now, The initial IPO for Facebook may have been over-valued. But there is still hope…

… If facebook can somehow harness the power of society over an individual, there might be an irresistible asset in connecting with real-users.

Charitable movements and Causes might be the framework Facebook can benefit financially from.

It’s remarkable to see societies using Youtube, Twitter, and Facebook to overthrow governments; Provide aid during disasters; Organize protest; And expose corruption. Granted the previous only facilitate human rights which have little uncorrupted financial reward – however – it is possible that other ‘social projects’ can be made financially rewarding.

For example, let’s say, I as an individual would like to see a bike lane on my street and will be willing to contribute $10 to it’s development only if 500 real people commit to the same project. Facebook there in can charge a minute fee for facilitating large scale projects that individuals “like” and produce a form of revenue in terms of managing such projects.

If this sounds like Governance, yes, that’s what it is. There is potential that Facebook can obtain revenue via competing with government and it’s money wasting time consuming bureaucracy. But, the question here in is, will people be interested in a social project, if the social project manager is interested in facilitating investors?

Who knows?

Maybe I’m becoming too hypothetical and we will see giant advertisements rule Facebook successfully in the near future – lets see where the NASDAQ takes Facebook. I’m expecting either a stock split, or a stock price around $10-$15 per share, unless Facebook manages some sort of strong revenue stream or some smart take overs or investments with the influx of money it just received. Only time will tell,

Saturday, 12 May 2012

Log Off, Desktop Taskbar Icon (for Windows)


Log Off Taskbar Button Screenshot

I never understood why “Logging Off” is more inaccessible than “Switching Users” especially when the nature of Windows itself works as a single user environment operating system.

So the question I ask is, why would anyone want to stay “Logged in” while someone else is using their computer? It’s beyond me, the only possible reasons I can think of are:
  1. To prevent updates from working correctly
  2. To appear to people that you are online, or available to chat with, even though you are not even there.
  3. To mis-sync all your cloud services
  4. To consume computer resources while you are gone.
Now granted, all those reasons sound tempting to the average user. For me, A simple way to log off is much more desirable. Especially in the newer Windows Vista/7 Operating Systems where the act of “Logging In” doesn’t take a quarter day.

Steps to make your very own Log Off Taskbar Icon:

Follow this extremely simple steps to get yourself Logged Off your computer (With ease)
  1.   Right Click your desktop and select “New”, and “Shortcut
    image
  2. In the “Creatre Shortcut” wizard type in:

    C:\Windows\System32\shutdown.exe -L

    then hit “Next” 
    image
  3. Be sure to the name the short cut “Log Off” then click “Finish”
  4. Right click the newly created shortcut and select “Properties”
    image
  5. In the Shortcut Properties dialog window, select the “Shortcut” tab and hit the “Change Icon…” button, a warning will come up, click “Ok” to continue.
    image
  6. The system should look for default icons in “%SystemRoot%\system32\SHELL32.dll” Search there if otherwise, there you should be able to find an ideal Logoff Icon:


    image
    (The Key Icon usually is the Ideal Log Off icon, you can however use whatever you please, like the Tree, I’m amused by this Tree Icon.. What it’s for is beyond me)
  7. Be sure to hit “Ok” and “Ok” again to close the respective dialogs, and drag that Short cut into the taskbar, or Quick Launch folder as you see fit.
Other things to note, you may want to consider other “Shortcut properties” such as “Run as minimized” and “Run as administrator” to better facilitate the Log Off action, and if you haven’t guessed the shortcut could be used for other things, if you change it’s location accordingly.



Enjoy Logging Off,

Twitter

Page Hits