Control: Textbox with autosearch

 Show all Hide all

Shows a textbox. When the user types in the textbox a list of search results is shown. The user can choose one value from the list or continue writing. The control only accepts values from the list of search results.

Start value:
Return value: The ID value of the chosen option.

Control container: NewEdit

Supported in: NewEdit Parameter page Multirow

SQL

SQL Call: Search (mandatory)

This call is made repeatedly as the user types into the textbox, and returns a list of search results for the entered value.
Supports custom errors: No
May modify database: No

Parameters:

@TopN mandatory int
Max number of suggestions to return.

For better performance it is necessary to limit the number of suggestions sent from the database to the client.

Always use the exact value of @TopN in the TOP-clause. Returning more values will cause an immediate exception. Returning fewer values when more values are present can cause unpredictable behavior.

You can use the Control height (rows) setting to control how many rows the list of suggestions contains.
@Value mandatory string
The text entered into the textbox.

Resultset: Search results

Repeat mode: repeated exactly once
Row count: zero or more rows

Columns:

<column with ordinal 1> mandatory string
ID value.
<column with ordinal 2> mandatory string
Text shown in textbox.
<column with ordinal 3> optional string
Text shown in listbox.
If not present the values from the second column are used.
InfoHtml optional string
Extra informative HTML to display below the field. This is not supported in multirows.
InfoText optional string
Extra informative text to display below the field. This is not supported in multirows.

SQL Call: ID Lookup (mandatory)

Gets the value to show in the textbox for a given id.

This call is made first when the page is first loaded and every time the control's value is changed by a default value dependency or a JavaScript.
Supports custom errors: No
May modify database: No

Parameters:

@Id mandatory string
ID-value to get the text for.

Resultset: Friendly value

Repeat mode: repeated exactly once
Row count: zero or one row

Columns:

<column with ordinal 1> mandatory string
ID value. Must match the ID value passed to the lookup procedure.
<column with ordinal 2> mandatory string
Text to show.
InfoHtml optional string
Extra informative HTML to display below the field. This is not supported in multirows.
InfoText optional string
Extra informative text to display below the field. This is not supported in multirows.

Default value

SQL Call: Default value

Retrieves the default value for the control.
Supports custom errors: No
May modify database: No

Resultset: Default value

Repeat mode: repeated exactly once
Row count: exactly one row

Columns:

<column with ordinal 1> mandatory string
The default value

Validation

SQL Call: Validation

Performs field validation when the user leaves the field or one of its dependencies is changed. This call is only made if there is a field validation of type SQL set for the field info.
Supports custom errors: No
May modify database: No

Parameters:

@Value mandatory string
The value of the field, the procedure will not be called if value is NULL.

Resultset: Validation messages

Repeat mode: repeated zero or one time
Row count: zero or one row

Columns:

Error optional string
Error message to display. Blocks the user from saving.
Info optional string
Informative message to display. Does not block saving.

Examples

Textbox with autosearch

Textbox with autosearch works like a slimmed down version of "Textbox with popup". The popup is opened automatically as the user types. However, only an ID-value and a friendly value is supported, much like a dropdown control.

CREATE PROCEDURE Example.TextboxWithAutosearchTable_TextboxWithAutosearch
	@Id				varchar(MAX) = NULL,
	@Value			varchar(max) = NULL,
	@TopN			int = 7
AS
BEGIN

	-- SQL Call: ID Lookup
	IF @ID IS NOT NULL
	BEGIN
		SELECT 
			T.TextboxWithAutosearchTableId,
			T.TextboxWithAutosearchTableName
		FROM
			(
				VALUES
					(1, 'alpha'),
					(2, 'beta'),
					(3, 'gamma'),
					(4, 'delta'),
					(5, 'alphaalpha')
			) AS T (TextboxWithAutosearchTableId, TextboxWithAutosearchTableName)
		WHERE
			T.TextboxWithAutosearchTableId = @Id;
		RETURN;
	END


	-- If you need to be able to search for %, [], [^], _ - make sure you use the escape function below.
	SELECT @Value = SoftadminUtil.String_EscapeLikeWildcards(@Value); 
	
	-- SQL Call: Search
	SELECT TOP (@TopN)
		T.TextboxWithAutosearchTableId,
		T.TextboxWithAutosearchTableName
	FROM
		(
			VALUES
				(1, 'alpha'),
				(2, 'beta'),
				(3, 'gamma'),
				(4, 'delta'),
				(5, 'alphaalpha')
		) AS T (TextboxWithAutosearchTableId, TextboxWithAutosearchTableName)
	WHERE
		T.TextboxWithAutosearchTableName LIKE ISNULL(@Value, '') + '%';
END;

Best practice

Escape @Value

Escape searched value.
SELECT @Value = SoftadminUtil.String_EscapeLikeWildcards(@Value);