Category Archives: Web development

Programming, CSS, and general web stuff.

Google Analytics Core Reporting API (Data Export) with Google Chart Visualizations

Updated 12/20/11 and 01/04/12 to reflect Google’s API changes

You can punch into the Google Analytics Core Reporting (Data Export) API, pull out some stats, and stuff them into some nice graphical charts using Google Chart Visualizations. This demo is done in PHP.
Google Chart Visualizations

Authenticate the User

The user can authenticate via the ClientLogin or using the AuthSub login which is actually more secure. For the ClientLogin, a typical username/password form is used. For the AuthSub login a link is used to send the user to Google to log in. Both are shown below. Normally you would use one or the other.

* Use ClientLogin ONLY for installed apps and not web apps

<form name="loginForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
		    <label for="email">Gmail:</label>
		    <input id="email" type="text" name="email" />
		    <label for="password">Password:</label>
		    <input type="password" name="password" id="password"/>
		    <br /><br />
		    <button type="submit" id="submitLogin">Submit</button>
		</form>
        <p><a class="button" href="https://www.google.com/accounts/AuthSubRequest?next=http://your-return-url&scope=https://www.googleapis.com/auth/analytics.readonly&secure=0&session=1">Or,authenticate using AuthSub through Google.</a></p>

And, of course, the two authentication methods use different http calls to return a token that can be used to access the API.

//ClientLogin: try to log in and get session token for multiple API calls
if(isset($_POST['email']) && isset($_POST['password'])){
	$_SESSION['sessionToken'] = googleLogin($_POST['email'],$_POST['password']);
}
//AuthSub: exchange token for session token so multiple calls can be made to api
if(isset($_REQUEST['token'])){
	$_SESSION['authSub'] = true;
	$_SESSION['sessionToken'] = get_session_token($_REQUEST['token']);
}

//returns sessionToken for multiple calls to API
function googleLogin($email,$passwd){

    $clientlogin_url = "https://www.google.com/accounts/ClientLogin";
     $clientlogin_post = array(
    "accountType" => "GOOGLE",
    "Email" => $email,
    "Passwd" => $passwd,
    "service" => "analytics",
    "source" => "my-analytics"
	);

	$curl = curl_init($clientlogin_url);

	curl_setopt($curl, CURLOPT_POST, true);
	curl_setopt($curl, CURLOPT_POSTFIELDS, $clientlogin_post);
	curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
	curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);

	$response = curl_exec($curl);

	preg_match("/Auth=([a-z0-9_\-]+)/i", $response, $matches);
	$sessionToken = $matches[1];

	if (strlen($sessionToken) == 0){
		$sessionToken = "Authentication Failed.";
	}

 	return $sessionToken;
}

//AuthSub returns session token for multiple calls to API
	function get_session_token($onetimetoken) {
		$output = call_api($onetimetoken, "https://www.google.com/accounts/AuthSubSessionToken");

		if (preg_match("/Token=(.*)/", $output, $matches))
		{
			$sessionToken = $matches[1];
		} else {
			echo "Error authenticating with Google.";
			exit;
		}

		return $sessionToken;
	}

Data Requests

Once authenticated to a Google Analytics account and a multi-use session token is acquired, the data requests can be made. The first one will request the profiles (websites) associated with the account. If there is more than one, a dropdown select is populated allowing for the selection of the profile from which to pull data.

The key is added to the request per this post by Google.

$accountxml = call_api($_SESSION['sessionToken'],"https://www.googleapis.com/analytics/v2.4/management/accounts/~all/webproperties/~all/profiles?key=Axxxxxxxxxxxxxxxxxx4");
// Get an array with the available accounts
$profiles = parse_account_list($accountxml);

The call_api function is going to return the XML data from Google based on the request URL sent in. In this case, it is getting the profile data and the parse_account_list function is rolling through that XML and putting the profile data in an array.

