Ability to Manually Enter a Parameter in a Queried Parameter

I have a queried parameter getting it's parameters from its own dataset.
Works great. Now they want to have the ability to enter their own if they
know what value they want rather than scrolling through the list.
Any ideas on how to go about this?
ThanksAnybody?
"BillD" wrote:
> I have a queried parameter getting it's parameters from its own dataset.
> Works great. Now they want to have the ability to enter their own if they
> know what value they want rather than scrolling through the list.
> Any ideas on how to go about this?
> Thanks
>|||Chris,
Thank You for taking the time to reply. Looks like I'll be getting into some
ASP as I also like to have a calendar for date parameters...
"Chris Baldwin" wrote:
> > "BillD" wrote:
> >
> >> I have a queried parameter getting it's parameters from its own
> >> dataset. Works great. Now they want to have the ability to enter
> >> their own if they know what value they want rather than scrolling
> >> through the list.
> Hello BillD,
> I'm not sure there's a way to do this when rendering through Report Manager.
> If there is I'd love to hear about it. But, I know you could easily create
> a custom webform that accomplishes this goal.
> For example, you can use JavaScript to determine whether the text box has
> been typed into and adjust the form action that requests the report accordingly.
> Here's a snippet that I used in one of my apps that might help:
> <script>
> function process()
> {
> var actionBase = "http://localhost/reportserver/myreport?rs:Command=Render&ParamVal=";
> var theForm = document.getElementById("myForm");
> var txtBox = document.getElementById("myBox");
> var selectBox = document.getElementById("mySelect");
> // If the textbox is empty, use the select box value
> if(txtBox.value == "")
> theForm.action = actionBase + selectBox.value;
> else
> // otherwise, use the text box value
> theForm.action = actionBase + txtBox.value;
> theForm.submit();
> }
> </script>
> <form method="get" id="myForm">
> <select id="mySelect">
> <!-- would be populated from database -->
> <option>Val1</option>
> <option>Val2</option>
> <option>Val3</option>
> </select>
> <input id="myBox" type="textbox">
> <input type="button" value="Submit" onClick="process();">
> </form>
> -chris
>
>sql

A Way To Go To First Row In A Parameter's DataSet?

We have cascading parameters in many of our reports. Many of the subsequent parameters after the previous ones have been selected, will result in a dataset with just one or two rows. Is there anyway to have Reporting Services select that first (and many times the only) record in that dataset?

Thanks!

Unfortunately no, there isn't.

A tough nut to crack

I have a class C# which dynamically generates the SQL to create a stored procedure, one of the parameters i write into to the SQL is @.Keyname, and i use it in the following way

WHERE @.KeyName = CONVERT(VARCHAR, @.KeyValue)

