My SQLGridSelectedView module (SGSV) is a useful tool for displaying data from a database. However there are times you want to place it on a page and NOT have it process!  

Seems strange to place a module on a page then not use it - well it did to me!  But, as Joseph Craig pointed out, there is a specific use case where this is the desired requirement.

I was truly amazed by the turnout at last night’s Toronto Area DotNetNuke User Group (TADUG).   The reason I was amazed was the competition – let me explain.
 

On Wednesday April 14th, the inaugural meeting of the Toronto Area DotNetNuke was held at the McLean Community Center in Ajax Ontario.  By all accounts the meeting was a great success.

What most don't know is it came within a few hours of being cancelled.  Let me explain. 

As many of you know I use DotNetNuke to monitor the operation of a rather complex Material Handling Application.  Part of the monitoring includes SQL Server Jobs.  The application has several jobs that routinely generate reports and collect and summerize data at shift changes. 

We have some other tools that monitor the health of the various servers and specifically the SQL Server application.  Those other tools will page me if they discover something out of the norm.  One issue I have had in recent months is the occasional page that a SQL Server Job had failed.   A review of the Job summary revealed no such failure. What?  So, here is where DNN and a little T-SQL helped.

This blog entry will be a bit off the topic of DotNetNuke, but there is a link.

I have to set the stage, in 1975, I was 20 years old. I was just starting out in the computer industry and the stories of the early “home brew” computer clubs and the beginning of “hobby” computing were part of the times I lived.  I read about all the hobbyist computing through the pages of “Byte” magazine – still regret throwing out 10 years worth of the magazine.    

On Tuesday March 9th, I presented at the Orlando DotNetNuke User Group (ODUG) and had a great time.

The fact that I was presenting added to the fun and enjoyment of the night, so I thought I would share a bit of the experience.  Primarily I want to encourage other DotNetNuke Users to pass along their knowledge.

So, here is my story…

At Open Force 2009 this past November, I attended a presentation by Kevin Schreiner (DotNetNuke on Speed and Performance) that spoke in part about the use of Sprites.  What on earth are sprites?  Well I had heard of the term sprites in relation to Computer Graphic programing, but not when it came to Web sites and DotNetNuke in particular.

So what are Sprites in terms of a Web site?  They present a way to reduce traffic between the server and the client.  Specifically, a sprite will contain a collection of images in one file, that can be reference to supply various graphic elements on a page. Since only one image file is required, the site will load faster. Additionally, since one image is used, less memory is used by the browser to render the page.

How is this done?  Well, after I had seen the presentation, I understood the concepts, but had yet to put into practice as I had no real need.  So it took me until now to sit down a try to use sprites.  Read on to see a simple example using sprites.
 

One of the most useful T-SQL features is the keyword "IN".  THis keyword allows the user to filter the selected data against a list of comma separated data. For example, using the Northwind sample database:

    select * from Customers where Country IN ('Canada', 'Mexico')
   
However, this is rather limited syntax as the parameter list is static.  Read on to see how you can use a User Defined Function to improve the IN keywords useability.

First of all it's a new year. Welcome to 2010 everyone -- I for one am looking forward to what the year will bring.

The Holidays has given me some time to get several items completed  that I have been working on for some time.  They are -- in no particular order: 

The second module I developed was called XMLdb.  This modules uses the "For XML" functionality of T-SQL along with power of XLST to provide some awesome results. 

Recently, I rewrote a good portion of XMLdb to utilitize the revised ASP.NET V2.0 XML routines.  Sadly, the existing module as still using the ASP.NET V1.0 methods, so the module was in need of some TLC. 

To demostrate the power of the module, I developed an Paging Example using the Northwind database and the Customer table.  While implementing paging is simple using the various grids, typically the performance suffers when the database table is larger.  In most cases the entire table must be returned from the database the the grid selects the disired records for display ... all the other records are unused.  On a 1,000,000 row table -- this can hurt performace especially if this is done several times as the user pages through the data.  However there are alternatives, and this demostration illustrates such an alternative.

You can read about the demostration here, see it in action here, or download the sample code and modules here

Hope you enjoy this version of paging.
Paul.

Ever wondered...

How can I display an EXCEL Spreadsheet in DotNetNuke?

I have been asked this question several times in the last few weeks and have seen the same question posted in the forums too.  The answer is rather straight forward and the work is mostly done by Microsoft.  But there is always a catch.  In most causes, it's view only! Also, the syntax to extract the data is a special version of T-SQL, so there are some limits. 

Self promotion can be difficult especially if it's not in your nature.  In my case, I find it difficult.  However, I received a forum post from a new subscriber to my site that indicated that he had been looking of a SQL Grid display module for some time and just discovered my module.  So I guess it is time to do some self-promotion.

