Dot Net Nuke and the “Remember Me” option

Posted on by
Reply

The DotNetNuke “Remember Me” option on the log in page has always been a bit of mystery because it doesn’t quite do what you would expect it to do.  I expected that when I clicked it on future logins the fields would be filled in automatically and I would just click the login button.  But that’s not what it does and so it never really “appeared” to work.

However, there’s been a fix using the PersistentCookieTimeOut option.  Once set in the web.config the user will be automatically logged in; that is, the login page will be bypassed and the user will be immediately directed to the home page.  To clear this option the user simply does a manual log out instead of just closing the browser.

For some sites this is a problem.  You wouldn’t want this on your banking site, for example.  Someone just has to open your computer, go to the page, and they are in!  However, for other work flows its not so much an issue.  Say you are in and out of the site all day and don’t want to have to keep logging in, but at the end of the day you officially log out.

Here’s the code to make it happen:

In the web.config add the following line to the <appSettings> section

<add key=”PersistentCookieTimeout” value=”20160″ />

Where 20160 is two weeks in minutes, but you can set it to whatever you like.

For more information see this post by Cathal Connolly at DotNetNuke: New Solution to An Old Problem

SQL Server: Group records by Week

Posted on by
Reply

Quick code snippet to group a recordset by week.  In this case we are summing hours worked per week.

SELECT DATEPART(YEAR,myDateField) AS 'Year',
           DATEPART(wk,myDateField) AS 'Week #',
           MIN(DATEADD(wk, DATEDIFF(wk,0,myDateField), 0)) AS 'Week date',
           SUM(COALESCE([Hours],0)) AS 'Hours'
FROM myTable
GROUP BY DATEPART(YEAR,myDateField),DATEPART(wk,myDateField)
ORDER BY 1,2

“Approve” Multiple records in a GridView

Posted on by
Reply

Every once in a while you have a list of records that need to be “approved by a manager.”  You could select the records via a filter, but that always gets a little cumbersome because there’s always one record you have to then individually “un-approve” in order to get it just right.

In this example we put a CheckBox on each row, allow the user to select whichever records they want to approve and then approve them with the click of a button.

1) Build your GridView and display your data. The following code only applies to the current page, so if your GridView allows paging you will want to balance between showing too many records versus showing enough records for an efficient approval process.  Make sure you set the DataKeyNames field to your Table’s Identity field.

2) Add the following row to your GridView:

<asp:TemplateField>
    <ItemTemplate>
        <asp:CheckBox runat="server" ID="chkApprove" />
    </ItemTemplate>
</asp:TemplateField>

3) Add a button to your page

 <asp:Button runat="server" ID="btnApprove" Text="Approve Selected" />

4) Add the code to iterate through the GridView records to the Button Click event.

Protected Sub btnApprove_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnApprove.Click
01   Try
02       For Each row As GridViewRow In grdContent.Rows
03           If row.RowType = DataControlRowType.DataRow Then
04               Dim cChk As CheckBox = CType(row.FindControl("chkApprove"), CheckBox)
05                   If cChk.Checked Then
06                       ApproveMyRecord(grdContent.DataKeys(row.RowIndex).Value)
07                   End If
08           End If
09       Next
10   Catch ex As Exception
11      lblMsg.Text = "Approve error: " & ex.Message
12    End Try
End Sub

Breakdown:
Lines 01, 10-12: Basic Try/Catch error trapping routine.  The lblMsg control is arbitrary.  Display errors as you see fit.
Lines 02, 03, 08, 09: We are iterating through every displayed row in the GridView and checking for DataControlRowType.DataRow rows (this will exclude header and footer rows).
Line 04: Create a CheckBox variable and assign the row checkbox to it.
Line 05, 07: Check to see if the CheckBox is checked and if not, skip it.
Line 06: This is where you run the code to approve the individual record.  Grab the record ID from the DataKeys using the RowIndex value.

You can improve this code by collecting the ID’s and sending them to a Stored Procedure as a list and reducing the number of calls to the database.

SQL – Creating and Dropping Temp Tables

Posted on by
Reply

I run into this all the time but can’t ever remember the syntax.

First, check to see if it exists and if it does, drop it.

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
Drop Table #MyTempTable

Second, build your table.  The “#” is important.  The “#” denotes it as a temp table.  Forget the hash and you’ll be creating permanent tables in your database every time the code runs.

Create Table #MyTempTable
 ( MyID int,
   MyText varchar(50)
 )

When creating you’re table you can include any kind and amount of fields that you can in a regular table. This is just a demo.

At this point you can fill the temp table with records and do whatever queries against it that your code requires as long as they occur in the same context.  Once the code stops running the temp table ceases to be available.

Now, go create some temp tables!

Navigating Excel Cells in VBA

Posted on by
Reply

Fixed Reference

There’s plenty of documentation on how to move around Excel in VBA if you know exactly what cell you want to move to.

For example to move to cell D3 all you need to do is add this line of code:

