SandboxM

Marc Grabanski has written a great tutorial on how to spice up Google Maps using JQuery. What I have done is a slight variation of his tutorial.

Instead of placing ten random markers on the map, we will generate driving directions from point A to B and use the KML data from Google Maps to render the markers.
Appending “&output=kml”, Google Maps will return a KML document which we can then parse to get the marker information.

<br />var geoXml;<br />var urlKML = "http://maps.google.com/maps?saddr=";<br />urlKML += fromAddress.val();<br />urlKML += "&amp;amp;amp;amp;daddr=";<br />urlKML += toAddress.val();<br />urlKML += "&amp;amp;amp;amp;output=kml";<br />geoXml = new GGeoXml(urlKML);<br />

We can now create an XMLDOM object using the KML and parse the nodes to get all the marker coordinates and its description.

<br />var doc = new ActiveXObject("Microsoft.XMLDOM");<br />doc.async = false;<br />doc.load(urlKML);<br />if (doc.parseError.errorCode != '0') {<br />errordiv.innerHTML = "Error generating KML! Probably an invalid address...";<br />return false;<br />} else errordiv.innerHTML = "";<br />var coords = doc.documentElement.selectNodes("Document/Placemark/Point/coordinates");<br />var places = doc.documentElement.selectNodes("Document/Placemark/name");<br />

We can now loop through each and add the markers to the map.

<br />for (var i = 0; i < coords.length; i++) {<br />var pts = [];<br />pts = coords[i].text.replace(",0", "").split(",");<br />var point = new GLatLng(pts[1], pts[0]);<br />marker = new GMarker(point);<br />map.addOverlay(marker);<br />markers[i] = marker;<br />}<br />

And append the description to each marker using the .html property:

<br />$(markers).each(function(i, marker) {<br />$("<li />")<br />.html(places[i].text)<br />.click(function() {<br />displayPoint(marker, i);<br />});<br />GEvent.addListener(marker, "click", function() {<br />displayPoint(marker, i);<br />$("#myInfoWindow")<br />.html(places[i].text)<br />});<br />});<br />

You can view download the final demo here.
Its still work-in-progress so feel free to chime in with your suggestions. Next I am going to add to the map are lines connecting the markers with each other.

Here’s how to generate Excel (xls\xlsx) files on-the-fly.

All search results talked about setting the Page.Content-Type to “vnd.xls” or “vnd.ms-excel” and using the obsolete “content-disposition” meta tag. But I just wanted to save the data without popping up the confirmation dialog. Luckily Excel can read HTML tables very well, so all I had to do is take the HTML buffer and save it to disk with an XLS file extension.

protected void Page_Load(EventArgs e)
{
string strData = GetDataFromWhereEver();
ExportToExcel(strData, "C:\ExcelFiles", "MyExcelFile.xls");
}

private void ExportToExcel(string strHTMLbuffer, string path, string filename)
{
Byte[] bytes = System.Text.Encoding.ASCII.GetBytes(strHTMLbuffer);
FileStream fs = new FileStream(path + "\\" + filename, (FileMode)FileAccess.Write);
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}

But once converted, I didn’t like the whitespace in Excel. So using the VBA code that I found here and here, I managed to turn on the Gridlines in the Excel file.

Here you go:

Excel.ApplicationClass  app = new Excel.ApplicationClass();
app.Visible = false;
Excel.Workbook workbook = app.Workbooks.Open(Filename, 0, false, 5, '', '', false, Excel.XlPlatform.xlWindows, '', true, false, 0, true, false, false);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
worksheet.Activate();
app.ActiveWindow.GridlineColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
app.ActiveWindow.DisplayGridlines = true;
worksheet.PageSetup.Draft = false;
workbook.Save();
app.Workbooks.Close();
app.Quit();

Make sure you’ve added a reference to the Office 11.0 Object Library.

That’s it!

Comments\Suggestions always welcome.

Couple of days ago, my Access database (mdb) got corrupted. I kept getting the “Invalid bookmark” error whenever I ran a select against one of my tables. To make it worse, the last time I had backed up my database was 4 days ago, so in other words, in one snap I had nothing to show for my week’s worth of work. That’s when I decided to come up with an automated way of backing up my data because apparently I couldn’t trust myself to do the job regularly.
I looked around for sample VB code or a bat file that would backup my files at regular intervals. But backing up the files wouldn’t be enough, I also wanted to zip them up together so that would a) save space and b) keep them all together. I found lots of snippets that required WinZip or 7-zip (I know it’s free) but I didn’t want to rely on a specific 3rd party program. Finally I found 2 pieces of code here and here which I converted into a VBS file.