So what is SQLGridSelectedView?  First let's take a look a the name:

  SQL - indicates the use of SQL to query a database
  Grid - indicates the typical display format although templating is available
  Selected - indicates the ability to filter the selection of data 
  View - the primary focus is to view data, but creating and updating data is possible. 

I have been fighting a problem with user subscriptions on my site for some time.  It would take some time for a user to obtain access to the subscriber viewable items on my site.  I am using the Subscription Tools from Ventrian ( highly recommended by the way) and works as expected except for this one issue. 

I originally blamed everyone else (a good trait of a developer) - first PayPal, then subscription module, then server performance, then caching ...  Yesterday, I happened to be on the site and trying once more to figure this out when a user subscribed to the site.  Perfect, I immediate validated all the appropriate table at ensure all was correct.  I contacted the user and asked to see if he did have access. 

I have been working on skinning and playing with menus. Menus of empty pages.  I manually would create a structure and then work the skin and menu classes to define the look wanted. If you want a good tutorial on menus, see DNNCreative's #47 edition -- good stuff.  

But after creating these empty pages over and over ... I got thinking there must be a better way. I binged and googled (boogled it?) and found no hits, so it was time to play around in T-SQL again.

I developed a procedure called CreateEmptyPage 

The latest version of MenuLinks has been released on Tressleworks site.  This version has several enhancements that may interest the power users out there.

However, first a bit of recap if you are not familiar with MenuLinks.  MenuLinks displays the relative menu items as links based on the current page.  What? 

Consider this scenario, you want to display a list of links for all other pages at the same level on the menu.  Well this can be done maually with the DotNetNuke Links modules.. but it will take time.  MenuLinks allow the user to do this by setting a few modules settings once.  If addition pages are added (or removed) MenuLinks will display the links based on the current menu contents.  Like the DNN Links menu, the list can be horizontal or vertical and align left, center, or right within the container.  Very handy.  You can see examples of MenuLinks here 

It seems lately that I have been taking the long way around when attempting to do something involving DotNetNuke.  My latest extended effort was attempting to add a indented dot tab list to my MenuLinks module.  It’s a long story that I will shorten so you can learn the lessons I had to learn the hard way.

It start simply enough, I wanted to add the ability to display any sub-menu within MenuLinks, not just the current page’s relative menu items (Parent, Siblings, or Children).  The module currently uses the tabid of the current page to determine the relative menu position.  I thought that if I allowed the user to supply the starting menu item, the module could easily display any relative menu.  So begins the hunt for the indented dot tab list.  

Back in the spring of 2004, I was introduced to DotNetNuke via an article  by Jim Duffy  in Code Magizine. I got the magazine from the magazine stand in our office building at noon, and by the time I left for the day I had a site up and running (seems that it was a rather quiet day and no one questioned what I was doing).  Well I was hooked and have been working with DotNetNuke ever since.

One of the first things I did was write a module -- took me several weeks of banging my head as I was complete lost in this new world.  I using DotNetNuke as a learning tool to a) learn VB.Net, b) learn ASP.NET, and c) learn about the web. I have spent the better part of my life developing supervisory process control applications for material handling, so the Web stuff was a complete new territory and DotNetNuke was a nice structured working application that I could extended with my own code -- what could be better!  My first module was T-Minus. 

After a security review of an internal site, a concern was raised that passing parameters to other pages in cleartext where the parameter name was rather reveling was weak from a security point of view.  The pages where the concern was raised all used DotNetNuke’s IFrame module, and in fact the parameters were passed in cleartext.  For example, several of the target Frames required that the user’s username be passed.  The IFrame module would create a URL like www.othersite.com?username=myusername. This meant that a user could potentially revise the URL with a different username and spoof the real username.  I was required to “blur” the parameters being passed so that someone looking for specific parameters would no be able to determine what was being passed. 

In recent weeks, I have been working on a sample site for all of the Modules currently available from Tressleworks.  There have been examples on my site, but they had been rather limited.  So I have created a child site off www.tressleworks.ca called www.tressleworks.ca/samples.  Original n'pas?

It seems that I was thinking too hard when I attempted to understand how "Display on All Pages" might be implemented.  Well I was all wrong and have been living under a misconception for quite some time.  There were clues that my conceptualization was incorrect, but I ignored them because I was right -- and it must be something else ... yes -- pure ignorance.

OK, so how is this feature implemented and what are some of the issues associated with it? 

I required an enhanced IFrame Module that will "blur" the parameters being passed to the page referenced in the IFrame.  The issue is passing parameters in clear text is considered "bad form" by Web Inpection tools such as HP's WebInspect.  While the DNN site in a intranet site, I guess the need to "blur" the parameters does have merit. 

So I need to revise the IFrame to "blur" the parameters --  I am going to modify the parameters passed in one of two ways -- Base64 encode the value string -- this will make the effort of determining what is passed difficult for a snooper of the HTML source.  The other way will be to Encrypt the data using the standard encryption provided by .Net. 

