"select TOP" with parameter...

Hi there.
Does anyone know of a way to get the following statement to work without
using dynamic SQL?.....
I'm trying to get a "select TOP" to work while passing in a parameter as 'N'
for 'select the first N rows...'
declare @.NumberofRecords integer
select @.NumberofRecords = 5
select top @.NumberofRecords
from MyTable
Any info would be appreciated!len
Lookup SET ROWCOUNT in the BOL
"len" <len@.discussions.microsoft.com> wrote in message
news:3643D907-3483-4B3D-9F9A-B7D45EABF6AB@.microsoft.com...
> Hi there.
> Does anyone know of a way to get the following statement to work without
> using dynamic SQL?.....
> I'm trying to get a "select TOP" to work while passing in a parameter as
> 'N'
> for 'select the first N rows...'
> declare @.NumberofRecords integer
> select @.NumberofRecords = 5
> select top @.NumberofRecords
> from MyTable
> Any info would be appreciated!|||You are not selecting anything so maybe this will work
select top @.NumberofRecords myField1, myField2, etc.
from MyTable
if that still fails try
EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
HTH,
Gerard|||Len,
you can't use a parameter with TOP in SQL 2000. In 2005 it is possible
see this article for more details:
http://support.microsoft.com/defaul...kb;en-us;891605
In 2000 and 7.0 you can use the SET ROWCOUNT option to achieve what you
want.
Try something like
Declare @.num int
SET @.num = 8
SET ROWCOUNT @.num
select * from myTable
Order by columnname
Markus|||I just did a quick check in Qry Analyzer and
EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
works, provided offcourse that @.NumberofRecords is a string
Gerard|||"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1130325402.186088.177800@.g14g2000cwa.googlegroups.com...
>I just did a quick check in Qry Analyzer and
> EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
> works, provided offcourse that @.NumberofRecords is a string
It does work, but that's not a parameterised procedure, it's dynamic SQL.
Plenty of reasons to try and avoid using that if possible.
Dan|||> select top @.NumberofRecords myField1, myField2, etc.
> from MyTable
This will fail because TOP does not take parameters in SQL Server 2000!

> EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
This will fail because (a) @.NumberOfRecords can't implicitly be concatenated
into a string and (b) you didn't leave a space after top, so it will come
out with a syntax error like this:
select top5 myColumn, myColumn2, ...
Please see http://www.sommarskog.se/dynamic_sql.html to understand why
dynamic SQL is not always the best knee-jerk reaction to a problem...|||Yes yes yes,
I know about the limitations/dangers/restrictions with/of dynamic sql,
I was a bit quick of the gun maybe.
And thanks for checking on the punctuation, e.g. the space after the
top.
On your point b)

> works, provided offcourse that @.NumberofRecords is a string
which would, I forgot to mention, require an additional variable and a
CONVERT if the initial variable is an int.

"Refresh" an SQLDataSource object programmatically

Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.

Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.

My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need.

Any ideas, oh great and wise Forum readers? *smile*

I don't think I have fully understood your problem. But when the user clicks search button you need to refresh the sqldatasource and call your_repeaters_or_datalist.DataBind() to refresh repeaters or datalist. If you have code in Post_Back you don't want to trigger you can include it in
if (!Page.IsPostBack)
{
////your code
}

|||

Thanks for the response. Hopefully this helps clarify my situation: The source page has several text boxes that mirror those on the target page. When a specific button on the source page is clicked, the contents of the source text boxes is written to the Session object and a redirect cross-posts to the target page. The Page_Load on the target page checks IsPostback and if it is a cross-page post, the code checks the origin of the action (via a Session variable), and updates the textboxes on the target page with the search parameters. In code, I poppulate a listbox (with results of a SQL stored procedure) with potential matches. If more than one match is returned, I want it to stop and await user interaction (And I have it at that point.) However, if only one match is returned, I have tried to save the key record value back to the Session object, so it can be available to the SQLDataSources that use it as its source value. This is not occurring.

IIn your response, you mentioned "you need to refresh the sqldatasource and call your_repeaters_or_datalist.DataBind() to refresh". That (refreshing the sqldatasource) is what I am trying to do. How can I get them to refresh without issuing an additional postback to the target page? Is there a method that I need to invoke on the sqldatasource objects to do that? It sounds like I am close, but not quite sure what to do at this point.

|||

Is there a method that I need to invoke on the sqldatasource objects to do that?

yes. Assume DataList control yourdatalist is bound to sqldatasource camDS,call following code to refresh:

camDS.SelectCommand ="searchcam";
camDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
camDS.SelectParameters.Add("keyword1","para1 ");
yourdatalist.DataBind();

How can I get them to refresh without issuing an additional postback to the target page?

I don't think this can be achieved without post back since data is saved at the server.

"Order by" by parameter in stored procedure

I would like to pass the name of a column to a stored procedure, so the
result of the query would be ordered by that column.
I tried this:
CREATE Procedure ProductsByTab
(
@.TabID int,
@.Order nvarchar (50)
)
AS
SELECT
*
FROM
Product
WHERE
TabID = @.TabID
ORDER BY
@.Order
GO
but I get this message:
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
variable as part of the expression identifying a column position. Variables
are only allowed when ordering by an expression referencing a column name.
Is it possible to do what I want? What am I doing wrong?
Thank you.i have the same thing but i use a different approach, i pass a flag
representing the numeric order of the field
select * from ttt
order by case @.flag when 1 then name when 2 then description else '' end,
case @.flag when 3 then amount else 0 end,name,description,amount
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||Thanks! Your replies were absolutely efective.

"Missing Parameter Values" Error shown when subReport includes in Main Report

Dear all,

There is an error,"Missing Parameter Values" shown when sub-report includes in the Main report.
(And there is no parameter setup in sub-report)
What i do is to setup the datasource into sub-report by coding..

I find that they runs fine when the main report, sub-report are separated.

CAn anyone give me some ideas about the solution?

P.S. The platform i am using is VS 2005.. Thanks much

michaelDid you link your subreport to your main?
GJ|||there is no link between the subreport and the main report. Maybe say, main report and subreport are totally 2 different kinds of report.

P.S. the subreport is located in report footer

Thx

"Global" Report Parameters

Hi,
I have a report with 3 different datasets. How can I add a parameter to
this report which works or is valid for all the 3 datasets?
ThanksYou should be able to simply add a parameter to the report. These variables
can be accessed by any dataset.
HTH,
Brian
"florian" <florian.stammer@.gmail.com> wrote in message
news:1160661960.136268.105290@.h48g2000cwc.googlegroups.com...
> Hi,
> I have a report with 3 different datasets. How can I add a parameter to
> this report which works or is valid for all the 3 datasets?
> Thanks
>|||I think what you are missing is the concept that there are query parameters
and report parameters. RS creates a report parameter for each query
parameter for you and maps them. You can change this mapping by clicking on
the ... in the dataset, parameter tab. You can map to an expression or to a
report parameter. In the layout tab menu report-> report parameters you can
remove, rename, add parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"florian" <florian.stammer@.gmail.com> wrote in message
news:1160661960.136268.105290@.h48g2000cwc.googlegroups.com...
> Hi,
> I have a report with 3 different datasets. How can I add a parameter to
> this report which works or is valid for all the 3 datasets?
> Thanks
>