Here’s the code:

strDBName = "myAccessDB"
strFolderLocation = "C:\Databases\"
strDate = Right("00" & DatePart("M", Date),2) & Right("00" & DatePart("D", Date),2) & DatePart("YYYY", Date)
strTime = Right("00" & DatePart("H", Time),2) & Right("00" & DatePart("N", Time),2) & Right("00" & DatePart("S", Time),2)
zipfilename = strFolderLocation & strDate & "_" & strAppName & ".zip"
filename = strFolderLocation & strAppName & ".mdb"
Set fso = CreateObject("Scripting.FileSystemObject")
'Zip it only if the MDB file is found
IF fso.FileExists(filename) THEN
'If a file with the same name exists, append Time to the Zip file name.
IF fso.FileExists(zipfilename) THEN
zipfilename = strFolderLocation & strDate & strTime & "_" & strAppName & ".zip"
END IF
strZIPHeader = Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
Set tf = fso.CreateTextFile(zipfilename)
tf.Write(strZIPHeader)
tf.Close()
With CreateObject("Shell.Application")
.NameSpace(zipfilename).CopyHere(filename)
End With
wScript.Sleep 1000
Wscript.Echo(strAppName &  " zipped and saved to " & zipfilename)
ELSE
WScript.Echo("Could not find database: " & filename)
END IF

Finally for the automation part, I set up a Scheduled Task in Windows’ Task Scheduler to execute the VBS file every day at 9pm so my work is backed up a few hours after I have left the office.

WebEx is one of the best web conferencing solutions out there. But it comes at a price. So if you’re looking for the same functionality (or almost the same functionality) for FREE, keep reading. 

1. Dimdim – my personal favorite. Feature-rich and very stable.

2. Yugma -  a close second, comes with a Skype add-in.

3. Yuuguu – Free for use for up to 30 participants! 

4. Mikogo – super-fast, it’s a snap to install. But the yellow giraffe doesn’t look professional when I use it with my clients.

5. Vyew – yet to try this one out for myself, but my friends recommend it highly.

Disclaimer: Above links are direct links to the websites and not affiliate links so I am not making a dime off of this. 

Recently, I created a dashboard which displays, let’s say account balances, by month. I wrote a query that pivoted the data in the format that I wanted (totals by month). My SQL query returned 12 columns, one for each month, but the database contains data for only the past 6 months. Since my 12 columns were fixed or “non-dynamic”, the result was that the GridView showed 6 empty columns.

I googled a lot to remove the null columns but all results referenced the column by name or ID but I had to do this dynamically. Lot of results talked implementing the logic in the GridView_RowDataBound method but after a lot of head-scratching, it hit me! I was trying to fix the problem at the GridView end. Why not deal with the problem when the columns are returned from the database and before the datatable is attached to GridView’s data source? And so that’s what I did. Looped thru each column and subsequently looped thru each row one by one, if any of the rows contain a valid value, do not remove the column. Simple.

Here’s the C# code:

protected void Page_Load(object sender, EventArgs e)
{
GridView GridView1 = new GridView();
DataTable myDataTable = GetDatafromDB();
myDataTable = RemoveNullColumns(myDataTable);
GridView1.DataSource = myDataTable;
GridView1.Databind();
}

private DataTable RemoveNullColumns(DataTable dt)
{
for (int c = 0; dc < dt.Columns.Count; c++)
{
string colName = dt.Columns[c].ColumnName;
bool allRowsempty = true;
for (int r = 0; r < dt.Rows.Count; r++)
{
string tempVal = dt.Rows[r][c].ToString();
if (tempVal != "") //Can also use Convert.DbNull here
{
allRowsempty = false;
}
}
if (allRowsempty)
{
dt.Columns.Remove(colName);
c--;
}
}
return dt;
}

Hope this saves you some time and effort!

I had initially set up this blog on WordPress.com to kick the tires and understand how WordPress worked etc. Once I was comfortable with it, I decided to host my blog at this domain. I would recommend this approach for all new bloggers-to-be out there.

When I searched for “migrate wordpress”, I got a lot of results that talked about downloading plugins that can backup\restore your blog etc. But if your blog is hosted on wordpress.com they are of no use.

So, you have set up your blog at xyzblogname.wordpress.com, written a few posts, tried a few different themes, test-driven a handful plugins and now you’re all set to take off the training wheels.  You have secured the www.xyzblogname.com domain name and installed WordPress and are ready to migrate your blog to its new home.

1. Log in to your WordPress.com blog: www.xyzblogname.wordpress.com\wp-admin\