ActiveSheet.Range("D3").Select

Or let’s say you want to select not just a single cell but a range of cells, like D3 thru E4:

ActiveSheet.Range("D3:E4").Select

Relative Reference

But let’s say you want to run your macro from anywhere on the spreadsheet instead of a fixed location.

For example, you’re cursor is on some cell and you want to move left 2 columns and up 1 row:

ActiveCell.Offset(-1, -2).Select

Or maybe instead you to stay on the same row, but move to the right 5 columns:

ActiveCell.Offset(0, 5).Select

Now let’s say you want to move left 9 columns and up one row.  Once you get there you want to select from the new starting cell to one cell to the right and one row down so that four cells are selected:

ActiveCell.Offset(-1, -9).Select
Range(ActiveCell, ActiveCell.Offset(1, 1)).Select

Now just imagine that once you’ve selected those four cells what you really want to do is copy the values, or the formulas, from the top two cells to the bottom two cells.  Enter the following code all on one line:

Selection.AutoFill Destination:=Range(ActiveCell, _
ActiveCell.Offset(1, 1)), Type:=xlFillDefault

If you need to fill more rows then just extend the range by changing the row offset to whatever number you need.

The Function

ActiveSheet.Range("{cell}").Select
ActiveCell.Offset({row}, {column}).Select

Orphaned SQL Server Users

Posted on by
Reply

The Scenario:

You’re working on a SQL database locally and when you restore the database on the  production server the login no longer works.  The user has been orphaned.

Solution #1 – Delete and recreate the user from scratch

Solution #2 – Restore the orphaned user

Check to see if the server registers any orphaned users.  Open SQL Server Management Studio and select the database you restored.  Run this stored procedure:

EXEC sp_change_users_login 'Report'

The resulting list will confirm what users are orphaned, if any.  As long as the Login exists under the SQL Server Security -> logins you can reattach the user to the login by running this stored procedure:

EXEC sp_change_users_login 'Auto_Fix', '{user name}'

If the login doesn’t exist under the SQL Server Security -> logins you can create a new login for the user using the following stored procedure:

EXEC sp_change_users_login 'Auto_Fix', '{user}', '{login}', '{password}'

Care and Feeding of SQLParameters and SQLHelper

Posted on by
Reply

I chased my tail for hours on this one, but finally found the combination that works.  Here’s the basic layout

Function GetProjects(ID as int64, Name as string)

Dim arParams As SqlParameter() = New SqlParameter(2) {}

arParams(0) = New SqlParameter("@ID", IIf(ID = 0, _
    DBNull.Value, ID))
arParams(1) = New SqlParameter("@Name", IIf(Name = "", _
    DBNull.Value, Name))

Return CType(SqlHelper.ExecuteReader(ConnectionString, _
    CommandType.StoredProcedure, _
    GetFullyQualifiedName("GetAT_LU_Positions"), _
    arParams), IDataReader)

End Function

To instantiate the SQLParameter array you must declare the number of variables available.  In this case, there are two.

To build the parameters I’ve included code to null out the variables if they are not used.  This becomes important if you are creating multiple overloads of the function (in this case the GetProjects function).

When calling the SQLHelper there are several overload possibilities.  In this case we want the overload with the following options: 1-connection, 2-command type, 3-command name, 4-SQL Parameters.  There is one version of the overload that accepts options 1,3, and 4 and will work without an error, but the problem is nothing will be returned.  Obviously, that’s a problem.

This is a very open ended scenario, but the important piece for me was getting the SQLHelper set up and properly called and this was the way to do it.  If you are implementing SQLParameters and SQLHelper and have questions for your particular routine, let me know.

How to Uninstall a DotNetNuke module

Posted on by
Reply

These instructions cover uninstalling a module from DotNetNuke 05.06.03, but should be good for DotNetNuke 5 installs.

If you are having trouble with a module on a particular page and are unable to remove it from the page, uninstalling the module will remove the offending code from the page as well.

1. Login as host to your DotNetNuke site and select “Extensions” from the host menu.

 

2. Find your module in the extensions list (in this case “Widget World”) and click the “X” icon.

3. The uninstall package screen confirms what module you are attempting to uninstall.  If this is correct click “Uninstall Package”.

4. Once the uninstall is done DotNetNuke reports the steps taken.  Any errors will appear in red.

Provided there were no errors you’re all done.  If there were errors you will likely need to delete each file and database component manually.

How to Manually Install a DotNetNuke Module

Posted on by
Reply

These instructions cover setting up a dynamic DotNetNuke module in Visual Studio 2010 and manually installing the module in DotNetNuke.  While these instructions are based on DotNetNuke 05.06.03 they are applicable to all of DotNetNuke 5 versions.

These instructions do not cover in detail how to program each piece of the module, but does detail what files are need for a basic dynamic module and how to install them.

For the sake of clarity we’re going to create and install a module called “Widget World” and our company will be “iWidgets”.

