Iterate through list of parameters

Hello,
I want to concatenate a string like
=Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
+ Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
+ Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
+ ...
But this should be universal for all reports and universal for
single/multivalue parameters (where JOINs would be necessary).
So my question:
Is it possible to iterate through the list of parameters in custom code or
in an assembly to return such a string in a single, report-independant way?
Kind regards,
R."Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>
sure you can ... here is a function I used in a previous report :
function createparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function
enjoy
Peter|||Ralph,
I have used the Join function to concatenate the selected values of my
multivalue parameters like this:
=Join(Parameters!Para1.Label + ": " + Parameters!Para1.Value, ", ")
if you need to wrap anything in quotes you will need to modify, say add
quotes around the whole thing:
=Join("'" + Parameters!Para1.Label + ": " + Parameters!Para1.Value, "', '")
Hope this helps
Reeves
"Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>|||Hello Peter,
thank you very much for your help. Maybe I do not understand your code, but
I can't see the solution to my problem.
So I try to explain in more detail:
I wan't to print out the filters a user selected for displaying a report,
containing all filter descriptions and values. And so I think I need to
write a function like
function concatenateparameter(ByVal param as object) as string
but I do not know how to specify the object "param" which has to consist auf
all available parameters and their descriptions.
Kind regards,
Ralph
"Peter De Rop" <PeterDeRop@.discussions.microsoft.com> schrieb im Newsbeitrag
news:507E6A77-B3E6-4B26-8171-D5CB69764E68@.microsoft.com...
>
> "Ralph Watermann" wrote:
>> Hello,
>> I want to concatenate a string like
>> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
>> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
>> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
>> + ...
>> But this should be universal for all reports and universal for
>> single/multivalue parameters (where JOINs would be necessary).
>> So my question:
>> Is it possible to iterate through the list of parameters in custom code
>> or
>> in an assembly to return such a string in a single, report-independant
>> way?
>> Kind regards,
>> R.
>>
> sure you can ... here is a function I used in a previous report :
> function createparameter(ByVal param as object) as object
> dim strNewParam() as string
> dim countery as int32
> dim x as object
> for each x in param
> countery = countery + 1
> next
> redim strNewparam(countery + 1)
> dim counterx as int32
> counterx = 0
> for each x in param
> strNewParam(counterx) = x
> counterx = counterx + 1
> next
> strNewParam(counterx) = ""
> return strNewParam
> end function
> enjoy
> Peter

Issue with SQL Parameter when used with % character.

Hi all,

is there a known issue when using a SQL Paramater in a SQL string that uses a '%' character for wildcard string searches?

For example, if I used the following...

SQLCeParameter pKeyword = new SQLCeParameter("@.Keyword", SQLDbType.Char);
pKeyword.value = "Some Text";

string strSQL = "SELECT * FROM tbl_Keywords WHERE (Keyword = @.Keyword%)";

This just never returns and results. But if I run the query in a query analyser window replacing the @.Keyword% with "Some Text", I get results.

I guessing that the placing of the character '%' in the string is the cause maybe...

try putting the % in the string...

pKeyword.value = "Some Text%";

|||

for wildcard searches, you need the LIKE statement. example:

SELECT * FROM someTable WHERE FieldName LIKE @.parameter + '%'

|||Sorry, my query does actually have the LIKE statemet, its just when I post the thread I wrote the query off the top of my head.|||

try this

string strSQL = "SELECT * FROM tbl_Keywords WHERE Keyword LIKE" + @.Keyword + "%";

|||

>>This just never returns and results. <<

What does this mean? It returns nothing? Or it takes forever?

Check the plan of the query using the Display Estimated Plan feature. You should see a very large hotspot in there somewhere that is taking large amounts of the plan (since you are only dealing with one table, it might not be as useful). Then run it without the wildcard and see how the plan looks.

My guess is that it is just taking forever. And is there lots of data? Is that column indexed? An index will work with a LIKE 'value%' criteria, so that shouldn't be a problem. It could be that your indexes aren't optimized too, but first things first :)

|||My Issue is that I no resuts are being returned. I doubt it is taking forever as I am doing a search on a table that has no more that 100 records.

Thanks|||

so have you tried my suggestion earlier?

if you copy and paste your query into query analyzer or SQL management console or something, and execute it - does it bring back results?

so do you get results now, or are you have a perf issue?

another way to increase the perf really is to use stored procedures, if you havent already - safer and faster than standard SQL Injection as you maybe doing ;-)

Issue with 2003 virtual server and ASP.NET 2.0 Application

I'm using Sql server 2005 Express.

my connection string is as fallows:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ccinfo.mdf;Integrated Security=True;User Instance=True

When testing this website on my local computer everything works fine. But then I uploaded it to my server... dun dun dun....

Its a godaddy virtual server running windows server 2003.

When I try to connect to the database stored in my app_data folder I get this error message:

'Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. '

What does this mean?

There was a similar discussion on a different forum (SQL Server Database Engine) that hopefully contains enough information to help you on this particular scenario. You can find it at: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1.

Please, take a look to this link, and if the information posted there doesn’t help you, please let us know.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server engine

|||

I figured it out.

the problem was I needed to detach my mdf file from my local sql server before I uploaded the database to the host server. thanks anyway

|||

I was wondering if you could provide any more information about what was causing the problem, and how detaching the mdf files fixed it?

I'm having the exact same problem on the exact same server setup described in the original post. I have already read the other threads mentioned above and tried detaching the mdf files, but nothing seems to work.

I was able to find a work around that gets past the error, but that introduces its own strange issues. Basically, if I go into IIS Manager and set the website to impersonate a user with sufficient access rights, it works -- BUT, only if I first login to the server as that user (via remote desktop, since it's a remote virtual dedicated server). I have to do this every few hours (login as the impersonated user, that is) or the app goes back to giving the same error:

"Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed."

I can't figure out what logging in accomplishes -- unless somehow that lets ASP .NET or SQL Express retrieve the user's local app path? I don't need to start or run the app from the user's account -- just login to it.

The other thing I don't understand is if user instances in SQL Server Express Edition cause the system DBs to be copied into the user's local application path (which seems to be related to this problem), where do the default NETWORK SERVICE or ASPNET accounts have the files copied? I know that ASP .NET uses the Default User when no local app path exists, but I can't confirm what SQL Server Express does. I just know that giving "Everyone" write access to the SQL Express directory under Default User does not help.

If anyone can shed any light onto this situation, I'd really appreciate it. I've spent the last few days reading through everything I can find related to this issue, but can't find an explanation. I realize that not using user instances would eliminate the problem, but this should work, and I'd really like to figure out what's going on here.

|||

User instances (RANU) is a feature that was originally designed for interactive users and it requires a local profile. Typically the profile is created when a user interactively logs in a machine.

The error message suggests that the user you are impersonating in your web application has not login to this server; and from your description (things work after you login in the machine), I think this is the case indeed.

I assume you are trying to use the attach database feature in RANU, but probably a different approach may work for your scenario. For example, create the user instance with attach database using a fixed (low privileged) Windows user for your application, and allow other users to connect directly via the instance named pipe (you can find the pipe name in the management view sys.dm_os_child_instances).

The main idea behind RANU is to allow any usre in the machine to run their own (isolated) copy of SQL Server as an application running under their own context. A copy of all default databases (including master) is copied to the application path. These files are not copied from the parent instance, instead, they are copied from the SQL Express “Template Data” folder. The only exception to this rule is resource database, a read-only system database that is shared across all instances.

I hope this information helped to answer your questions, and please, let us know if you have other questions.

-Raul Garcia

SDE/T

SQL Server Engine