However is creation of the module turns into a real problem with multiple control definitions for a module.

As I mentioned in a recent Blog posting, I am attempting to blog more.  With blogs postings come comments to my blog -- this is a appreciated and unfortunately sometimes overlooked.  So I have added a new report to my site that displays recent blog comments.  This way when I do my daily site review I can check if I have comments that require responses.

I also have missed some feedback messages -- even though they generate an email, I do miss them so I have a report to display all the unanswered feedback messages. 

You can use the Reports module to display the information or one of the several other SQL grid modules to display the report.  For obvious reasons I use SQLGridSelectedView.

The new release of DNN 5.1 does have a small (very small) issue when converting from V4 DNN site.  The problem exists with a key and the conversion script does not drop the key due to a naming issue.  There is a fix and is available from the support wesite. 

If you are upgrading from 4, please read.  The issue is simple to fix as even if you don't you should not encounter any problems.

 I have included Charles Nurses' comments and script below - a direct copy from the support.dotnetnuke.com site. 
 

Recently, I have been doing a fair amount of work with fonts and colours  (or color depending if you were not born under british rule).   During this work, I discovered a colour that I didn't know existed.  The color is "transparent".  In .Net world, this color will allow the underlying color to be shown.  Brillant!

So where does this come in very handy -- well consider things like border colours -- often the border colour is pre-defined and you are allowed to change it, but in the world of DNN and skinning, what is the correct colour today, may be horrible when you re-skin your site.  In this case, transparent works perfectly.  Instead of a colour - either a colour number or explicit colour name, use "transparent" and the problem is solved.

While this is no doubt known in the work of designers and skinner, this may be "news" to us more graphically challenged types.

Hope this helps Paul.

EDIT: Seems that IE6 and probably lower version will default "transparent" to black.  Ugh!  ...

Read More »

I wanted to create some testing data, so I began adding users by hand.  One or two users is OK, but I wanted to add many so I started to play with the script that Mitchel Seller's had blogged in May, 2007 (here) .  I wanted a stored procuedure I could use to add a user.  I would then create a script calling this procedure as required.   So here is what I developed ...

 In the last few days, the DotNetNuke Web Site has been struggling.  The recent move to dogfood the 5.1 Release Candidate has surfaced the worst possible type of error – a performance error based on load.  This is probably the toughest error to determine and eliminate as it may be one issue or a combination of several.

I know there are many in the community that are not pleased at the current situation as it does publicly air the problem DotNetNuke Corp is having. I, for one, am more interested in the solution than the minor embarrassment that the Corp may feel.  In the end, once the problem is found, I am confident the cross-checks and test will be put in place so this issue will not happen again which will lead to a better product in the long run.

Now that the dust has settled, I wanted to take some time and recount my time in Tampa at Day of DNN.  As many have said it was a great day and I certainly agree.

The adventure started at Toronto Pearson Airport with my flight to Tampa on Westjet.  The flight was rather common and I was lucky enough to have the seat row to myself, the plane being 75% full.  Arrival at Tampa provided the first surprise – it was really hot.  Toronto was cold in comparison, but I was not ready for +93F (34C) temperature.

I must admit the first version of the report module was viable, but lack many options I would perfer to see.  Well the new version -- release this past weekend -- is worth a new look.   While this release is just a minor release to correct some issue, the fact it is probably a the best time to take the module for a test drive. 

I spent the day getting my presentation ready for the Day of DotNetNuke  (www.dayofdnn.com) .  Well they are at a point where I could tweak them all night and not really get any improvement.  So it's time to put then down for a while and relax.

The big issue is the audience I will be presenting to - you know the first rule of presentations is know your audience - but this is going to be a bit tricky.  I could get a group of all DNN pros ... gulp -- or a room of rookies.  I am hoping for a mixture. 

Well I made it down to Tampa and spent the day hanging out talking (amoung other things) about DNN. I know that a DNN ecosystem exists, but I find that description is so cold.  So when meeting other DNNers, I really enjoy the passion and the energy the "ecosystem" provides.

In the last few years, the number of way that someone can view your site has increased considerably.  The growth in different browsers and the growth in cell phones, the change is amazing.
 
As you may have noted from other blog posts and several Dotnetnuke forum posts, I have been attempting to get people to move off IE6 to any of the current new browsers.  So I was somewhat surprised by the following stored procedure in Dotnetnuke:

I was playing around with the functions and views described by EricVB in this blog entry and realized I could easily determine my international audence using one of the functions.

Eric created a function called udf_UserProfileField that generates a table result (MS-SQL 2005 or better) for a given profile field. All the user who have this value are returned.

When visiting the ODUG (Orlando DotNetNuke User Group) website, I noticed as a container with a title of "Modules used on this page".  The container is minimized so you need to expand the container to see the report.  The report details the containers used on the page. 