If you’ve already install the Visual Studio Starter Kit follow steps A-D, skip steps 1-3 and continue at step 4.

A. In Visual Studio, with your project loaded, right click on the project root and select “Add New Item”

B. In the dialogue select “DotNetNuke Dynamic Module”.  Dynamic Module includes a database backend.  If you do not need a database backend select “DotNetNuke Simple Dynamic Module”.  Clear all text in the “Name” entry box at the bottom of the dialogue, enter “WidgetWorld” and click “Add”.

C. The Starter Kit will automatically add a folder called ModuleName to both the App_Code and DesktopModule folders.  Rename the ModuleName folder to WidgetWorld in both cases.  In the DesktopModules\WidgetWorld folder you can delete the Documentation folder.  You won’t need it.

D. This is the most tedious part.  Open all of the files that were generated by the VSSK.  Find and Replace all instances of “YourCompany” with “iWidgets”.

Skip steps 1-3 if you used the Visual Studio Starter Kit.  Otherwise start here.

1. In your Project create the following folders on the root of your project:

App_Code\WidgetWorld
DesktopModules\WidgetWorld

2. A dynamic module has a database backend, so you will need several database connecting files in the App_Code\WidgetWorld folder:

WidgetWorldController.vb (entry point to the functionality)
WidgetWorldInfo.vb (maps fields to properties)
DataProvider.vb
SQLDataProvider.vb (makes the actual calls to the database)

To get started the only file that needs code in it is the WidgetWorldController.vb.  Copy the following lines into it:

Namespace CUIM.Modules.CUIM_BusinessDir
Public Class CUIM_BusinessDirController
End Class

End Namespace

3. The Desktop Modules file is a bit more complicated and will have several files and folders

01.00.00.SqlDataProvider (build the sql components for auto install)
WidgetWorld.dnn (base manifest file for auto install)
EditWidgetWorld.ascx and .vb (add/edit control)
Settings.ascx and .vb (special settings for the module)
Uninstall.SqlDataProvider (uninstalls sql components for auto uninstall)
ViewWidgetWorld.ascx and .vb

App_LocalResources\EditWidgetWorld.ascx.resx
App_LocalResources\Settings.ascx.resx
App_LocalResources\ViewWidgetWorld.ascx.resx

*You don’t actually need any code in these files to install the module, although without any code when you place it on a page in DotNetNuke it will be totally anti-climatic.  :)

4. Log in to your DotNetNuke site as “Host” and click on: “Extensions” in host menu

At the bottom of the page click the “Create New Extension” Link.

5. In the Create New Extension page select “Module” from the drop down and wait for the page to refresh.  The “Name” field must match the folder name in the file system.  “Friendly Name” should be a user-friendly title.  Description is a general overview of the module.  In our case this is version one, but in subsequent updates you have the flexibility of using build, major revision, and minor revision settings.  Click “Next”.

6. In Module Specific Details the “Folder Name” will be filled in for you.  The “Business Controller Class” entry can be pulled from the WidgetWorldController.vb file.  The general format can be seen in the figure below.

If the module will be exported from one site to another, check “Is Portable”
If the module contains searchable data, check “is Searchable”
If the module will be upgraded at a future date, check “is Upgradable”
If you are the host for the site and will allow users to select modules, but certain modules require payment first, check “is Premium module”

Click “Next”.

7. General info, just fill in these fields with your information.  Click “Next”.

8. DotNetNuke returns you to the Extensions list and you should see the Widget World module listed near the bottom of the module section.  Click the pencil icon.

7. The configure extension settings screen shows all of the information you’ve entered in the install process.  If you’re planning on selling the module, or distributing the module you’ll want to fill in the “License” and “Release Notes” sections.  To complete the setup click on the “Add Definition” link.

This will bring up the “Create Definition” link. The “Default Cache Time” is typically for content that will not change often.  Since our module is databased we expect the content to change regularly so it’s better to leave the cache time at 0 so we always get a fresh copy.  If the content doesn’t change often, setting the cache will decrease load time.

Enter “Widget World” in the friendly name and click “Create Definition”

Click “Add Module Control”

8. Add the View control which is the default control.  Leave the “Key” field blank for this control.  By leaving the “Key” field blank the module will default to this control when the page containing the module is loaded.  The fields left blank and unchecked are not necessary for a basic install, but you can click on the “?” icons to learn more about them.

The “Source” is the View control itself.  Scroll through the drop down list until you find the control “ViewWidgetWorld.ascx”.

Set “Type” to “View” and click “Update”

9. Add the “Edit” and “Setting” controls by clicking “Add Module Control” again and following the layouts below.  Notice that while “Edit” and “Settings” have different key fields, they have the same “Type” setting.

Your “Module Controls” list should now look like this

10. Assign the module to a page by returning to the home page of your site and select “Add’ from the Edit mode Page Functions menu.

 

 

11. On the new page select the module in the “Add New Module” mode and set the Title, Visibility, Pane, and Insert settings as you need them.

And that should do it!