However when i run the generated SQL to actually create the stored proc is is created with the line exactly as it is above. I need to be able to take the keyname as specified when the proc is called, which is a quoted string (i.e 'keynamefield' and write it into the SQL as keynamefield without quotes to make the field lookup dynamic based on this keyname field.

I am presuming that i will need to use function against the keyname variable inside the stored procedure (as this cannot be called from C#) something like GetValue(@.KeyName)

??

Any Ideas would be greatly appreciated

You need to use dynamic SQL but that is not what you want to do. You should not write applications that passes column names and table names dynamically for manipulation. There are lot of security risks, performance issues among other things. Best is to create the SP in such a manner that you don't need dynamic SQL. There are many ways to do this. The link below discusses the techniques to do something like this:

http://www.sommarskog.se/dyn-search.html

|||

As Umachandar said, its highly risk to create a object from your code. Your database is widly open to any one. Security Issues..

Comming to your issue, you have to use NVARCHAR instead of VARCHAR for unicode characters(non-english alphabets).

|||

Got it sussed thanks, the procs use the sql in a pre-generated fashion (i.e the compiler calculates the select and then it is added to a stored proc and which sql is executed is controlled by paremeters.) The procs are also encrypted so no one can execute any code they fancy on the database. I wonder though, can you encrypt tables the same way toy can procedures (i.e. WITH ENCRYPTION)?

Thanks

|||No. You can't encrypt the table definitions.

A question about varchar parameters

I've created a stored procedure that takes a varchar(10) as a parameter. However calling this stored procedure from an ASP page, with a string of greater length, generates an error. However this does not happen in Query Analyzer (it simply truncates the string to 10 characters). I was under the previous impression that this truncation was implicit, but now it seems that it is not. Can someone please give me a quick overview of how to work around this issue (is there an SQL setting I can flip on). I know I could pre-truncate every value in my page, but that seems like a design nightmare (seeing as how I would need to know the size of every varchar parameter in every stored procedure old and new, also I'd like to be able to simply increase the size of the data field in the table, at a later point, without having to match it up in every stored procedure and ASP page ).

P.S. I am using SQL Sever 2000

How did you call the stored procedure from your code? I use SqlConnection and SqlCommand to call the sp with a Parameter, it succeeded even I input a string with length greater then the length defined for the stored procedure parameter, as what happened in Query Analyzer.

So I guess your exception came from ASP .NET, not SQL. Did you call the stored procedure using OleDbCommand and specify the length for the parameter on the application side?

A good TSQL editor/debugger

Greetings.
Which is the best tool to use for editing SQL , and debugging SPs?
Must have features are auto-complete of column members and SP parameters and
good query plan/server trace outputs.
Thanks.
Elias.
Hi,
Query Analyzer is the best tool to do all. You could do all the belwo:-
1. Execution plans
2. Set statistics IO ON, SET STATISTICS TIME ON
3. Index tuning
4. Debugging
The list goes on and on...
Thanks
Hari
SQL Server MVP.
"elias448" <elias448@.discussions.microsoft.com> wrote in message
news:E3D4D66B-EDD4-4844-B0F0-37EDD8AEC284@.microsoft.com...
> Greetings.
> Which is the best tool to use for editing SQL , and debugging SPs?
> Must have features are auto-complete of column members and SP parameters
> and
> good query plan/server trace outputs.
> Thanks.
> Elias.
|||Thanks for the reply. I was looking for a tool that has more advanced
editing options, for example, auto-completion of table column names (in
the same way Intellisense works in VB).
|||One possibility is SQL-Programmer from BMC which has
intellisense and a debugger:
http://www.bmc.com/products/products..._0_105,00.html
-Sue
On 7 Jun 2005 05:56:11 -0700, "Liakos"
<elias448@.hotmail.com> wrote:

>Thanks for the reply. I was looking for a tool that has more advanced
>editing options, for example, auto-completion of table column names (in
>the same way Intellisense works in VB).
|||FWIW, yesterday I downloaded and started using ApexSQL Edit.
Can't say I've tested it thoroughly yet, but so far I'm pretty
impressed. It does many of the things you mentioned. Download the
free trial and give it a whirl.
http://www.apexsql.com
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On 7 Jun 2005 05:56:11 -0700, "Liakos" <elias448@.hotmail.com> wrote:

>Thanks for the reply. I was looking for a tool that has more advanced
>editing options, for example, auto-completion of table column names (in
>the same way Intellisense works in VB).
|||You should also check out SQL IDE from Imceda Software (http://www.imceda.com). If you're an Oracle person, TOAD reigns supreme. (TOAD is also in beta for SQL Server at http://www.toadsoft.com/toadssbeta.html.)
Hope this helps,
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> Thanks for the reply. I was looking for a tool that has more advanced
> editing options, for example, auto-completion of table column names
> (in the same way Intellisense works in VB).
>
|||Or give http://www.agileinfollc.com DataStudio a try for table editing.
John King
http://www.agileinfollc.com
"Kevin Kline [MVP]" <kevin.kline[NOSPAM]@.quest.com> wrote in message
news:uQqMjlEfFHA.3304@.TK2MSFTNGP12.phx.gbl...
> You should also check out SQL IDE from Imceda Software
> (http://www.imceda.com). If you're an Oracle person, TOAD reigns supreme.
> (TOAD is also in beta for SQL Server at
> http://www.toadsoft.com/toadssbeta.html.)
> Hope this helps,
> -Kev
> ~~~
> -Kevin Kline
> Quest Software (www.quest.com)
> SQL Server MVP
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>