So how is this done?  The report modules is used to query the modules tables to display the modules for the current tab.

Ever needed to locate every page (tab in DNN old speak) where a module is used?  This is often needed when updating a site and want to confirm that each use of the module converted properly.

So use this query:

SELECT   t.taborder,          t.tabid,          t2.tabname AS [Parent Tabname],          t.tabname,          m.moduleid,          m.moduletitle FROM     dbo.tabs t          JOIN tabmodules tm            ON t.tabid = tm.tabid          JOIN dbo.modules m            ON tm.moduleid = m.moduleid          JOIN dbo.moduledefinitions md            ON m.moduledefid = md.moduledefid          JOIN dbo.desktopmodules dm            ON md.desktopmoduleid = dm.desktopmoduleid          LEFT OUTER JOIN tabs t2            ON t.parentid = t2.tabid WHERE    dm.friendlyname = "TMinus"          AND t.portalid = 2          AND m.isdeleted = 0          AND dm.isadmin = 0 ORDER BY t.taborder

As you can see I have explicitly defined the friendlyname of the module -- in this case "TMinus" 

...

Read More »

Even wonder where all the settings for a module is kept ... in the ModuleSettings table - duh!   Ok ... maybe it's not that obvious to some.  But the ModuleSettings table holds the keys to the kingdom for certain modules.  In fact, often the module export is just a copy of the items in the Modulesetting table for the specific module.

The table layout is straight forward ... ModuleID, SettingName, SettingValue.  So if the moduleID is 400 then to view all settings for moduleID 400, execute the following select

Select * from ModuleSettings where ModuleID = 400 order by SettingName

This Select will return all the specifics settings for Module 400.  Note: not all

ModuleID  SettingName          SettingValue --------- -------------------- -------------------------------------- 400       EventAfterMessage    !!! 400       EventBeforeMessage   until Vancouver Olympics 400       EventDisplay         14 400       EventDuringMessage   Go Canada Go 400       EventProcess         1030...

Read More »

Unless you are hosting your site on your own server(s), your hosting company will impose a limit on your disk space.  Occasionally, you will see a posting in the DotNetNuke forums asking "Why is the database so big?" or "What table is using all the disk space"

So there is two ways to look at this ... via SQL Servier Management Studio (SSMS) or via SQL ... some hosting environments do not allow access via SSMS, so SQL is the only option. 

The following SQL will display all the tables:

 EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]'

This statement uses the "hidden" Microsoft store procedure "sp_MSforerachtable" that will execute a given command against each table in a database.  You will need to review the results to determine the largest table.

Alternatively,  the following T-SQL will generate a list of tables in largest row count to smallest.

set nocount on

-- create table to hold name and rows -- cannot use in-memory as it will not work with sp_MSforeachtable...

Read More »

While I have been playing with DNN V5 beta and RCs, I was interested in a forum post a few weeks ago that was asking how to determine the version of DNN.  For long time users of DotNetNuke, this is a simple question answered on the Host page.  

However, there are other ways to determine the version - primaryly the version table in the DotNetNuke database.  But  when did you perform your updates?  This is what I began wondering.  So I created a SQL SELECT that will return the dates of the update.

select     Cast(major as varchar(2))     + '.' + cast(minor as varchar(2))     + '.' + Cast(build as varchar(2))     as [Version Number],     Convert(varchar(17), Createddate, 113)     as [Implementation Date]  from     {databaseOwner}{objectQualifier}version where     versionid in    (      select  max(versionid)      from {databaseOwner}{objectQualifier}version      group by Convert(varchar(17), Createddate, 113)    ) order by     versionid desc

Note: I used the fact...

Read More »

In recent months new browsers have been released in to the wild - IE8, FF3, Crome...  So do you know the breakdown on how your site is visited?  In a forum post here I requested that DNN site owners help eliminate IE6.  I aslo included a select statement you can use to determine your traffic.

select count(useragent) as [count], Case     when charindex('MSIE 5', UserAgent) > 0  then 'IE5'     when charindex('MSIE 6', UserAgent) > 0  then 'IE6'     when charindex('MSIE 7', UserAgent) > 0  then 'IE7'     when charindex('MSIE 8', UserAgent) > 0  then 'IE8'     when charindex('Firefox', UserAgent) > 0 then 'FF'     when charindex('Opera', UserAgent) > 0 then 'Opera'     when charindex('Chrome', UserAgent) > 0  then 'Chrome'     when charindex('bot', UserAgent) > 0     then 'bot'     when charindex('slurp', UserAgent) > 0   then 'bot'     when charindex('crawler', UserAgent) > 0 then 'bot'     when charindex('spider', UserAgent) > 0  then 'bot'     when charindex('jeeves', UserAgent) > 0  then 'bot'...

Read More »