Dynamic User Interface to accept values for a Parameterized Report

Hi,
Can any one help me to generate dynamic user interface so that end user
can give inputs to a parameterized report.I am using reporting services and
asp.net.Pls send if any one has code or link.
Thanks in advance
regards
ajay kannaHi,
All you have to do is refer the book "Hitchiker Guides to Reporting Service"
. that has the solution and sample code that you asked for.
let us know how it goes.
Thanks
Bava
"ajay" wrote:
> Hi,
> Can any one help me to generate dynamic user interface so that end user
> can give inputs to a parameterized report.I am using reporting services and
> asp.net.Pls send if any one has code or link.
> Thanks in advance
> regards
> ajay kanna
>|||Can i know how why need a dynamic user interface in the first place. dont you
find the current one provided by report manager is good.
"ajay" wrote:
> Hi,
> Can any one help me to generate dynamic user interface so that end user
> can give inputs to a parameterized report.I am using reporting services and
> asp.net.Pls send if any one has code or link.
> Thanks in advance
> regards
> ajay kanna
>

Dynamic SQL Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql = ' SELECT
...
FROM
...
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom = ' SELECT
...
FROM
... '
SET @.sqlWhere = ' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters to
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:
> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql => ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom => ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere => ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statement
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere = ' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:
>Hi
>I think you should be looking at what you are trying to achieve and possibly
>split it up, rather than trying to get a statement of over 4000 characters to
>execute. There may be scope to use views and or functions to reduce the size
>a little. As you concatenating your input variables you may still be subject
>to SQL Injection, use of the QUOTENAME function may help.
>John
>> I have created a parameterized statement in a T-SQL procedure that uses
>> sp_executesql such as the following:
>[quoted text clipped - 63 lines]
>> 2. In the Revised statement, specifically the INSERT, can the variables be
>> assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.com|||Hi
It would be the second option.
John
"cbrichards via SQLMonster.com" wrote:
> Thanks John. Given the Revised statement, would I use the QuoteName function
> within the Set Statement:
> SET @.sqlWhere => ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> >Hi
> >
> >I think you should be looking at what you are trying to achieve and possibly
> >split it up, rather than trying to get a statement of over 4000 characters to
> >execute. There may be scope to use views and or functions to reduce the size
> >a little. As you concatenating your input variables you may still be subject
> >to SQL Injection, use of the QUOTENAME function may help.
> >
> >John
> >
> >> I have created a parameterized statement in a T-SQL procedure that uses
> >> sp_executesql such as the following:
> >[quoted text clipped - 63 lines]
> >> 2. In the Revised statement, specifically the INSERT, can the variables be
> >> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic SQL Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql =
' SELECT
..
FROM
..
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom =
' SELECT
..
FROM
.. '
SET @.sqlWhere =
' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
Message posted via http://www.droptable.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters t
o
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:

> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql =
> ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also
am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom =
> ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere =
> ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statemen
t
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.droptable.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere =
' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:[vbcol=seagreen]
>Hi
>I think you should be looking at what you are trying to achieve and possibl
y
>split it up, rather than trying to get a statement of over 4000 characters
to
>execute. There may be scope to use views and or functions to reduce the siz
e
>a little. As you concatenating your input variables you may still be subjec
t
>to SQL Injection, use of the QUOTENAME function may help.
>John
>
>[quoted text clipped - 63 lines]
Message posted via http://www.droptable.com|||Hi
It would be the second option.
John
"cbrichards via droptable.com" wrote:

> Thanks John. Given the Revised statement, would I use the QuoteName functi
on
> within the Set Statement:
> SET @.sqlWhere =
> ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere =
' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:[vbcol=seagreen]
>Hi
>I think you should be looking at what you are trying to achieve and possibl
y
>split it up, rather than trying to get a statement of over 4000 characters
to
>execute. There may be scope to use views and or functions to reduce the siz
e
>a little. As you concatenating your input variables you may still be subjec
t
>to SQL Injection, use of the QUOTENAME function may help.
>John
>
>[quoted text clipped - 63 lines]
Message posted via http://www.droptable.com|||Hi
It would be the second option.
John
"cbrichards via droptable.com" wrote:

> Thanks John. Given the Revised statement, would I use the QuoteName functi
on
> within the Set Statement:
> SET @.sqlWhere =
> ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>