//gets the data
function call_api($sessionToken,$url){
	$curl = curl_init($url);

	curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
	if (isset($_SESSION['authSub'])){
		$curlheader[0] = sprintf("Authorization: AuthSub token=\"%s\"/n", $sessionToken);
	} else {
		$curlheader[0] = "Authorization: GoogleLogin auth=" . $sessionToken;
	}
	curl_setopt($curl, CURLOPT_HTTPHEADER, $curlheader);

	$response = curl_exec($curl);
	curl_close($curl);

	return $response;
}

//returns accounts list as array
function parse_account_list($xml){
	$doc = new DOMDocument();
	if(stripos($xml,"<") !== FALSE)
	{
		$doc->loadXML($xml);

		$entries = $doc->getElementsByTagName('entry');
		$i = 0;
		$profiles= array();
		foreach($entries as $entry)
		{
			$profiles[$i] = array();

			$properties = $entry->getElementsByTagName('property');
			foreach($properties as $property)
			{
				if (strcmp($property->getAttribute('name'), 'ga:accountId') == 0)
					$profiles[$i]["accountId"] = $property->getAttribute('value');

				if (strcmp($property->getAttribute('name'), 'ga:profileName') == 0)
					$profiles[$i]["title"] = $property->getAttribute('value');

				if (strcmp($property->getAttribute('name'), 'dxp:tableId') == 0)
					$profiles[$i]["tableId"] = $property->getAttribute('value');
			}

			$i++;
		}

		return $profiles;
	} else {
		$sessionToken = "Authentication Failed.";
	}
}

The dropdown of the profile array:

echo "<form name='siteSelect' id='siteSelect' method='post' action='" . $_SERVER['PHP_SELF'] . "'><p><label for='tableId'>Select Site:</label><select name='tableId' id='tableId'>";
		foreach($profiles as $profile)
		{
			if($profile["tableId"] == $table_Id)
				$selected = "selected='selected'";
				echo "<option value='" . $profile["tableId"] . "|" . $profile["title"] . "'" . $selected  . ">" . $profile["title"] . "</option>";
				$selected = " ";
		}
		echo "</select></p>";

// set $tableId
if(isset($_POST['tableId'])){
    $tableId = substr($_POST['tableId'],0,strpos($_POST['tableId'],"|"));
}

The parse_data function below is going to roll through the data returned from Google Analytics and spit out an array that can be used to create the Google Visualization graphs.

//returns data as array
function parse_data($xml){
		$doc = new DOMDocument();
		$doc->loadXML($xml);

		$entries = $doc->getElementsByTagName('entry');
		$i = 0;
		$results = array();
		foreach($entries as $entry)
		{
			$dimensions = $entry->getElementsByTagName('dimension');
			foreach($dimensions as $dimension)
			{
				$results[$i][ltrim($dimension->getAttribute("name"),"ga:")] =  $dimension->getAttribute('value');
			}

			$metrics = $entry->getElementsByTagName('metric');
			foreach($metrics as $metric)
			{
				$results[$i][ltrim($metric->getAttribute('name'),"ga:")] =  $metric->getAttribute('value');
			}

			$i++;
		}
		return $results;
}

Graph Generation

Request the data

// For each website, get referrals
$requrl = sprintf("https://www.googleapis.com/analytics/v2.4/data?ids=%s&dimensions=ga:source&metrics=ga:visits&filters=ga:medium==referral&start-date=%s&end-date=%s&sort=-ga:visits&max-results=10",$tableId,$start_date,$end_date);

$referralsxml = call_api($_SESSION['sessionToken'],$requrl);
$referrers = parse_data($referralsxml);

$requrlvisitsgraph = sprintf("https://www.googleapis.com/analytics/v2.4/data?ids=%s&dimensions=ga:date&metrics=ga:visits&start-date=%s&end-date=%s",$tableId,$start_date,$end_date);
$visitsgraphxml = call_api($_SESSION['sessionToken'],$requrlvisitsgraph);
$visitsgraph = parse_data($visitsgraphxml);

