Do You Have an Update Script for Browse Page Widths – Upgrade from v5.x to v6?

Answer

The one downside to this is that when upgrading a site from v5.x the browse pages would take on their windowwidth versus a blank width and as such they were expanding past the page defaults and in turn making the form render in a strange fashion.

The below script will update all browse pages and move the width property to the new attribute in the options column for both width and height.

Support for SQL version 2008+ (due to the use of sql:variable within the script).

Please run the below after your site has been upgraded though the site manager and as always ensure a database backup is available in the unlikely event of an error and a rollback is required.

The script is a cursor that is going to loop through all browse page controls and update as required, it is all SQL transaction based so in the event of an error it will rollback and display the relevant error message.

The end result is all browse pages with a blank value in windowwidth and a value in width will have this reversed, all browse pages without a windowwidth will have one added and width updated.

The same applies for the height attribute to windowheight.

DECLARE @controlId BIGINT, @options XML

DECLARE @width BIGINT, @windowwidth BIGINT, @height BIGINT, @windowwidthfound BIT

DECLARE @errorMessage varchar(4000)

SET @errorMessage = ''

BEGIN TRANSACTION [controloption_update]

BEGIN TRY

DECLARE ControlOptionsCleaner CURSOR

FOR

SELECT  [ID], [Options]

FROM  [dbo].[fcEventControl]

WHERE  [Type] = 'Browse Page'

OPEN ControlOptionsCleaner

FETCH NEXT FROM ControlOptionsCleaner INTO @controlId, @options

WHILE (@@Fetch_Status = 0)

BEGIN

SELECT @width   = @options.value('(/options/width)[1]', 'BIGINT')

SELECT @windowwidth  = @options.value('(/options/windowwidth)[1]', 'varchar(max)')

SET @windowwidthfound = @options.exist('/options/windowwidth')

IF (@options.exist('/options/height') = 1)

BEGIN

SELECT @height = @options.value('(/options/height)[1]', 'BIGINT')

END

If @width > 0 AND ((@options.exist('/options/windowwidth') = 0) OR @windowwidth = '')

BEGIN

PRINT 'Found Item'

PRINT @controlId

DECLARE @INTVALUE NVARCHAR(5)

SET @INTVALUE = @options.value('(/options/width/text())[1]', 'NVARCHAR(5)')

SET @options.modify('insert <windowwidth>{sql:variable("@INTVALUE")}</windowwidth> as last into (/options)[1]')

If (@windowwidthfound = 1)

BEGIN

SET @options.modify('delete (/options/windowwidth)[1]')

END

SET @options.modify('replace value of (/options/width/text())[1] with ""')

IF (@height > 0)

BEGIN

SET @options.modify('insert <windowheight>{sql:variable("@height")}</windowheight> as last into (/options)[1]')

SET @options.modify('delete (/options/height)[1]')

END

UPDATE [dbo].[fcEventControl]

SET  [Options]=CAST(@options AS VARCHAR(MAX))

WHERE [ID]=@controlId

END

FETCH NEXT FROM ControlOptionsCleaner INTO @controlId, @options

END

COMMIT TRANSACTION [controloption_update]

CLOSE ControlOptionsCleaner;

DEALLOCATE ControlOptionsCleaner;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION [controloption_update]

CLOSE ControlOptionsCleaner;

DEALLOCATE ControlOptionsCleaner;

SET @errorMessage = ERROR_MESSAGE()

PRINT @errorMessage

END CATCH

GO

Update script for Advanced browse page widths

The below script has been updated to handle a non-width for advanced browse pages (Edit the value 150 as appropriate)

DECLARE @controlId BIGINT, @options XML
DECLARE @width BIGINT, @windowwidth BIGINT, @height BIGINT, @windowwidthfound BIT
DECLARE @errorMessage varchar(4000)
SET @errorMessage = ”

BEGIN TRANSACTION [controloption_update]

BEGIN TRY
DECLARE ControlOptionsCleaner CURSOR
FOR
SELECT [ID], [Options]
FROM [dbo].[fcEventControl]
WHERE [Type] = ‘Advanced Browse Page’

OPEN ControlOptionsCleaner
FETCH NEXT FROM ControlOptionsCleaner INTO @controlId, @options

WHILE (@@Fetch_Status = 0)
BEGIN
SELECT @width = @options.value(‘(/options/width)[1]’, ‘BIGINT’)

If (@options.exist(‘/options/width’) = 0 OR @width < 0 OR @width = '')
BEGIN
SET @options.modify('insert 150 as last into (/options)[1]’)

If (@width < 0 OR @width = '')
BEGIN
SET @options.modify('delete (/options/width)[1]')
END

UPDATE [dbo].[fcEventControl]
SET [Options]=CAST(@options AS VARCHAR(MAX))
WHERE [ID]=@controlId

PRINT @controlId
END

FETCH NEXT FROM ControlOptionsCleaner INTO @controlId, @options
END

COMMIT TRANSACTION [controloption_update]

CLOSE ControlOptionsCleaner;
DEALLOCATE ControlOptionsCleaner;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [controloption_update]
CLOSE ControlOptionsCleaner;
DEALLOCATE ControlOptionsCleaner;
SET @errorMessage = ERROR_MESSAGE()
PRINT @errorMessage
END CATCH
GO

Browse Page with 0 Width specified

The above script has been updated to handle a non-width for browse pages (Edit the value 150 as appropriate)

DECLARE @controlId BIGINT, @options XML
DECLARE @width BIGINT, @windowwidth BIGINT, @height BIGINT, @windowwidthfound BIT
DECLARE @errorMessage varchar(4000)
SET @errorMessage = ”

BEGIN TRANSACTION [controloption_update]

BEGIN TRY
DECLARE ControlOptionsCleaner CURSOR
FOR
SELECT [ID], [Options]
FROM [dbo].[fcEventControl]
WHERE [Type] = ‘Browse Page’

OPEN ControlOptionsCleaner
FETCH NEXT FROM ControlOptionsCleaner INTO @controlId, @options

WHILE (@@Fetch_Status = 0)
BEGIN
SELECT @width = @options.value(‘(/options/width)[1]’, ‘BIGINT’)

If (@options.exist(‘/options/width’) = 0 OR @width < 0 OR @width = '')
BEGIN
SET @options.modify('insert 150 as last into (/options)[1]’)

If (@width < 0 OR @width = '')
BEGIN
SET @options.modify('delete (/options/width)[1]')
END

UPDATE [dbo].[fcEventControl]
SET [Options]=CAST(@options AS VARCHAR(MAX))
WHERE [ID]=@controlId

PRINT @controlId
END

FETCH NEXT FROM ControlOptionsCleaner INTO @controlId, @options
END

COMMIT TRANSACTION [controloption_update]

CLOSE ControlOptionsCleaner;
DEALLOCATE ControlOptionsCleaner;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [controloption_update]
CLOSE ControlOptionsCleaner;
DEALLOCATE ControlOptionsCleaner;
SET @errorMessage = ERROR_MESSAGE()
PRINT @errorMessage
END CATCH
GO