Dynamic Forms allows you to use the value of SOME QueryString parameters in your Default Value SQL Query. Specifically, you can use DSParam1, DSParam2 and DSParam3 with little concern, and you can use your own parameters IF and ONLY IF you FIRST insert the value of that parameter into a HIDDEN field (no other field type will work) on the form (using the Advanced Field Option "Retrieve values from querystring variable for this question". Then just use the parameter's tag in the query like this:

SELECT FirstName + ' ' + LastName AS DefaultValue FROM Users WHERE UserID = '$(DSParam1)'

Two important things to note: First, the query must select a single value and you must name it DefaultValue. Second, you should put apostrophes around the parameter value even though in the table it is numeric. I did this because if you don't have DSParam1 in the QueryString an invalid SQL statement will be generated. MSSQL will convert the parameter and the field type to compatible types, so this will execute successfully. If DSParam1 is not specifed, and NULL value will be returned and the field will have no default value.

The next logical question is "How do I specify a default value for DSParam1, so when it is not specified I can still get something as the default value for the field? The answer isn't quite as simple as the above query, but it CAN be done, and in three different ways (that I could think of). While we're at it, we will also try to handle the situation where an invalid UserID is specified and return the same default value.

1. Use a stored procedure. Create a stored procedure such as this:

CREATE PROCEDURE [dbo].[GetFullName] @UserID nvarchar(10) = 1
AS
BEGIN
  IF @ItemID = ''
    SET @ItemID = 0
  SELECT FirstName + ' ' + LastName AS DefaultValue FROM Users WHERE UserID = @UserID
END

And enter this for the default value query:

exec GetFullName '$(DSParam1)'

If DSParam1 is not specified, or has no value, the Name for the user with UserID 1 will be returned. However, if a non-existent UserID is specified, the stored procedure will still return null, and there will be no default value. I couldn't easily come up with a solution for that problem, so I went to the next option.

2. Create a function such as this:

CREATE FUNCTION [GetFullName] ( @UserID nvarchar(10) = 1 )
RETURNS nvarchar(200)
AS
BEGIN
  DECLARE @Result nvarchar(200)
  IF @UserID = ''
    SET @UserID = 1
  SELECT @Result = FirstName + ' ' + LastName FROM Users WHERE UserID = @UserID
  IF @Result IS NULL
    SELECT @Result = FirstName + ' ' + LastName FROM Users WHERE UserID = 1
  RETURN @Result
END

And the query would look like:

select dbo.GetFullName('$(DSParam1)') as DefaultValue

Again, if DSParam1 is not specified, the name for the user with UserID 1 is returned. But now if an invalid UserID is specified the first query will return null, the second query will be executed, and the same default user's name is returned. Just what was required, but there's a simpler option:

3. Do it all in the query itself.

If a query returns no data the return value is null. This can be used with the 'isnull' function like this:

select isnull((select FirstName + ' ' + LastName from Users where UserID = '$(DSParam1)'),(select FirstName + ' ' + LastName from Users where UserID = 1)) as DefaultValue

All in one neat package! If DSParam1 is not specified, or if an invalid value is specified, the first query will return null. The isnull function will then use the value of the second query, which returns your default user's name.

Which of the three options you use depends on your needs and your personal preferences, and on whether you have the ability to create stored procedures or functions. I prefer the third method for it's simplicity and because it keeps everything all in one place.

It took me hours to figure all this out. I hope this posting saves people the time I spent.

Tracy - You are my favorite forum poster of the week!

Thank you so much for posting this useful information... I know most people only post forum threads when they need something, and it takes a lot of time to also let others know the knowledge you learned and I am sure this will help them.

Thanks,

Chad

I have just discovered a MAJOR difference in the way Dynamic Forms handles DSParam1, etc. QueryString variables and user-defined QueryString variables that are assigned to hidden fields, when the variable is not specified in the QueryString. This difference SIGNIFICANTLY affects the techniques described above!

If DSParam1 is not specifed in the QueryString, it's value is NOT an empty string! Instead, it gets the literal value '$(DSParam1)'. In other words, it is simply NOT REPLACED. Therefore any SQL code you write to handle cases where it is not specifed should check for the value '$(DSParam1)' and NOT an empty string. None of the code above will work correctly for cases where DSParam1 is not specifed. In fact, MSSQL will throw an error if you attempt to compare it against an integer field, since it cannot convert that value to an integer. You would have to modify the stored procedure and function to say:

IF @UserID = '$(DSParam1)'

The 3rd technique would have to be changed to convert the integer field UserID to a string in order to work. For example:

where CAST(UserID AS nvarchar(20)) =

However, if a user-defined QueryString variable is not specifed, and you assign it's value to a hidden field, the hidden field DOES get an empty string, and everything works as described above. (You should probably still cast the UserID field as a string to avoid problems in cases where a non-numeric value is passed, or modify the stored procedure or function to check for a non-numeric value.)

If you simply MUST use the variable names DSParam1, etc. then there is a workaround: simple create a hidden field with the same name ABOVE the point where you want to use it, and get it's value from the QueryString. Then when you use the variables tag, it will use the hidden field rather than the special parameter. In effect, you are CONVERTING the special DSParam1 variable into a normal hidden variable.

Sorry if this caused any confusion. It confused the HECK out of me until I figured out what was going on!