Google Visualizations requires the inclusion of a javascript file in the head tag and empty div’s that will be the target for the graphs:

<script type="text/javascript" src="//www.google.com/jsapi"></script>

The target div’s should be placed on the page where you want them to appear.

<div id='barchart_div'></div>
<div id='piechart_div'></div>

Finally, the data can be added to the chart generation javascript:

<script>
function drawPieChart() {
	var data = new google.visualization.DataTable();
    data.addColumn('string', 'Referrer');
    data.addColumn('number', 'Visits');
    data.addRows(<?php echo sizeof($referrers) ?>);
    <?php
    $row = 0;
    foreach($referrers as $referrer)
		{
	?>
	data.setValue(<?php echo $row ?>,0,'<?php echo $referrer["source"] ?>');
	data.setValue(<?php echo $row ?>,1,<?php echo $referrer["visits"] ?>);
	<?php
	$row++;
	}
	?>

	var chart = new google.visualization.PieChart(document.getElementById('piechart_div'));
    chart.draw(data, {width: 600, height: 440, is3D: true, title: 'Referrer/Visits'});
}

function drawBarChart() {
	var data = new google.visualization.DataTable();
    data.addColumn('string', 'Day');
    data.addColumn('number', 'Visits');
    data.addRows(<?php echo sizeof($visitsgraph) ?>);
	<?php
    $row = 0;
    foreach($visitsgraph as $visits)
		{
	?>
		data.setValue(<?php echo $row ?>,0,'<?php if ($visits_graph_type === "month"){echo date("M", mktime(0, 0, 0, $visits["month"]))." ".$visits["year"];}else{echo substr($visits['date'],6,2)."-".date('M', mktime(0, 0, 0, substr($visits['date'],4,2)))."-".substr($visits['date'],0,4);} ?>');
		data.setValue(<?php echo $row ?>,1,<?php echo $visits["visits"] ?>);
		<?php
		$row++;
		}
		?>
    var chart = new google.visualization.ColumnChart(document.getElementById('barchart_div'));
    chart.draw(data, {'width': 700, 'height': 400, 'is3D': true, 'title': 'Visits'});
}

google.load("visualization", "1.0", {packages:["corechart"]});
google.setOnLoadCallback(drawPieChart);
google.setOnLoadCallback(drawBarChart);

</script>

Recommended

Demo

Download code

ColdFusion Session Timeout with Warning and jQuery Session Refresh

There are times when a user needs to sit on a page for a while either to read or fill out a long form. If their visit is controlled by a session timeout, for example a member site, then the session needs to be refreshed without refreshing the page. My previous post on user session warning used a page refresh to renew the session. This example will refresh the session with a jquery .post so the browser maintains state.

Much of what happens here is very similar to the previous page reload session refresh so you will see some of the same explanatory text. Changes to the code mainly affect the jquery/javascript since the variables and timers need to be reset without refreshing the page.

Javascript is needed to keep track of the time the user has been sitting on the page. The server does not know how long they have been sitting there. It only knows whether or not a request comes in during a session or after the session has expired and acts accordingly at that time. Too late for a warning.

Session Defined: Start to Finish

A session is defined as when a user begins and ends using or visiting a web site. It can be unlimited in length or strictly defined by a timeout period. If the site requires a log in or accesses sensitive data, it should time out after a period of inactivity. They can end a session by logging out or closing the browser.

Inactivity means the user has done nothing, made no requests of the web server, during a specified time. Ajax requests usually do not count.

Demo

The session time left is determined by the server, and, if you want to poll the server with an Ajax request, go for it. Javascript is used to keep track of the time left in the session.

The demo uses a simple log in with session timing handled by jquery and javascript. When the session expiration approaches, the user is warned and given an opportunity to restart the session. If the session time limit is reached, the user is prompted to log in again. If they ignore that prompt, the page automatically redirects to the log in form. In the demo this sequence of events takes 40 seconds to complete and is broken down as follows:

  1. Session timeout: 30 seconds
  2. Timeout warning: 20 seconds
  3. Session expired warning: 10 seconds
  4. Redirect to log in page: 10 seconds

