Changing what appears on the title bar

DotNetNuke has two ways of setting the title bar, depending on whether you set the "Page Title" field in Page Properties. It either displays your page title as you have set it, or the full path to your page (similar to the breadcrumb) when you leave it blank.

Some third-party modules, though, do use the page title for their purposes so you may want to have a page title exactly the same as the page name (reminder: the page name is what you see in the navigation component, while the page title is what you see at the browser title bar).

But you don't want the page title to appear "naked" in the title bar. You may still want to display the full path, or even just the portal name along with the current page's title.

To do either:

- Open default.aspx.vb (you'll find it in the root folder) with your favourite editor, even with notepad.

- Locate the InitializePage() function.

- Find the following chunk of code:

' set page title
Dim strTitle As String = PortalSettings.PortalName
For Each objTab In PortalSettings.ActiveTab.BreadCrumbs
strTitle += " > " & objTab.TabName
Next
' tab title override
If PortalSettings.ActiveTab.Title <> "" Then
strTitle = PortalSettings.ActiveTab.Title
End If


The first part of the code (set page title) says: "Put the full path in the title"
The second part of the code (tab title override) says: "If the title is not empty, just show the title the user has entered and nothing else".

In order to show the portal name along with the title the user has entered, change the line:

strTitle = PortalSettings.ActiveTab.Title
to
strTitle = PortalSettings.PortalName + " > " + PortalSettings.ActiveTab.Title

In order to show the full path in any case (regardless whether the title is empty or not), DELETE the second part of the code all together (the last if...end if part). That way, the title will always be the full path to your page, comprised of the corresponding page titles of all the parent pages till the root page.

Read more...

Retrieving tabs using SQL, security-aware

There are times when you like to go deeper with DNN and retrieve certain information by means of an SQL Query or a stored procedure. That's what happened to me when I needed to create a custom combo box which would redirect the user to specific pages.

In order to achieve that functionality, I had to use a module which would permit me to issue SQL queries to DotNetNuke and then format the results as I wanted. As I've stated many times before, I find the ListX module pretty useful in these situations.

One thing I discovered, though, is that you need to have a deeper understanding on what's going on with security in order to issue an SQL query that selects ONLY the tabs that a registered user has the right to see. You see, DNN tabs are not always visible to all user roles, so when using SQL you must be careful not to retrieve tab records that lead nowhere for the current user.

I'm not going to go into details on what I've done with the ListX module, unless someone asks specifically for it. Instead, on this post, I'm going to focus on how to issue a SQL query that retrieves records from the Tabs table that are visible for the current user.

For this post, it is assumed that you already know the ID of the current user in some way.

If you issue something like:

SELECT tabid, tabtitle FROM Tabs

Then you'll have all tabs, even those that are deleted. So we first need to have a WHERE clause to exclude deleted tabs, like this:

SELECT tabid,tabtitle FROM Tabs WHERE isDeleted=0

That leaves as with all active tabs, even those that are accessible by the administrator. In order to make our query security-aware, we have to see what tables the Tabs table is related to, in the context of security permissions.

The related tables are the following:

• TabPermission: A table which holds role permissions for every tab. Remember, DNN security works with roles. (We assume that we're not using the special "specific userid" feature that was introduced in later version of DNN).
• UserRoles: A table which holds the participations of DNN users to DNN security roles
• Users: A table which holds the actual DNN users.

The table that interests us more is the TabPermission table. Let's see the fields that interest us the most from this table:

TabId:

The tab id the record is referring to. There may be more than one records per tab id in the TabPermissions table, if the tab is available to multiple roles or even to one role with multiple permissions (DNN grants VIEW and EDIT permissions to tabs).

PermissionID:

The number you see here is the type of permission specified, and is related to the Permission.PermissionId field. If you do a SELECT * FROM Permissions you'll get, among other records, the following two records:

3    SYSTEM_TAB    -1    VIEW    View Tab
4    SYSTEM_TAB    -1    EDIT    Edit Tab

For our purposes, we will need permission id 3, i.e. VIEW permissions. Remember, we need the tabs that our user is allowed to see.

RoleID:

This is where things get a bit complicated. The RoleID field is related to the Roles.RoleId field (try a SELECT * FROM Roles to see what you'll get). But here, we don't need all the roles but just the roles our user is participating in. So we need to relate this field to the UserRoles table, which holds that information. If you do a SELECT * FROM UserRoles WHERE UserId=xxx (put your user id in the place of xxx), you'll get all role participations for the specified user.

(You'll notice that the UserRoles table also has the fields ExpiryDate, IsTrialUsed and EffectiveDate. For the sake of simplicity, we'll suppose that we don't have any roles with dates set, although it would be very easy to extend our final query to include only roles that are effective in the current time period.)

In the TabPermissions table, you'll notice two additional role ids that DO NOT exist on the Roles table: -1 and -2. -2 stands for "Host", and concerns pages accessible by the host user. -1 stands for "All Users" and concerns all non-administrative pages that are accessible from the "All Users" virtual role (this role actually does not exist anywhere).

Try doing the following:

--Fetch only admin pages accessed by the Administrators role 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=0

--Fetch only non-admin pages accessed by all users 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=-1

--Fetch only host pages 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=-2

And observe the different results.


So what do we need in order to get the tabs that are accessible from a single, specific user? We'll take our initial query:

SELECT 
    tabid,tabtitle 
FROM 
    Tabs 
WHERE 
    isDeleted=0

And we'll amend it as follows:

declare @userid int 
select @userid = 2 

    select 
        tabs.tabid, tabs.title 
    from 
        tabs 

inner join ( 
    select 
        distinct tabpermission.tabid 
    from 
        tabpermission 
        left outer join 
        userroles on tabpermission.roleid = userroles.roleid 
        left outer join 
        users on users.userid = userroles.userid 
    where 
        tabpermission.permissionid=3 
        and (users.userid = @userid 
        or tabpermission.roleid=-1 or @userid=1) 
    ) as perm 
on perm.tabid=tabs.tabid 

where 
    tabs.isdeleted=0 
order by tabs.tabid ASC

What we did was to initially declare a @userid variable and give it a value (4 for our example, whatever suits you for your DNN installation - if you're making an SP out of this you can just provide the user's ID value as a parameter to the SP).

We're using the @userid variable inside our JOIN statement, which actually joins the Tabs table with the TabPermission table, but restricts the TabPermission table results to where the permission type is VIEW (id 3) and our user id is equal to @userid (users.userid = @userid) OR there are view rights for all users (tabpermission.roleid=-1)  OR our user is the administrator. (@userid=1), which overrides everything else. The DISTINCT clause is used to ommit propable multiple results for the same tab.

This will essentially get us the tab records that are accessible with VIEW permissions for the given user.

As I've mentioned before, some things are not covered in this query:

• Permissions to a specific user id



• Roles with start and expiry dates

But in my opinion it's a good starting point for anyone who needs to create a security-aware tab retrieval query in DNN.

Please let me know what you think.

Read more...

The dbo.tabs.IconFile field issue

The case is fairly simple: In a DNN installation, we needed the user to use the IconFile field (i.e. the "Icon" field in the Advanced Settings section of the Page Properties page to add a custom icon to each page they made. We would then use a query created in Bi4Ce's ListX module to present a list of pages in the form of an in-page navigation menu for a specific portion of the portal.

To our horror, we discovered (using DNN version 4.8.2), that the IconFile field in the Tabs table takes two types of values: Either a value like "FileID=xxx", where xxx is the id of a record in the Files table, or the full path of the image file (Portals/0 is implied, so the file path is starting with the next sub folder).

That is, if you've set the icon to be in portals/0/myimages/myimage.jpg, you either get an entry in the Tabs table like "FileId=2312", or an entry like "myimages/myimage.jpg". At this point we are not sure why this is happening, but the truth is we don't care. We just want the path to the actual file either way.

In the case of the fileid thing, you can get the path by joining the tabs table with the files table and then the files table with the folders table (the folder path itself is in the folders table) and voila. In the case where the full path exists in the IconFile field, you obviously don't need to do anything else.

We created a query which decides on what to do and produces a result set with two fields: tabid and img where img ALWAYS contains the full path to the image file (provided that one is set for the page), regardless of whether it's in the form "FileID=xxx" or in the full path form, and I'd like to share it with you.

You can easily make a view out of it and use it where appropriate. It hasn't been tested on multi-portal installations, maybe things will differ a bit there. Comments are always welcome. Here goes:

select 
 tabid
 , case 
  when ltrim(rtrim(iconfilepath))='' 
  then folderpath+filename
  else iconfilepath
  end 
 as img

from 
(
select 

   tabid
 , taborder
 , case
  when patindex ('FileID=%', iconfile) >0
  then convert(int, replace(iconfile, 'fileid=', '')) 
  else 0
  end 
 as iconfileid
 , case
  when patindex ('FileID=%', iconfile) =0
  then iconfile
  else ''
  end 
 as iconfilepath
 
from 
 tabs 
where 
 isdeleted=0 


) mytabs

left outer join files
on files.fileid = iconfileid
left outer join folders
on files.folderid = folders.folderid

order by 
 taborder asc

Read more...

Solving the Search Results problem when using the free MMLinks localizable module

(This article assumes that you have an advanced knowledge of what's going on inside the DotNetNuke portal framework and its Search indexer.)

For those of you who don't know what the MMLinks module is, go here to find out.

(waiting for you to read...)

Okay, now you know. With MMLinks, you can replace your standard Text/HTML modules with fully localizable ones, localizing both the title and the Text/HTML part. With one slight defect: Since DotNetNuke's search indexer doesn't know anything about the localized titles, it insists on presenting the default, non-localized module title in the Search Results page (that would be "MMLinks" if you leave it intact).

The reason for this is that localized titles for the MMLinks modules are kept in a different table: Delisoft_MMLinksTitleLocales. DotNetNuke doesn't take this table into consideration when indexing, so no localized titles in Search Results.

I tried to solve this problem by creating a trigger which affects the core SearchItem table. That's right, the table where all indexed items are kept. The trigger investigates whether the row being updated belongs to an MMLinks module instance (by checking the ModuleDefinitions table). If it does, it replaces the contents of the Title field with the localized MMLinks title. Since MMLinks modules produce one entry for each locale, voila. You've got localized MMLinks titles in Search Results (which, by the way, uses the contents of the SearchItem table).

I am providing this code in order to receive comments and / or improvements to it. Please do not use it anywhere near sensitive data or production databases. It may not work correctly.


So, first, a catch-all disclaimer to make the above statement even stronger :) :

CAUTION: THE TRIGGER HAS BEEN ONLY TESTED A COUPLE OF TIMES. IT IS SUGGESTED THAT YOU DO NOT USE THIS CODE IN A PRODUCTION ENVIRONMENT. IT IS STRONLGY ADVISED THAT YOU BACKUP YOUR DOTNETNUKE DATABASE FIRST. I DO NOT TAKE RESPONSIBILITY FOR ANY MALFUNCTION OR DATA LOSS THAT MAY RESULT FROM USING THE CODE PROVIDED HERE. USE AT YOUR OWN RISK.

And now, the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tool
-- Create date: March 2008
-- Description: A trigger which changes the title
-- field in the SearchItem table and replaces it
-- with the LOCALIZED title when using the MMLinks
-- module. USE AT YOUR OWN RISK.
-- =============================================
ALTER TRIGGER [dbo].[tr_Tool_setLocalizedTitleForMMLInksModule]
   ON  [dbo].[SearchItem]
   AFTER INSERT, UPDATE
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON

 DECLARE @searchItemId int
 SELECT @searchItemId = searchItemId FROM Inserted

 DECLARE @moduleid int
 SELECT @moduleid = moduleid FROM Inserted

 -- Do work ONLY if entry in SearchItem table concerns an MMLinks module instance.
 IF EXISTS
  (
  SELECT
   ModuleId
  FROM
   dbo.Modules
  WHERE
   ModuleDefId in
    (
    SELECT
     ModuleDefId
    FROM
     dbo.ModuleDefinitions
    WHERE
     FriendlyName ='MMLinks'
    )
  AND ModuleId = @moduleid
  )
 BEGIN

  DECLARE @localetext nvarchar(150)

  --Get the localized text
  --In order to find the localized text, we use a trick.
  --MMLinks fillds the SearchKey column in the SearchItem table with the
  --moduleId concatenated with the locale, in the form xxx-yyy. We use this
  --information to track the correct entry in the MMLinksTitleLocales table.
  
  SELECT
   @localetext = LocaleText
  FROM
   dbo.Delisoft_MMLinksTitleLocales
  WHERE
   (CONVERT (varchar(5), ModuleId)+'-'+ LocaleKey) =
   (SELECT SearchKey FROM SearchItem WHERE SearchItemID = @searchitemid)

 
  --Once we have the localized text, do the update.
  IF @localetext IS NOT NULL AND rtrim(ltrim(@localetext))<>''
  BEGIN
   UPDATE
    dbo.SearchItem
   SET
    Title = @localetext
   WHERE SearchItemId = @searchitemid
  END

END


 SET NOCOUNT OFF
END

If you want to see immediate results, just delete everything in your SearchItem table and do a content re-index from the Host Search Admin menu.

I'm anxiously waiting for comments.

Read more...

Integrating the Lightbox JS 2.03.3 image overlay script with DNN

According to its creators, "Lightbox is a simple, unobtrusive script used to overlay images on the current page. It's a snap to setup and works on all modern browsers."

And it really does what they say. So, why not use it with DNN? The fact that it can also work with groups of pictures providing basic back/forward navigation functionality makes it ideal as a lightweight picture gallery. Of course, it can be used with single pictures too, eliminating popup windows in order to see a picture, complete with (optional) caption, at a larger size.

In order to get Lightbox to work, you first need to go get if from http://www.huddletogether.com/projects/lightbox2/ . There you can also see examples and the way you can integrate Lightbox in an HTML page. So we'll only concentrate on things needed to be done in DNN here.

Our aim is to integrate Lightbox with a specific skin, not with the whole DNN installation. This way we'll have more flexibility regarding access to hosted DNN installations which may only allow access to the "Portals" folder.

So here are our steps:

1. Under the skin folder you wish to use for your DNN portal, create a folder called "Lightbox" and drop the contents of the .zip file you downloaded there. Three directories will be created: js, images and css.

2. Delete the html file as it only serves as an example.

3. Append the contents of the lightbox.css file that you'll find in the css folder into your skin.css file. You can keep the lightbox.css file, but you'll only make things more complex :)

4. Delete the lightbox/css folder if you like, as it is no longer needed.

5. In the ASCX file you will be using for your skin, put the following code immediately after the "@Register" tags as follows:

 <script src="<%=skinpath%>lightbox/js/prototype.js" type="text/javascript"></script>
 <script src="<%=skinpath%>lightbox/js/scriptaculous.js?load=effects" type="text/javascript"></script>
 <script src="<%=skinpath%>lightbox/js/lightbox.js" type="text/javascript"></script>

Update: For LightBox v2.04, the second line must be:
<script src="<%=skinpath%>lightbox/js/scriptaculous.js?load=effects,builder" type="text/javascript"></script>

SKINPATH is a DNN token and will be replaced at runtime with your current skin's path.

6. Immediately after the above piece of code, insert the following code:

<script type="text/javascript">
 var fileLoadingImage = "<%=skinpath%>lightbox/images/loading.gif";
        var fileBottomNavCloseImage = "<%=skinpath%>lightbox/images/closelabel.gif";
</script>

Why do we need this? The javascript file declares these two variables supposing they will be inside a folder relative to the physical position of the script, but that's not how DNN works, since our current path is NOT the skin's path, but the root path of the DNN installation! So we must override the paths here in order to be transparent to the image paths (using the skin path). We also have the additional ability to change the images used for the "close image" function here.

7. Open your skin.css file, locate the part you have copied from lightbox.css and locate the #prevLink, #nextLink classes. Change the paths to the three images for blank background, previous and next picture mentioned there as follows:

../images/blank.gif to lightbox/images/blank.gif
../images/prevlabel.gif to lightbox/images/prevlabel.gif
../images/nextlabel.gif to lightbox/images/nextlabel.gif

These images are unfortunately defined by the stylesheet, so since we moved the contents to skin.css we must make the positions of the images relative to the position of our current skin file. I will say again that you could always include the original lightbox.css but the current way is just my suggestion.

Of course you can use any image you like for next, previous and close functions as long as you maintain correct paths.

8. Try entering some image links into a text/html module as described in the original instructions for Lightbox and see what happens. Note: You can't use the aspx page link provided by the file manager when using lightbox. You must provide the links exactly as described in their (simple) instructions.


Finally, if the required JS files seem too big (approx. 103K total size) you could always use a JScript compressor like http://javascriptcompressor.com/ to reduce the total file size.

Please send comments if something in the instructions is not correct, so as to have it immediately corrected.

Happy Lightboxing! :)

Read more...

Video: Creating DotNetNuke Modules

A free video from JumpStart TV explaining how to create your own DotNetNuke modules. You can watch it online right here.

Read more...

Using the ListX module to create a database-driven master-detail interface on a single web page

Warning: This article assumes that you're aware of the ListX module by Bi4ce and that you have a basic understanding of what this module does, as well as a basic understaning of what DotNetNuke is. This article requires you to have the ListX module installed in your DotNetNuke portal.

Needless to say I am not a Bi4ce employee, I have just been using ListX and I'm thrilled about what this module can do - ListX lets you effortlesly pull data from any data source into DNN and present it the way you want - even use AJAX to do it.

The title may seem complex at first, but in fact what I would like to demonstrate is the exciting functionality of the ListX module with which not only can you issue custom queries (for tables inside your own DotNetNuke database or for any other source) and display results as a grid or repeater-like style, but you can also easily create detail pages - AJAX-enabled - which can reflect your choice in the "master" part without even having to reload the current web page.

First of all, we'll need a table to pull our data from. For the sake of simplicity, we'll just use an existing DotNetNuke table which will contain data even in a default installation: The Tabs table. The Tabs table contains information about our DNN pages, i.e. the title, the id and so forth. In a default installation, the table is filled with our default page info along with all the administrative pages for the Admin and Host accounts. Of course, nothing stops you from using your own table that you may create inside your DNN database or even a table from an external database. ListX supports it all.

Let's just mention the SQL we'll be using to retrieve list and detail data, we'll be using the statements later in our respective ListX instances.

Query 1: The query that we'll be using to present a list of available entries for the Tabs table
SELECT tabid AS pageid, tabname FROM tabs ORDER BY tabname DESC

Query 2: the query that we'll be using to present the details of a record
SELECT tabname, title, isvisible, iconfile, tabpath FROM tabs WHERE tabid = @pageid

If you're curious about the @pageid thing, have in mind that, when using the query in ListX, this will represent a parameter which will be replaced at runtime with the given tab id. The tabid field is a primary key for the Tabs table, so we're using this WHERE clause in our details query to ensure that it will return the requested record and that it'll be one and only one record.
Also notice that in Query 1 we give the alias "pageid" to the column "tabid". This is done because "tabid" is already a querystring parameter in DotNetNuke and we wouldn't like our Details module to confuse this querystring parameter for our own. More details on this will follow.

Finally, notice that in the details query we're just pulling some more columns from the Tabs table. I'm trying to keep this simple, so I'm not pulling too many columns - just enough to show a visual difference between list data and detail data.

It's time to create our first ListX component which will be the "master" part of our Master-Detail interface. Just add a ListX instance on any page you like (no details on that! If you're reading this you know how to do that! :).

- Click "View Options" on your new module's drop-down menu and you'll be transferred to the View Options page.
- Click the "Query" icon to show the Query section and query no.1 like this:

- Click "Save and Continue".
- Click the "Format" icon (you can click the "Query" icon a second time to hide the Query section).

Our next step is to define the template for showing our data. Click the pencil next to "List Item Detail" to edit the list item detail template and define a very simple template, like the following:

Note that this template doesn't provide us with any links in order to connect with the Details part of our page, but we'll add the markup for that later.


- Click "Save and Continue".
- Click on the "Format" icon again to make the section hide, if you like.


For now, we'll finish by checking the "Show all records" checkbox on the General section of the View Options page for our module, so that we can see the records. Click the "General" icon and check the "Show All Records" checkbox, as in the picture:



- Click "Save Configuration" to return to your page.


That's how our first result will look like:




So we've got a list which comes from a query made on our Tabs table, which contains links that lead nowhere - for the time being. Let's start preparing our second ListX module instance - the details section. For that, we'll have to add another ListX instance on our page, directly beneath the first one. So, add a second ListX instance now.


For reasons of not being too verbose, I'll be skipping the steps on how to go to the various sections of the View Options page from now on. Be sure to click "Save and Continue" after you have finished a series of actions so as not to accidentally lose your configuration.


Working with our second ListX instance (the "detail" instance), we must also specify a query to pull data from our Tabs table. We'll use Query no.2 here, as follows:




and a simple template in the Format section under List Item Detail for presenting our data:

As usual, don't forget to check "Show all records" in the General section of our second module's View Options.

This is going to present us the details of a record selected in our first ListX module instance - but in order for this to work, we need to define one more thing in our Detail instance: The @pageid query parameter. This parameter will be coming from the QueryString (that's the way ListX works), so we've got to issue something like the following in the Variables section of View Options:

Details on how to do that (need to be a bit more explanatory here):

- In View Options, click the "Variables" icon.
- The Variables interface appears. Click "New Query Variable"
- Choose "QueryString" as the variable type from the Variable Type combo box
- Enter "pageid" at the Source textbox
- Enter "@pageid" at the Target text box
- Click "Save"

Remeber what I told you about querystring parameters? Tabid is already a querystring parameter (if you're using friendly URLs you'll see it appearing as /tabid/xxx where xxx is your page's id, and if you're not using friendly URLs you'll see it appearing as /default.aspx?tabid=xxx). Whatever the case, we've GOT to use another name for our parameter, hence the AS alias in our Query no.1 in the first place in order to be concise with the naming.

- Click "Save Configuration". You will return to your page.

What we did was to define a variable that will be contained in the querystring and will have the name "pageid". The value of that variable will go into the query parameter @pageid (remember our query?) so essentially our WHERE clause will get a record with a specific tab id.

But hold on. Aren't we supposed to make our first (master) module instance communicate in some way with our second (detail) instance and pass that variable?

Yes, we are. Since we've got the tab id value for every record in the first place (remember Query no.1), now it's the time to use it. Let's go to our first module's View Options and edit the Template section as follows:

Notice the HREF part of our link. The lxFetch function is a JavaScript function defined by ListX and, in short, refreshes a given module passing it any number of name/value pairs for querystring variables needed.

The first argument is the module ID of the module we want to refresh (fetch). There are a number of ways to get to know the module number, the simplest of which is to look for the "mid" parameter value in our querystring when in View Options or Module Settings for our ListX module. In our case, our Detail module's id is 375.

You can find some information for the second argument in the ListX documentation, but it's ok for our example if we leave it with the default value 0.

The third argument specifies the name/value pairs that we want to pass to the module that will be fetching data (i.e., our detail listx module with module id 375). The [pageid] string will be replaced with the specific record's tab id (remember: tabid AS pageid) at runtime, so we'll have a different value for each record shown. This value will be passed to our "detail" listX module as a querystring variable (although the DNN page itself won't be refreshed and thus you won't be seeing the pageid variable in the actual query string) and it'll be used by our query as we specified.

The result is a master/detail interface in which the detail module's content will change WITHOUT reloading the page each time we click on one of our master module's links. Of course, you can extend that example and use your own table with better formatting on your master / detail modules (your master module's layout can easily become a multicolumn grid and your detail module's layout can easily be transformed to a neatly-aligned detail view)

This is a screenshot of our final result:

By clicking on any link on the first ListX module, the contents of the second ListX module change reflecting the selected record's details without the page being reloaded.

Of course, there are some more issues like what you can show when the page first loads (when there's no selected record), which are beyond the scope of this article. It's easy, though, to have the second module hidden at first, or have it present a message like "Please select a record".

Happy ListX'ing!

Read more...