SyntaxHighlighter

Tuesday, 20 November 2012

MSSQL: Date Last Data Change Occurred on a Table

I needed to find out when the data was changed on a table in SQL Server. Unfortunately, I don't have the article I found it on in the mighty Stackoverflow, but here it is:

SELECT TABLENAME, LASTUPDATED
FROM (
        SELECT B.NAME AS 'TABLENAME',
               MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
        FROM SYS.SYSINDEXES AS A
        INNER JOIN SYS.OBJECTS AS B ON
                A.ID = B.OBJECT_ID
        WHERE B.TYPE = 'U'
        AND STATS_DATE (ID, INDID) IS NOT NULL 
        GROUP BY B.NAME
) AS A
ORDER BY LASTUPDATED DESC

A really useful bit SQL!

Monday, 12 November 2012

HTMLtweaks mBox & mForm with ASP.NET

I was recently looking around for a nice mootools implementation of a modal box and found many. I settled on one that I find brilliant and deserves much credit: HTMLtweaks mBox & mForm by Stephan Wagner.

I needed to use multiple features that mBox & mForm offer, but was struggling to get the correct configuration for it to all work with ASP.NET postbacks. Individually and certain combinations were working great but the not the full compliment for me. I got it working though and here's how:

Scenario
I wanted to use an mForm to make the form and it's elements sweeter, with validation and tooltips (using mBox). I also wanted to take advantage of the mForm.Submit features to get a confirmation when the user attempts to submit the page.

The Problem
The form, validation and tooltip, without the mForm.Submit confirmation options worked fine. When I introduced the confirmation, the form was not validated and submitted. As the form contents were not valid, I was getting an invalid submit.

The Solution
Some info:
  • I have a JavaScript file called core.js that I have included on the page
  • I am using a Master page
Firstly the markup. In the interest of keeping this concise, I have not included the full page. If you have got this far, you know all about that ;)



Email Address
Password

This is a very basic login form (albeit the form element is not shown). I have included the data-required attributes and on the email address asp:TextBox also added the data-validate attribute. Under each element, I have added the markup to be used in the tooltip. Finally, the asp:Button has the CssClass added and the server-side OnClick event tied in. I have included the data-confirm attruibute, with a suitable message and added the a JavaScript function (confirmSubmit()) to the data-confirm-action attribute. Noticed the asp:LinkButton? More on that in a bit...

In core.js, I created a function called doTips(). This processes a JSON array of elements to use to tie the tips into.


function doTips(elements) {
    for (i = 0; i < elements.length; i++) {
        new mBox.Tooltip({
            content: elements[i].tip,
            attach: elements[i].element,
            event: 'mouseenter',
            position: {
                x: 'right',
                y: 'center'
            },
            delayOpen: 500
        });
    }
}

This gets called during the domready event on the page, as such:

doTips([
 {element: '<%=txtEmail.ClientID%>', tip: 'txtEmail_Info'},
 {element: '<%=txtPassword.ClientID%>', tip: 'txtPassword_Info'}
]);

The ClientID of the element to attach is sent, along with the element that has the tip in it.

Now to getting the form to submit with a confirmation and validation! In core.js I created a function called setUpSubmitForm(). This configures the mForm.Submit object.

frmSubmit = null;

function setUpSubmitForm() {
    frmSubmit = new mForm.Submit({
        form: 'fMaster',
        ajax: false
    });
    frmSubmit.validateOnSubmit = false;
}

The keen eye will notice the use of validateOnSubmit being set to false outside the declaration of the object. This is done for two reasons. Firstly, when the submit button is pressed, the form should not be submitted. Secondly, when the object is declared, it must have the validateOnSubmit property set to true in order for the validation to work.

This also gets called in the domready event.

Finally to tie it all together, I put the confirmSubmit() function onto the page. I did it this way to allow for custom actions prior to the validation and submit taking place.

function confirmSubmit() {
 _confirmSubmit('<%=btnLogin.UniqueID%>');
}

It quite simply calls _confirmSubmit that lives in core.js. The key thing to note here is that I am sending through the UniqueID of the login button. This is what _confirmSubmit() does:

function _confirmSubmit(elementUniqueID) {
    if (frmSubmit.validate()) {
        __doPostBack(elementUniqueID);
    }
}

I test to see if the form validates successfully and if so, fire the __doPostBack .NET created function, passing through the control that fired the event. The onClick server-side event now gets picked up.

So, what about the asp:LinkButton - what's that all about? Only certain controls will force .NET to create the __doPostBack  function on the page. By adding an asp:LinkButton, the code is added by the framework. Obvisouly if you have other controls on the page that create the function, then it doesn't need to be added.

Another Point...
Depending on your site configuration, you may get an Invalid postback or callback argument exception. There are a few ways to get round the problem and they are pretty well documented all over. Using EnableEventValidation in the page directives or web.config is one option, but a safer method is to use the Page.ClientScript.RegisterForEventValidation(myButton.UniqueID). I haven't got the ClientScript option working yet, but I'm sure I'll work it out at some point.