Interrupting the User

The user’s attention can be diverted away from other open windows to the eminent session expiration by using a javascript alert in place of the jquery dialog box. Personal preference.

Code Breakdown

The application.cfc controls the session by creating non-persistent cookies for CFID and CFTOKEN so the session expires when the user’s browser closes. It also sets the session variable sessionStartTime. The sessionStartTime variable is used to illustrate the fact that the application.cfc function OnSessionStart only fires once. It does not fire every time a session is renewed or restarted.

RequestStartTime is set in the OnRequestStart function to provide a reference for comparison later. See “Just for Fun” section below.

<cfcomponent
    displayname="Application"
    output="false"
    hint="Handle the application.">

    <!--- Set up the application. --->
    <cfset THIS.Name = "sessionrefreshtest" />
    <cfset THIS.ApplicationTimeout = CreateTimeSpan(0,1,0,0) />
    <!--- CreateTimeSpan(days, hours, minutes, seconds) --->
    <cfset THIS.SessionTimeout = CreateTimeSpan(0,0,0,30) />
    <cfset THIS.SessionManagement = true />
    <cfset THIS.SetClientCookies = false />

    <cffunction
        name="OnSessionStart"
        access="public"
        returntype="void"
        output="false"
        hint="Fires ONLY ONCE when session first created and not when session renewed/restarted.">       

        <!---set cfid/cftoken as non-persistent cookies so session ends on browser close --->
        <cfif not IsDefined("Cookie.CFID")>
            <cflock scope="session" type="readonly" timeout="5">
                <cfcookie name="CFID" value="#session.CFID#">
                <cfcookie name="CFTOKEN" value="#session.CFTOKEN#">
                 <cfset session.SessionStartTime = Now() />
            </cflock>
        </cfif>

        <cfreturn />
    </cffunction>

    <cffunction
        name="OnRequestStart"
        access="public"
        returntype="boolean"
        output="true"
        hint="Fires at first part of page processing.">

        <!--- Define arguments. --->
        <cfargument
            name="TargetPage"
            type="string"
            required="true"
            />

        <cfset session.RequestStartTime = Now() />

        <cfreturn true />

    </cffunction>    

</cfcomponent>

The log in page checks for a query string variable called ‘expired’ and, if present, deletes the session loggedin variable. This is there because the code is going to control the expiration of the session eliminating the need to compensate for browser latency. The actual session start time the time the page loads can differ by several seconds. To avoid having to add time to the session or any other fancy guesswork, when the allotted session time has expired according to the javascript timer on the page, they are done – session over.

If they are logged in, they get bumped to the index page. The rest is the logic that handles the log in form.

Note: I would not recommend handling a log in form this way. This is for demonstration only.

<cfif isDefined("url.expired") AND url.expired>
    <cfset StructDelete(session,"loggedin") />
</cfif>

<cfif isDefined("form.username") AND isDefined("form.pw") AND form.username EQ "session" AND form.pw EQ "test">
    <cfset session.loggedin = true />
</cfif>

<cfif StructKeyExists(session, "loggedin") AND session.loggedin>
    <cflocation url="index.cfm" addToken="no" />
</cfif>

Other than the log in form and a message for the user, that’s all there is to the log in page.

Handling Session Timeout

The index page handles the session timeout code. This could be a separate javascript included in every page. The first block simply determines if they are logged in. If they are not, send them to the login page. If they are, load the index page.

<!---if not logged in, send them to login page, else load the index page--->
<cfif NOT StructKeyExists(session, "loggedin") OR NOT session.loggedin>
    <cflocation url="login.cfm" addToken="no" />
<cfelse>
 <!---Load the page --->
</cfif>

