Google Maps API

Ok, so I started a project for work in ASP/C#: verifying peoples' addresses and finding our closest facility to them. I ran into a few little problems here and there, so I thought this would make for an interesting post.

The Google Maps API seems to be pretty handy for anything you might want to display or look up on a map for your web pages. It allows you to center the map to any point you would like, set markers anywhere, add shapes and images to the map, and even allows for transparent overlays.

Now, everything on the web page we are designing is strictly in code, there is no ASP scripting involved, which added some interesting twists to this project which would normally be avoided, but I ran into a couple of snags while working on this project:

• Google Map doesn't automatically display when made from the C# code.


• Google Map disappears when placed in a panel after each postback.


• Google Map has a gray border.


• The Google Maps API key was registered for a different web site.

The first thing you have to do in order for any part of the Map API to work is to make the following link, using your own key, not the Google key:

<script src="http://maps.google.com/maps?file=api&amp;v=2.x&amp;key=ABQIAAAAzr2EBOXUKnm_jVnk0OJI7xSosDVG8KKPE1-m51RBrvYughuyMxQ-i1QfUnH94QxWIa6N4U6MouMmBA" type="text/javascript"></script>

The Google Map is made by creating a div tag and setting an id for it, and then passing that div id to the GMap2 object in JavaScript, which places a map in the div.

var map = null;

function initialize()
{
    if (GBrowserIsCompatible())
    {
        map = new GMap2(document.getElementById('map_div'));
    }
}

Now, you make sure that you run the "initialize" function in your body's onload event, and the map will display in the div. If you make this div as a "LiteralControl" or any other server side code, and the div is inside an UpdatePanel, the onload event fires and runs "initialize" without ever showing the map, and you need to add the following line of code after the div is created to make it display from the start (replace <unique id> with a proper element id):

ScriptManager.RegisterClientScriptBlock(Page, typeof(System.Type), "<unique id>", "initialize();", true);

Now, if all you ever do is show the map, and never center it or anything else, all you need to do is to make sure that the line above gets run with every postback. However, if you want markers set, or want the map centered in a specific spot, you'll need to add a new function to your JavaScript, and a couple new global variables. To center the map on a specific location:

map.setCenter(new GLatLng(37.4419, -122.1419), 13);

Easy, all you need to do is pass a new latitude and longitude to the setCenter method. The last number (13) is the zoom level; the higher the number, the closer the zoom.

So at this point, we can add a marker to the map, to show pinpoint the above location. This can happen on a button being pressed, or any event, but you need this code somewhere to be executed in your JavaScript:

map.addOverlay(new GMarker(new GLatLng(37.4419, -122.1419)));

Here you have your map on the page, centered over the Google office (yeah, I used their example lat/long), and a big red marker placed on their building. You click an element on your page that causes a postback, and the map disappears!

Now when this first happened to me, I freaked out, chewed off all my nails, and called my shrink. Not quite, but it was frustrating to see it all nice and pretty, and then just go away. So I started searching, and pretty soon found a way to fix it. I'm not going to go into the details of why this happens, as this could take several more posts, but I'll tell you how to fix it.

If you've read the post from top to bottom, then you remember seeing this line:

ScriptManager.RegisterClientScriptBlock(Page, typeof(System.Type), "<unique id>", "initialize();", true);

And now, we're going to utilize a similar line to make the map display again. Firstly, if you have markers, you have to save them somewhere: in varibles, a CSV file, hidden text fields, I don't care, but you will lose them on every postback if you don't save them on the client. So we need to add the following code to our JavaScript:

// It's up to you to fill "markers", I'm setting it null
// Use it as an array, and store GLatLng's in it
var markers = null;

function reInitialize()
{
    initialize();

    for (var i = 0; i < markers.length; i++)
    {
        map.addOverlay(new GMarker(markers[i]));
    }
}

And add the following line to your C# (best place is up to you, replace <unique id> with a proper element id):

ScriptManager.RegisterClientScriptBlock(Page, typeof(System.Type), "<unique id>", "reInitialize();", true);

Suddenly, your map is back, with all it's markers, centered where you had it, and looking all pretty again.

The last little thing I noticed with the map, is that when you add it to a page from code, IE8 doesn't always display it properly. If while the UpdatePanel is still loading, your map div gets moved, the map's "center" is the original center of the div, not based on the new location. If you see this occurring, the simplest way to fix it is just to wait until the page is loaded to initialize the map. This can be done using our ScriptManager line again, but modified as shown in the first example below:

ScriptManager.RegisterClientScriptBlock(Page, typeof(System.Type), "<unique id>", "setTimeout('initialize();', 1500);", true);

Rather than as it was shown in the first of this post:

ScriptManager.RegisterClientScriptBlock(Page, typeof(System.Type), "<unique id>", "initialize();", true);

Note that the new version of this line has a timeout set on the "initialize" function. You simply need to set the timeout to a sufficient amount of time for all the elements on your page to finish loading.

This one is a short one, but one that reared it's ugly head just after releasing all this code that we worked so hard on to an actual server. We had been running the site on the development machines (localhost), and when development was done, we went to release to a QA environment for further testing. Suddenly, our beautiful map stopped working! We got an error stating "The Google Maps API key used on this web site was registered for a different web site. You can generate a new key for this web site at http://code.google.com/apis/maps." So, i went and re-registered the key, copied it and pasted it into place, and still got the same error.

Finally, I came across a post about using Microsoft FrontPage with the Google Maps API, and problems with the "&amp;"s in the link source. FrontPage will apparently (trying to help you out and all) translate the "&amp;" to "&amp;amp;". Now I knew this wasn't my problem because I was using C# to output the link, but C# uses literal translation when outputting strings.

Therefore, I had to replace the string as shown above:

<script src="http://maps.google.com/maps?file=api&amp;v=2.x&amp;key=ABQIAAAAzr2EBOXUKnm_jVnk0OJI7xSosDVG8KKPE1-m51RBrvYughuyMxQ-i1QfUnH94QxWIa6N4U6MouMmBA" type="text/javascript"></script>

with the following string:

<script src="http://maps.google.com/maps?file=api&v=2.x&key=ABQIAAAAzr2EBOXUKnm_jVnk0OJI7xSosDVG8KKPE1-m51RBrvYughuyMxQ-i1QfUnH94QxWIa6N4U6MouMmBA" type="text/javascript"></script>

And once this was changed, my map suddenly started to work on the server the way it should. Hope this helps.

Feel free to comment on this if you have any additional information or questions. I have tried my best to explain in detail without making this post too long, or simply boring you into leaving, and I hope that has been sufficient. Happy Mapping!

I'm Back! + Woot Auto Buy 3 on Random Crap

It's been a while since I've posted, and while I've been gone, I've done a few interesting things, and will be posting more bits and pieces of code here now. Sorry to anyone who was hoping I was a stable blogger, and would be posting more regularly, and I will try to do better as time goes on. So now, I'm going to post one piece I spent some time on: The Woot Auto Buy 3 on Random Crap.

This script can be found and installed on Userscripts.org, using the Firefox web browser, and the Greasemonkey add-on. It will automatically click the "I want 1" button, upgrade to 3, and fill out the information on the purchase page. It doesn't yet work with PayPal, but when I make that upgrade, I will also update this post. You need to be signed in to Woot.com for this script to function properly. Enjoy!


Edit (28 Oct, 2009 14:18 GMT-0600): I'm keeping the newest version number of this script on this page now, since the script has now had 1500+ installs!

Current version:

0.20

//
// by njkrut (njkrut{at}gmail.com)
// heavily modified by (md5sum{at}yahoo.com)
// ==UserScript==
// @name Woot.com Auto Refresh and Buy 3 on Random Crap
// @namespace http://nikru.com
// @description Auto Refreshes during WootOff, then Buys 3 items on BoCs
// @include https://www.woot.com/Member/Order.aspx
// @include http://www.woot.com/
// ==/UserScript==
var doMyPostBack = function()
{
   if(theForm && wantthreebutton)
   {
       eventTarget.value = 'ctl00$ContentPlaceHolder$ShoppingCartControl$WantedThreeButton';
       eventArgument.value = '';
       theForm.submit();
   } else {
       alert('Could Not Select Three for Unknown Reasons');
   }
}

var pageItem = document.getElementById('TitleHeader');
var wantOne = document.getElementById('ctl00_ContentPlaceHolder_OrderButton');
var wantOne2 = document.getElementById('ctl00_ContentPlaceHolder_OrderButton2');
var eventTarget = document.getElementById('__EVENTTARGET');
var eventArgument = document.getElementById('__EVENTARGUMENT');
var item = document.getElementById('ctl00_ContentPlaceHolder_ShoppingCartControl_SaleTitleLabel');
var wantthreebutton = document.getElementById('ctl00_ContentPlaceHolder_ShoppingCartControl_WantedThreeButton');
var theForm = document.getElementById('aspnetForm');
var cardSecurity = document.getElementById('ctl00_ContentPlaceHolder_SecurityCodeTextBox');

