SQL problem with mult-value text parameter

[magicboy]

We have a report that has a multi value text parameter, this allows the user to enter multiple strings and the report should produce a listing of all records where the customer name begins with any of the strings entered.

We have created a record selection formula on the customer name field of the form {custname} startswith {?CLIENT}. custname is the database field and CLIENT is the parameter.

If we "show SQL query" this formula is translated as...
WHERE (custname LIKE 'Value1%' OR custname LIKE 'Value2%').

This all works fine and the report functions correctly on the local machine, however the problem is that when we put the report on the webserver and run it through the CR Viewer it does not work when more than one value for the parameter is passed to the report (prompt0=Value1,Value2).

If the parameter contains only one value the report works fine but if 2 or more values are passed no report output is produced. We can see that the correct values are getting to the report by printing out the contents of the parameter in the report header so can only assume that it is a problem with the sql when the report is run via the CR viewer.

We are totally stuck so any help would be much appreciated.

 

[synapsevampire]

Perhaps you've a different ODBC driver, I can't think of another obvious reason.

Hvae you checked to see what is getting passed to the database on the Webserver?

Turn on the ODBC tracing and have a look, or ask your DBA to have a look.

You might try constructing the record selection criteria differently (use like or do some front end parsing of the parameter into another array), and just to further qualify, try hardcoding some values in the reocrd selection and see if it does it correctly.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

I don't know that there is a way to send an array parameter through a URL call. The problem probably isn't with the server, but with the call itself. If the user refreshes the report and re-enters the parameters in the active-x viewer itself does the problem occur? Does the report work correctly in eportfolio?

Lisa

 

[synapsevampire]

I didn't realize that a URL was being used, how did you know this, Lisa?

You can store multi parms within CE without a hitch.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[magicboy]

Hard coded values work fine.

I have done some tracing on the SQL being passed to the database and we end up with...
SELECT fields FROM tables WHERE custname LIKE 'value1,value2%'

So it seems that the problem is that the 2 values are just being treated as one when entered via the URL (prompt0=value1,value2),
I have also tried webSource.AddParameter "prompt0", "value1,value2" but this makes no difference.

So the real question is "How do you enter multi-value parameters when running a report over the web?"