Now the time variables are set and the a javascript timer is set to check the session every 10 seconds.
Javascript uses milliseconds so for clarity the time intervals multiply the number of seconds by 1,000. You could put 10000 in for 10 seconds but I think 10*1000 helps me determine that it is 10 seconds quite a bit faster. Do what is comfortable for you.

Also, a flag is set to determine if the warning dialog box has been opened and the countdown has begun.

//Your timing variables in number of seconds
//total length of session in seconds
var sessionLength = 30;
//time warning shown (10 = warning box shown 10 seconds before session starts)
var warning = 10;
//time redirect forced (10 = redirect forced 10 seconds after session ends)
var forceRedirect = 10; 

$(document).ready(function() {
	//event to check session time left (times 1000 to convert seconds to milliseconds)
    checkSessionTimeEvent = setInterval("checkSessionTime(requestTime)",10*1000);
});

//event to check session time variable declaration
var checkSessionTimeEvent = "";

//time session started
var requestTime = new Date();

//initial set of number of seconds to count down from for countdown ticker (10,9,8,7...you get the idea)
var countdownTime = warning;
//create event to start/stop countdownTicker
var countdownTickerEvent = ""; 

//initially set to false. if true - warning dialog open; countdown underway
var warningStarted = false;

function checkSessionTime(reqTime)
{
	//get time now
	var timeNow = new Date(); 

	//clear any countdownTickerEvents that may be running
	clearInterval(countdownTickerEvent);

	//difference between time now and time session started variable declartion
	var timeDifference = 0;

	//session timeout length
	var timeoutLength = sessionLength*1000;

	//set time for first warning, ten seconds before session expires
	var warningTime = timeoutLength - (warning*1000);

	//force redirect to log in page length (session timeout plus 10 seconds)
	var forceRedirectLength = timeoutLength + (forceRedirect*1000);

	timeDifference = timeNow - reqTime;

     if (timeDifference > warningTime && warningStarted === false)
        {
            //reset number of seconds to count down from for countdown ticker
			countdownTime = warning;

			//call now for initial dialog box text (time left until session timeout)
            countdownTicker(); 

            //set as interval event to countdown seconds to session timeout
            countdownTickerEvent = setInterval("countdownTicker()", 1000);

            $('#dialogWarning').dialog('open');
			warningStarted = true;
        }
    else if (timeDifference > timeoutLength)
    	{
    		//close warning dialog box if open
            if ($('#dialogWarning').dialog('isOpen')) $('#dialogWarning').dialog('close');

            $('#dialogExpired').dialog('open');

        }

     if (timeDifference > forceRedirectLength)
     	{
        	//clear (stop) checksession event
            clearInterval(checkSessionTimeEvent);

            //force relocation
            window.location="login.cfm?expired=true";
        }
}

The countdownTicker function provides a countdown inside the warning dialog box to prompt the user to act now. It uses a timer that fires every second for a 5,4,3,2,1 effect inside the dialog box.

function countdownTicker()
{
	//put countdown time left in dialog box
	$("span#dialogText-warning").html(countdownTime);

	//decrement countdownTime
	countdownTime--;
}

And, the dialog boxes either allow the user to restart the session or, if they did nothing when the warning popped up, it logs them out by redirecting to the log in page with the expired variable in the query string. Also, it redirects to the log in if they hit the close button on the dialog box rather than the Login button on the dialogExpired dialog box.

$(function(){
        // jQuery UI Dialog
        $('#dialogWarning').dialog({
            autoOpen: false,
            width: 400,
            modal: true,
            resizable: false,
            buttons: {
                "Restart Session": function() {
		   //reset session on server
                  $.post("restart_session.cfm");

		   //reset the variables
		   requestTime = new Date();
		   warningStarted = false;
		   countdownTime = warning;

		   //clear current checkSessionTimeEvent and start a new one
		   clearInterval(checkSessionTimeEvent);
		   checkSessionTimeEvent = "";
		   checkSessionTimeEvent = setInterval("checkSessionTime(requestTime)",10*1000);

		    $('#dialogWarning').dialog('close');
                }
            }
        });

        $('#dialogExpired').dialog({
            autoOpen: false,
            width: 400,
            modal: true,
            resizable: false,
            close: function() {
                   window.location="login.cfm?expired=true";
            },
            buttons: {
                "Login": function() {
                    window.location="login.cfm?expired=true";
                }
            }
        });
});