if (document.location == "http://www.woot.com/" && document.getElementById("PriceSpan").innerHTML == "$0.99" && pageItem.innerHTML.substring(0,11) == "Random Crap")
{
   try{
       document.location = "http://www.woot.com/" + wantOne.getAttribute("href");
   }catch(a){
       try{
           document.location = "http://www.woot.com/" + wantOne2.getAttribute("href");
       }catch(e){
           alert(e);
       }
   }
}else{
   if (document.location == "http://www.woot.com/" && document.getElementById("ctl00_ContentPlaceHolder_WootOffPanel"))
   {
       setTimeout("document.location = document.location", 30000);
   }
}

if (item.innerHTML.substring(0, 11) == "Random Crap" && wantthreebutton)
{
   doMyPostBack();
}else{
   if (item.innerHTML.substring(0, 11) == "Random Crap")
   {
       cardSecurity.value = '';
       if (cardSecurity.value != '')
       {
           WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ContentPlaceHolder$BuyButton", "", true, "", "", false, false));
       }
   }
}

SQL Stored Procedure Decryptor

This script will decrypt an encrypted stored procedure in your database. Please note that in order to use this stored procedure, you must be connected as a remote admin. In order to do this, you must first enable remote admin connections by running this:

sp_configure 'remote admin connections', 1 ;
GO

RECONFIGURE ;
GO

After that, you need to disconnect ONLY YOUR QUERY window, not object explorer. Object explorer will not connect as remote admin. Also, you will kick out any existing connections to your database when you connect as remote admin, so do not do this on a production box. To connect as remote admin, connect your query window again, but type "ADMIN:" before the server name (minus the quotes, of course), and connect using sa or an administrator account.

Then, you can edit the <catalog>, <schema>, and <object> areas (one of each) in the following query to decrypt a stored procedure.

I take absolutely NO credit for writing this, I found it on a website, and I don't remember where, but there were no credits in it where I found it.

USE [<catalog>]
GO

DECLARE @procedure sysname
SELECT@procedure = '<schema>.<object>'
DECLARE @intProcSpace bigint
DECLARE @t bigint
DECLARE @maxColID smallint
DECLARE @intEncrypted tinyint
DECLARE @procNameLength int

DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max)
DECLARE @real_decrypt_01a nvarchar(max)

create table #output
(
[ident] [int] IDENTITY(1, 1)
NOT NULL
,[real_decrypt] NVARCHAR(MAX)
)

SELECT
@maxColID = max(subobjid)
FROM
sys.sysobjvalues
WHERE
objid = object_id(@procedure)

select
@procNameLength = datalength(@procedure) + 29

select
@real_decrypt_01a = ''

SET @real_01 = (
SELECT
imageval
FROM
sys.sysobjvalues
WHERE
objid = object_id(@procedure)
and valclass = 1
and subobjid = 1
)

BEGIN TRAN

SET @fake_01 = 'ALTER PROCEDURE ' + @procedure + ' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 40003 - @procNameLength)

EXECUTE (@fake_01)

SET @fake_encrypt_01 = (
SELECT
imageval
FROM
sys.sysobjvalues
WHERE
objid = object_id(@procedure)
and valclass = 1
and subobjid = 1
)

SET @fake_01 = 'CREATE PROCEDURE ' + @procedure + ' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 40003 - @procNameLength)

SET @intProcSpace = 1


SET @real_decrypt_01 = replicate(cast(N'A' as nvarchar(max)), (datalength(@real_01) / 2))

SET @intProcSpace = 1

