Do You Have a Width Script for All Controls – Upgrade from v5.x to v6?

Answer

The below script will update all controls that have a width property to 150 (This can be amended in the below script as required).

It affects the following controls:

• Simple text box
• Large text box
• Drop down
• Lookup
• Date picker
• Advanced Date picker

DECLARE @controlId  BIGINT, @options XML

DECLARE @width   BIGINT

DECLARE @errorMessage varchar(4000)

SET @errorMessage  = ''

BEGIN TRANSACTION [controloption_update]

BEGIN TRY

DECLARE ControlOptionsCleaner CURSOR

FOR

SELECT  [ID], [Options] FROM [dbo].[fcEventControl]

WHERE  [Type] = 'Simple Text Box'

OR  [Type] = 'Large Text Box'

OR  [Type] = 'Drop Down'

OR  [Type] = 'Lookup'

OR  [Type] = 'Date Picker'

OR  [Type] = 'Advanced Date Picker'

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)

BEGIN

If (@width <= 0)

BEGIN

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

END

DECLARE @INTVALUE NVARCHAR(5)

SET @options.modify('insert <width>150</width> as last into (/options)[1]')

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

Options update for blank column
When trying to update the event controls the above script will not work if the Options column is previously blank.

The following resolves that particular area:

UPDATE fcEventControl SET Options = '<options><width>150</width></options>' WHERE CAST(Options as NVARCHAR) = '' AND Type <> 'Reference Label' AND Type <> 'Command Option'