How Can We Help?
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'
Comments are closed.