WHILE @intProcSpace <= (datalength(@real_01) / 2)
BEGIN
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1, NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^ (UNICODE(substring(@fake_01, @intProcSpace, 1)) ^ UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace = @intProcSpace + 1
END

insert #output (real_decrypt) select @real_decrypt_01
declare @dbname sysname
declare @BlankSpaceAdded int
declare @BasePos int
declare @CurrentPos int
declare @TextLength int
declare @LineId int
declare @AddOnLen int
declare @LFCR int
declare @DefinedLength int
declare @SyscomText nvarchar(max)
declare @Line nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0

CREATE TABLE #CommentText ( LineId int ,Text nvarchar(255) collate database_default )

DECLARE ms_crs_syscom CURSOR LOCAL FOR
SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY

SELECT @LFCR = 2
SELECT @LineId = 1

OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

SELECT
@BasePos = 1
SELECT
@CurrentPos = 1
SELECT
@TextLength = LEN(@SyscomText)

WHILE @CurrentPos != 0
BEGIN

SELECT
@CurrentPos = CHARINDEX(char(13) + char(10), @SyscomText, @BasePos)


IF @CurrentPos != 0
BEGIN

While (isnull(LEN(@Line), 0) + @BlankSpaceAdded + @CurrentPos - @BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT
@AddOnLen = @DefinedLength - (isnull(LEN(@Line), 0) + @BlankSpaceAdded)

INSERT
#CommentText
VALUES
(
@LineId
,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')
)
SELECT
@Line = NULL
,@LineId = @LineId + 1
,@BasePos = @BasePos + @AddOnLen
,@BlankSpaceAdded = 0
END

SELECT
@Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos - @BasePos + @LFCR), N'')
SELECT
@BasePos = @CurrentPos + 2

INSERT
#CommentText
VALUES
(@LineId, @Line)
SELECT
@LineId = @LineId + 1

SELECT
@Line = NULL
END
ELSE
BEGIN
IF @BasePos <= @TextLength BEGIN

While (isnull(LEN(@Line), 0) + @BlankSpaceAdded + @TextLength - @BasePos + 1) > @DefinedLength
BEGIN
SELECT
@AddOnLen = @DefinedLength - (isnull(LEN(@Line), 0) + @BlankSpaceAdded)

INSERT
#CommentText
VALUES
(
@LineId
,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')
)
SELECT
@Line = NULL
,@LineId = @LineId + 1
,@BasePos = @BasePos + @AddOnLen
,@BlankSpaceAdded = 0
END

SELECT
@Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength - @BasePos + 1), N'')

if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength + 1) > 0
BEGIN
SELECT
@Line = @Line + ' '
,@BlankSpaceAdded = 1
END
END
END
END

FETCH NEXT FROM ms_crs_syscom into @SyscomText
END


IF @Line is NOT NULL
INSERT
#CommentText
VALUES
(@LineId, @Line)

select
Text
from
#CommentText
order by
LineId

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText

ROLLBACK TRAN
DROP TABLE #output

GO

Auditing Your Tables

I wrote this script to use at work to create triggers on all of the tables here at work so that we can audit changes. I have modified it slightly to include the Create/Alter of the audit table. Soon I will be posting queries that will allow you to easily view the information in the audits. Please note that if you want things translated in your audit table, you can do lookups in the triggers, but this will require a manual change. So, without further anticipation:

--******************************************************--
--* Author: Nathan Wheeler *--
--* Date: 3/12/08 *--
--* Description: This query will look through each *--
--* table in the used catalog and create an auditing *--
--* trigger for each table with a primary key. *--
--* It will also create the table in which to place *--
--* the audit entries. *--
--* Licensing: Feel free to use this script to create *--
--* audits on all your catalogs, just don't give it *--
--* away without this notice. *--
--* *--
--* For more stuff like this, visit: *--
--* http://maxaffinity.blogspot.com *--
--******************************************************--
--Insert catalog name here.
USE [<catalog>]
GO
SET NOCOUNT ON
--If the AuditTable is there alter it, otherwise create it.
IF OBJECT_ID(('AuditTable'), 'U') IS NOT NULL
PRINT 'CREATE TABLE [dbo].[AuditTable]'
ELSE
PRINT 'ALTER TABLE [dbo].[AuditTable]'
PRINT ' ('
PRINT ' [AuditID] [int] IDENTITY(1, 1)'
PRINT ' NOT NULL'
PRINT ' ,[TableName] [varchar](50) NOT NULL'
PRINT ' ,[KeyName] [varchar](50) NOT NULL'
PRINT ' ,[KeyValue] [varchar](50) NOT NULL'
PRINT ' ,[FieldName] [varchar](50) NOT NULL'
PRINT ' ,[FieldOldValue] [varchar](8000) NULL'
PRINT ' ,[FieldNewValue] [varchar](8000) NULL'
PRINT ' ,[ModifiedBy] [varchar](75) NOT NULL'
PRINT ' ,[ModifiedDate] [datetime] NOT NULL'
PRINT ' ,CONSTRAINT [PK_AuditTable] PRIMARY KEY CLUSTERED ([AuditID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
PRINT ' )'
PRINT 'ON [PRIMARY]'
PRINT 'GO'
DECLARE
@TableName VARCHAR(255)
,@KeyName VARCHAR(255)
,@Schema VARCHAR(255)
,@TriggerName VARCHAR(255)
,@FieldName VARCHAR(255)
DECLARE table_cursor CURSOR
FOR SELECT
KU.TABLE_NAME
,KU.COLUMN_NAME
,KU.CONSTRAINT_SCHEMA + '.'
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
WHERE
--In the "NOT IN" is where you can list any tables you DON'T want to audit.
--Be sure to put at least the AuditTable in here.
KU.TABLE_NAME NOT IN ('AuditTable')
ORDER BY KU.TABLE_NAME
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName, @KeyName, @Schema
WHILE @@FETCH_STATUS = 0
BEGIN
--The trigger will be the name of the table prefixed be "audittr_".
--This can be changed by setting @TriggerName to something different.
SET @TriggerName = @Schema + 'audittr_' + @TableName
DECLARE field_cursor CURSOR
FOR SELECT
c.name
FROM
sysobjects o
INNER JOIN syscolumns c
ON o.id = c.id
WHERE
o.name = @TableName
ORDER BY
o.name
,c.colorder
--If the trigger already exists, alter it instead of create.
IF OBJECT_ID((@TriggerName), 'TR') IS NOT NULL
PRINT 'ALTER TRIGGER ' + @TriggerName
ELSE
PRINT 'CREATE TRIGGER ' + @TriggerName
PRINT ' ON ' + @Schema + @TableName
PRINT ' AFTER UPDATE'
PRINT 'AS '
PRINT ''
PRINT ' INSERT INTO AuditTable'
OPEN field_cursor
--Get the first field name.
FETCH NEXT FROM field_cursor INTO @FieldName
PRINT ' SELECT ' + QUOTENAME(@TableName, '''') + ' AS TableName,'
PRINT ' ' + QUOTENAME(@KeyName, '''') + ' AS KeyName,'
PRINT ' INSERTED.' + @KeyName + ' AS KeyValue,'
PRINT ' ' + QUOTENAME(@FieldName, '''') + ' AS FieldName,'
PRINT ' CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)) AS FieldOldValue,'
PRINT ' CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX)) AS FieldNewValue,'
PRINT ' SYSTEM_USER AS ModifiedBy,'
PRINT ' getdate() AS ModifiedDate'
PRINT ' FROM INSERTED'
PRINT ' INNER JOIN DELETED'
PRINT ' ON INSERTED.' + @KeyName + ' = DELETED.' + @KeyName
PRINT ' WHERE CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)) <> CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX))'
--If there is another field (can't see why you'd want just one)
--get the next field name.
FETCH NEXT FROM field_cursor INTO @FieldName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' UNION ALL'
PRINT ' SELECT ' + QUOTENAME(@TableName, '''') + ','
PRINT ' ' + QUOTENAME(@KeyName, '''') + ','
PRINT ' INSERTED.' + @KeyName + ','
PRINT ' ' + QUOTENAME(@FieldName, '''') + ','
PRINT ' CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)),'
PRINT ' CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX)),'
PRINT ' SYSTEM_USER,'
PRINT ' getdate()'
PRINT ' FROM INSERTED'
PRINT ' INNER JOIN DELETED'
PRINT ' ON INSERTED.' + @KeyName + ' = DELETED.' + @KeyName
PRINT ' WHERE CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)) <> CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX))'
-- Get the next field.
FETCH NEXT FROM field_cursor INTO @FieldName
END
PRINT 'GO'
--Close and deallocate the field cursor for the next table.
CLOSE field_cursor
DEALLOCATE field_cursor
--Get the next table.
FETCH NEXT FROM table_cursor INTO @TableName, @KeyName, @Schema
END
--Close and deallocate the table cursor.
CLOSE table_cursor
DEALLOCATE table_cursor

Welcome

Welcome to MaxAffinity where youre programming affinity can be maximized through the use of code snippets and tips from some of the top developers in the country.

Actually most of that is complete bullshit, but there is going to be a lot of neat code snippets, hardware tips and tricks, and a lot more. The real big difference is that it's not from some of the top developers in the world, it's just me. Sorry to disappoint. Enjoy the blog.