2. Click on Manage and then Export.

3. Select ‘All Authors’ in the Restrict Author drop-down.

4. Click on Download Export File.

5. Save the xml file somewhere on your computer.

6. Login to the Admin console of your new blog at www.xyzblogname.com/wp-admin/

7. Click on Manage and then Import.

8. From the list of systems, click on WordPress.

9. Click on Browse to select the xml file you saved on Step 5.

10. Click on Upload File and Import and let WordPress handle the rest.

You’re all set!

This is a handy script that I use regularly. You may wonder why I need to truncate all my tables so frequently but that’s a different story.

When I was searching for this script, almost all results had the ‘exec’ command in the loop to truncate the tables as it looped through each. But I modified it slightly and have replaced the ‘exec’ with the ‘print’ command. What I would like the script to do is generate truncate statements for each table which I can review and in case I want to remove a few tables from it, I can comment them out and execute the rest of the block.

So here we go:

————————————————————————

DECLARE @tableName VARCHAR(1024)
DECLARE table_cur CURSOR FOR 
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’

OPEN table_cur

FETCH NEXT FROM table_cur INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT (‘TRUNCATE TABLE ‘+ @tableName)
FETCH NEXT FROM table_cur INTO @tableName
END

CLOSE table_cur
DEALLOCATE table_Cur

————————————————————————

Please feel free to chime in with any suggestions\recommendations!

The Command Prompt is not available on a Windows Mobile device out-of-the-box. So if you want to do some command-line compiling or registering a DLL on the mobile device you’ll have to install the PPC Command Shell Power Toy. You can download it here. Follow the instructions in the ReadMe file to set it up.

PPC Command Prompt

However, a few of my colleagues have come across the following problem:

When you double-click the cmd.exe on the mobile device, it does not launch the Command Prompt window.

The solution was quite simple yet hard to find. Edit the mobile device’s Registry and change the OutputTo DWORD value to “0″ under the HKEY_LOCAL_MACHINE \ Drivers \ Console folder.

Editing the Registry

If you have Visual Studio 2005 installed, you can access the Remote Registry Editor under Visual Studio Remote Tools. Or there are a bunch of Registry Editors out there for mobile devices that you can use e.g. CeRegEditor and PHMRegEditor.

Earlier I wrote about how you could use the FILECTL component to write files on a mobile device.

A few people emailed me to tell me that they were getting the “ActiveX cannot create object CreateObject” error. So this time I’m going to go into more detail about how you first prepare the mobile device before you can use the FILECTL component.

Let’s get to it:

1. Download and copy the regsvrce.exe and MSCEFile.dll to the /Windows folder on the device. There are a couple of different versions of MSCEFile.dll out there, make sure you have the correct one based on your device OS\version.
2. Enable Command Prompt on the device. You can download the PPC Command Shell powertoy to do this.
   NOTE: If you keep double-clicking the cmd.exe and nothing happens, here’s what you do:
   a. Access the Registry and change the “OutputTo” DWORD value to 0 (zero) under HKEY_LOCAL_MACHINE\Drivers\Console.
3. Launch the Command Prompt window, and type “regsvrce.exe MSCEFile.dll” and press Enter.
4. A pop-up message will appear saying the DLL was registered successfully.
5. You’re done!

Now run your VBScript again and it should not throw any CreateObject-related errors.

If you want to write files on a mobile device, this can be easily accomplished using a little VBScript.

I was trying to use the FileSystemObject component to write files. However, the file storage component is managed differently on PocketPC 2002/03, Windows CE 4.2 (and above) devices such as the HP iPAQ, Symbol 9090 etc. Instead of FileSystemObject, the FILECTL component needs to be called. FileCtl is the FSO equivalent for Windows CE.

Following is the VBScript code:

Dim objTextFile, strText
strText = “This is my string of data”

‘ objTextFile.Open needs the following Const values:
‘ ForAppending, fsAccessWrite, fsLockReadWrite
Const ForAppending = 8
Const fsAccessWrite = 2
Const fsLockReadWrite = 0

Const objFilePath = “\Temp\MyFile.txt”

Set objTextFile = CreateObject(“FILECTL.File”)
objTextFile.Open objFilePath, ForAppending, fsAccessWrite, fsLockReadWrite

‘ Writes strText every time you run this VBScript
objTextFile.LinePrint(strText)

objTextFile.Close
set objTextFile = nothing

For reference, you can go to:  http://msdn2.microsoft.com/en-us/library/ms860108.aspx

If you know of a better way accomplishing this, feel free to share!