The “Restart Session” button sends a post request to a ColdFusion page that sets a session variable. That act alone refreshes the session.

<!---Setting the give_me_more_time session variable refreshes the session.--->
<cfset session.give_me_more_time = true />
<!--- Below is optional. There just so you can see a response from the server in firebug. --->
<cfoutput>session.RequestStartTime: #session.RequestStartTime# session.loggedin: #session.loggedin#</cfoutput>

The dialog box contents are at the bottom of the page but they could be just about anywhere in the body.

<!--Dialog box contents-->
<div id="dialogExpired" title="Session (Page) Expired!"><p><span class="ui-icon ui-icon-alert" style="float:left; margin:0 7px 0 0;"></span> Your session has expired!<p id="dialogText-expired"></p></div>

<div id="dialogWarning" title="Session (Page) Expiring!"><p><span class="ui-icon ui-icon-alert" style="float:left; margin:0 7px 0 0;"></span> Your session will expire in <span id="dialogText-warning"></span> seconds!</div>

Just for Fun

You can view the response from the restrart_session.cfm in Firebug and compare it to the time on the page from the dumped session vars.
session time compare

Usual recommended jQuery and CF reading:

Download zip of all files

jQuery UI Autocomplete Widget with ASP.NET VB

You might also be interested in the Using jQuery Autocomplete to Populate Another Autocomplete post.

As a follow up to the jQuery UI Autocomplete Widget with ColdFusion and the jQuery UI Autocomplete Widget with PHP posts, I did one with ASP.NET (VB.NET) as the backend. I swear this is the last one I’m doing. I’m running out of languages that I work in.

The jQuery UI folks have released an autocomplete widget that is pretty slick. This example uses the JavaScriptSerializer() function in .NET 3.5. I heard a rumor .NET 4 might make this json encoding with data easier. We’ll see.
autocomplete
This example will use US states and territories to populate the autocomplete. It will also demonstrate how to fill other fields with data returned from the database. This data can be used to fill a visible text box or a hidden form field. It also demonstrates the basic autocomplete functionality which may be fine for some applications.

Of course, you will need the jQuery core file, the jQuery UI core file, and the jQuery UI style sheet of choice. The style sheet comes from the themes available in the jQuery UI website and can be downloaded with the core file or you can link to the latest versions of both the core files and the css:

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/redmond/jquery-ui.css" /> 

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

The HTML is straight forward and stripped down for the example:

<form action="Default.aspx"  method="post">
<fieldset>
<legend>jQuery UI Autocomplete Example - ASP.NET VB Backend</legend>
<p>Start typing the name of a state or territory of the United States</p>
<p class="ui-widget"><label for="state">State (abbreviation in separate field): </label>
	<input type="text" id="state"  name="state" /> <input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></p>
    <input type="hidden" id="state_id" name="state_id" />
<p class="ui-widget"><label for="state_abbrev">State (replaced with abbreviation): </label>
<input type="text" id="state_abbrev" name="state_abbrev" /></p>
<p><input type="submit" name="submit" value="Submit" /></p>
</fieldset>
</form>

As a bonus, we dump out the form values to see what we have right underneath the form itself:

Sub Page_Load(Source As Object, E As EventArgs)
 	Dim formfields As String = "<p>" 

     For Each sItem In Request.Form
	 	formfields = formfields + "<strong>" + sItem + "</strong> = " +  Request.Form(sItem) + "<br />"
  	Next
	formoutput.Text = formfields + "</p>"
 End Sub

And the jQuery on the page is equally brief:

$(function() {

            $('#abbrev').val("");

            $("#state").autocomplete({
                source: "states.aspx",
                minLength: 2,
                select: function(event, ui) {
                    $('#state_id').val(ui.item.id);
                    $('#abbrev').val(ui.item.abbrev);
                }
            });

            $("#state_abbrev").autocomplete({
                source: "states_abbrev.aspx",
                minLength: 2
            });
        });

Notice that there are two autocomplete functions on the page, one for each example in the demo. Each function calls a different aspx file which return slightly different result sets.

Also, the minLength for autocomplete to return results is set to 2 to prevent too many rows from being returned.

The jquery autocomplete will append the text typed into the autocomplete field as the URL parameter ‘term.’ This URL parameter is used to query the database.

From the jquery documentation:

The request parameter “term” gets added to that URL.

Both .NET pages return the data after a few steps:

  1. It creates a new javascript serializer
  2. It creates an object to hold the data from each returned row in the query
  3. It queries the database and fills a dataset (keep reading if you like readers better)
  4. Loops an array of the query results adding each row to an object
  5. Adds the object to an ArrayList
  6. Outputs the ArrayList as JSON data

The states.aspx file returns the id field, the state field as ‘value’, and the abbrev field. These values are placed in the appropriate text boxes by the autocomplete jQuery function.

<%@ Page Language="VB" Debug="false" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
    Dim serializer As JavaScriptSerializer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        serializer = New JavaScriptSerializer()
        Response.Write(JSONData(Request.QueryString("Term")))
    End Sub

    Public Class State
        Public id As Integer
        Public value As String
        Public abbrev As String
    End Class

    Private Function JSONData(ByVal term As String) As String

        Dim stateArray As New ArrayList
        Dim index As Integer = 0

        Dim objConn As New SqlConnection("YOUR-CONNECTION-STRING-HERE")
        Dim myds As New DataSet("States")

        objConn.Open()

        Dim adapter As New SqlDataAdapter("SELECT id, state, abbrev FROM states WHERE state like '%' + @ac_term + '%'", objConn)
        adapter.SelectCommand.Parameters.Add("@ac_term", SqlDbType.VarChar)
        adapter.SelectCommand.Parameters("@ac_term").Value = term
        adapter.Fill(myds, "States")

        For Each dr As DataRow In myds.Tables(0).Rows
            Dim st As New State()
            st.id = dr("id").ToString()
            st.value = dr("state").ToString()
            st.abbrev = dr("abbrev").ToString()
            stateArray.Add(st)
        Next

        objConn.Close()

        Return serializer.Serialize(stateArray)
    End Function

    </script>

The states_abbrev.aspx shows the basic functionality of the autocomplete function by just assigning results of the query to the ‘label’ and ‘value’ fields. Explanation on the ‘label’ and ‘value’ fields from the jQuery UI site:

Very important information below. Please read and understand before expecting the autocomplete to work properly.

The local data can be a simple Array of Strings, or it contains Objects for each item in the array, with either a label or value property or both. The label property is displayed in the suggestion menu. The value will be inserted into the input element after the user selected something from the menu. If just one property is specified, it will be used for both, eg. if you provide only value-properties, the value will also be used as the label.”

<%@ Page Language="VB" Debug="false" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
    Dim serializer As JavaScriptSerializer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        serializer = New JavaScriptSerializer()
        Response.Write(JSONData(Request.QueryString("Term")))
    End Sub

    Public Class State
        Public label As String
        Public value As String
    End Class

    Private Function JSONData(ByVal term As String) As String

        Dim stateArray As New ArrayList
        Dim index As Integer = 0

        Dim objConn As New SqlConnection("YOUR-CONNECTION-STRING-HERE")
        Dim myds As New DataSet("States")

        objConn.Open()

        Dim adapter As New SqlDataAdapter("SELECT id, state, abbrev FROM states WHERE state like '%' + @ac_term + '%'", objConn)
        adapter.SelectCommand.Parameters.Add("@ac_term", SqlDbType.VarChar)
        adapter.SelectCommand.Parameters("@ac_term").Value = term
        adapter.Fill(myds, "States")

        For Each dr As DataRow In myds.Tables(0).Rows
            Dim st As New State()
            st.label = dr("state").ToString()
            st.value = dr("abbrev").ToString()
            stateArray.Add(st)
        Next

		objConn.Close()

        Return serializer.Serialize(stateArray)
    End Function

    </script>

Usual recommended jQuery and .NET reading:



CreateUserWizard Set Email as Username VB.NET 3.5

Commonly, a login username is the individual’s email address. If you have used the CreateUserWizard in ASP.NET 3.5, you know that the username field and the email field are separate fields. Username is required by the wizard and email is not. If you want to populate the email field in the database with the username field, it is not obvious on the surface how to do it. Fortunately, it’s just some minor mods to the web.config and the aspx page.

web.config

In the web.config file, you will add requiresUniqueEmail=”true” to the membership providers add entry. Below is a stripped down example.

 <membership defaultProvider="MyMembership">
      <providers>
        <add name="MyMembership" type="System.Web.Security.SqlMembershipProvider" connectionStringName="MyConnectionString" requiresUniqueEmail="true" />
        </providers>
 </membership>

aspx page

On the aspx page itself, you will add RequireEmail=”false” so the email textbox is not mandatory, a call to a function in the OnCreatedUser event, and a call to a function in the OnCreatingUser event. Also, some validation is added to the username field to ensure that it is in a valid email format. Finally, you will add an invisible textbox control for the email field since unique email is required in the webconfig.

<asp:CreateUserWizard ID="CreateUserWizard1" OnCreatedUser="CreateUserWizard1_CreatedUser" OnCreatingUser="CreateUserWizard1_CreatingUser" RequireEmail="false" Runat="server">

<asp:TextBox ID="UserName" Width="200" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="UserNameRequired" runat="server" ControlToValidate="UserName" ErrorMessage="E-mail is required."ValidationGroup="CreateUserWizard1" />

<asp:RegularExpressionValidator ID="regEmail" ControlToValidate="UserName" Text="Invalid e-mail" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" Runat="server" /> 

<asp:TextBox ID="Email" runat="server" Visible="false"></asp:TextBox>

The OnCreatingUser event fires during the creation of the user and sets the invisible email text field in the CreateUserWizard to the username. This ensures that the requireUniqueEmail setting in the webconfig checks to make sure the email is unique in the database.

Protected Sub CreateUserWizard1_CreatingUser(ByVal sender As Object, ByVal e As LoginCancelEventArgs)
        Dim cuw As CreateUserWizard = CType(sender, CreateUserWizard)
        cuw.Email = cuw.UserName
    End Sub

The OnCreatedUser event fires right after the user is created and updates the user object email field with the username.

Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)
        Dim userNameTextBox As TextBox = CType(CreateUserWizardStep1.ContentTemplateContainer.FindControl("UserName"), TextBox)
        Dim user As MembershipUser = Membership.GetUser(userNameTextBox.Text)

        user.Email = user.UserName
        Membership.UpdateUser(user)
    End Sub

Using Google Docs to Create a Survey

After creating the pop-up survey in jQuery, further exploration on creating online polls has turned up Google Docs quick and dirty form creation.

Google posted a 2-minute video on Google Docs form creation that pretty much sums it up. This has actually been around for a couple of years now but, even though it’s a little late to the party, here is a quick illustration of my jQuery poll done with Google Docs:

Select “Form” from the “Create New” menu.

Fill in the name of the poll or form, short description, and the first question.

Add a question by clicking the “Add Item” button and choose from the menu items. Under “More Actions” you can create the confirmation message and allow the submitters to see the results.

You can also get the embed link for putting the poll right on a web page.

And, you can email the form and have users submit answers right from their email by hitting the “Email this form” button and adding the email addresses.

Here it is in all it’s embedded goodness. Try it out: