Parameter problem..help is needed please

Can someone please help identify where the problem exist in this code. A
connection with an Access database (in this case)is established through the
GUI. The snippet of code is aimed to implement two parameters: an input
parameter and an output parameter. The user inputs a value in textbox1 to
serve as input for parameter @.BizName. The output parameter is @.BizAddress.
It’s value is captured in variable i and placed in textbox2.
Why do I get this error message:
Server Error in '/WebApplicationAccessParam' Application.
Parameter 1: '@.BizAddress' of type: String, the property Size has an invalid
size: 0
This is the code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim i As String
Dim dsResult As New DataSet
Dim strSQL As String
strSQL = "SELECT @.BizAddress=Address FROM testTable WHERE Name =
@.BizName"
Dim oleAdapter As New OleDbDataAdapter(strSQL, OleDbConnection1)
oleAdapter.SelectCommand.Parameters.Add("@.BizName", OleDbType.VarChar)
oleAdapter.SelectCommand.Parameters("@.BizName").Direction =
ParameterDirection.Input
oleAdapter.SelectCommand.Parameters("@.BizName").Value = TextBox1.Text
oleAdapter.SelectCommand.Parameters.Add("@.BizAddress",
OleDbType.VarChar)
oleAdapter.SelectCommand.Parameters("@.BizAddress").Direction =
ParameterDirection.Output
i = oleAdapter.SelectCommand.Parameters("@.BizAddress").Value
TextBox2.Text = i
oleAdapter.Fill(dsResult, "testTable")
DataGrid1.DataSource = dsResult
DataGrid1.DataMember = "testTable"
DataGrid1.DataBind()
OleDbConnection1.Close()
Many thanks in advance.I think you need to add a size to your .Add() function, like this:
oleAdapter.SelectCommand.Parameters.Add("@.BizName", OleDbType.VarChar, 50)
I used a default of 50, because that's what Access defaults to for those
type fields, but you should change it to match the size of the field in your
database.
Same thing with the @.BizAddress parameter.
Thx,
Mike C.
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:51A57F18-B570-4311-859C-3A0B7357FC6C@.microsoft.com...
> Can someone please help identify where the problem exist in this code. A
> connection with an Access database (in this case)is established through
> the
> GUI. The snippet of code is aimed to implement two parameters: an input
> parameter and an output parameter. The user inputs a value in textbox1 to
> serve as input for parameter @.BizName. The output parameter is
> @.BizAddress.
> It's value is captured in variable i and placed in textbox2.
> Why do I get this error message:
> Server Error in '/WebApplicationAccessParam' Application.
> Parameter 1: '@.BizAddress' of type: String, the property Size has an
> invalid
> size: 0
> This is the code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim i As String
> Dim dsResult As New DataSet
> Dim strSQL As String
> strSQL = "SELECT @.BizAddress=Address FROM testTable WHERE Name =
> @.BizName"
> Dim oleAdapter As New OleDbDataAdapter(strSQL, OleDbConnection1)
> oleAdapter.SelectCommand.Parameters.Add("@.BizName",
> OleDbType.VarChar)
> oleAdapter.SelectCommand.Parameters("@.BizName").Direction =
> ParameterDirection.Input
> oleAdapter.SelectCommand.Parameters("@.BizName").Value =
> TextBox1.Text
> oleAdapter.SelectCommand.Parameters.Add("@.BizAddress",
> OleDbType.VarChar)
> oleAdapter.SelectCommand.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
> i = oleAdapter.SelectCommand.Parameters("@.BizAddress").Value
> TextBox2.Text = i
>
> oleAdapter.Fill(dsResult, "testTable")
> DataGrid1.DataSource = dsResult
> DataGrid1.DataMember = "testTable"
> DataGrid1.DataBind()
> OleDbConnection1.Close()
>
> Many thanks in advance.
>
>|||I did that. But now i'm getting another server error:
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value. if available. No work was done."
Any more ideas.
Nab
"Michael C#" wrote:

> I think you need to add a size to your .Add() function, like this:
> oleAdapter.SelectCommand.Parameters.Add("@.BizName", OleDbType.VarChar, 50)
> I used a default of 50, because that's what Access defaults to for those
> type fields, but you should change it to match the size of the field in yo
ur
> database.
> Same thing with the @.BizAddress parameter.
> Thx,
> Mike C.
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:51A57F18-B570-4311-859C-3A0B7357FC6C@.microsoft.com...
>
>|||Which command is it erroring on? Possibly this one?
Why are you trying to get a Parameter Value from an Output parameter when
the Command hasn't been executed yet? I'm not even sure you can use an
OUTPUT parameter with a DataAdapter's .Fill() method. You might want to
look that one up in MSDN Online. To return output parameters, they have to
be specified in your SQL Command by following with the OUTPUT keyword also.
If you'd like to pursue this further, I can help you sort it out, but we
should probably move this thread to the
microsoft.public.dotnet.languages.csharp newsgroup, since we're leaving the
realm of SQL problems and charging into C#.NET + ADO.NET issues.
Thanks,
Mike C.
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:6B22A250-F07C-482B-A7E9-00AED74B8829@.microsoft.com...
>I did that. But now i'm getting another server error:
> "Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value. if available. No work was done."
> Any more ideas.
> Nab
> "Michael C#" wrote:
>|||Thanks Michael. The error is on this line:
oleAdapter.Fill(dsResult, "testTable")
I will appreciate it if you could email to: nabofengland@.hotmail.co.uk
With a suggested solution.
Cheers.
Nab
"Michael C#" wrote:

> Which command is it erroring on? Possibly this one?
>
> Why are you trying to get a Parameter Value from an Output parameter when
> the Command hasn't been executed yet? I'm not even sure you can use an
> OUTPUT parameter with a DataAdapter's .Fill() method. You might want to
> look that one up in MSDN Online. To return output parameters, they have t
o
> be specified in your SQL Command by following with the OUTPUT keyword also
.
> If you'd like to pursue this further, I can help you sort it out, but we
> should probably move this thread to the
> microsoft.public.dotnet.languages.csharp newsgroup, since we're leaving th
e
> realm of SQL problems and charging into C#.NET + ADO.NET issues.
> Thanks,
> Mike C.
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:6B22A250-F07C-482B-A7E9-00AED74B8829@.microsoft.com...
>
>|||Sorry, here are the lines of error with oleAdapter.Fill(dsResult,
"testTable") highlighted in red.
Source Error:
Line 62: i =
oleAdapter.SelectCommand.Parameters("@.BizAddress").Value()
Line 63:
Line 64: oleAdapter.Fill(dsResult, "testTable")
Line 65: DataGrid1.DataSource = dsResult
Line 66: DataGrid1.DataMember = "testTable"
"Michael C#" wrote:

> Which command is it erroring on? Possibly this one?
>
> Why are you trying to get a Parameter Value from an Output parameter when
> the Command hasn't been executed yet? I'm not even sure you can use an
> OUTPUT parameter with a DataAdapter's .Fill() method. You might want to
> look that one up in MSDN Online. To return output parameters, they have t
o
> be specified in your SQL Command by following with the OUTPUT keyword also
.
> If you'd like to pursue this further, I can help you sort it out, but we
> should probably move this thread to the
> microsoft.public.dotnet.languages.csharp newsgroup, since we're leaving th
e
> realm of SQL problems and charging into C#.NET + ADO.NET issues.
> Thanks,
> Mike C.
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:6B22A250-F07C-482B-A7E9-00AED74B8829@.microsoft.com...
>
>|||This should help:
http://support.microsoft.com/kb/308051
-oj
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:58CE1CA5-56D2-492A-A3EA-C15B41AF0D34@.microsoft.com...
> Sorry, here are the lines of error with oleAdapter.Fill(dsResult,
> "testTable") highlighted in red.
> Source Error:
> Line 62: i =
> oleAdapter.SelectCommand.Parameters("@.BizAddress").Value()
> Line 63:
> Line 64: oleAdapter.Fill(dsResult, "testTable")
> Line 65: DataGrid1.DataSource = dsResult
> Line 66: DataGrid1.DataMember = "testTable"
>
> "Michael C#" wrote:
>|||Line 62 looks like it might be an offending instruction, as well as possibly
the manner in which you're trying to use an Output Parameter with your SQL
statement. What's the Output Parameter for anyway? Just wondering... I'm
not sure exactly what you're trying to accomplish with your DataGrid and
OleAdapter here, but basically you could eliminate the Output Parameter
altogether, since the only thing your statement would return to the DataGrid
(without the "@.BizAddress=" part), is the value of Address; presumably a
single "Address" from a single row...
Thanks,
Mike C.
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:58CE1CA5-56D2-492A-A3EA-C15B41AF0D34@.microsoft.com...
> Sorry, here are the lines of error with oleAdapter.Fill(dsResult,
> "testTable") highlighted in red.
> Source Error:
> Line 62: i =
> oleAdapter.SelectCommand.Parameters("@.BizAddress").Value()
> Line 63:
> Line 64: oleAdapter.Fill(dsResult, "testTable")
> Line 65: DataGrid1.DataSource = dsResult
> Line 66: DataGrid1.DataMember = "testTable"
>
> "Michael C#" wrote:
>

parameter problem

Hi, i want to bind dropdowenlist ti gridview so i fill grid with aprameter takes from dropdownlist

i use this code to defien the parameter

SqlParameter pram1 =newSqlParameter("@.Group_ID",SqlDbType.Int, 4,"Group_ID");

pram1.Direction =ParameterDirection.Input;

pram1.Value =Convert.ToInt32(DropDownList5.SelectedItem.Value);

cmd.Parameters.Add(pram1);

what is th problem in value????

i try with many thing but the same result

plz help,,,

Hello my friend,

Use the following line: -

cmd.Parameters.Add("@.Group_ID", SqlDbType.Int, 4).Value = DropDownList5.SelectedItem.Value;

I would be sure to test that the dropdown list has a selected value or your code will crash: -

if (DropDownList5.SelectedIndex >= 0)

Kind regards

Scotty

|||

I agree. Something like:

cmd.Parameters.Add(
"@.Group_ID"
, SqlDbType.Int
).Value = (DropDownList5.SelectedIndex >= 0) ? DropDownList5.SelectedItem.Value : DBNull.Value;

Of course, your sql statement/sproc should consider @.Group_ID could be NULL as well.

|||

Try to use

DropDownList5.SelectedValue instead ofDropDownList5.SelectedItem.Value

Satya

|||

In order to know whether an items was selected (checking SelectedIndex >= 0) or not (setting the parameter to DBNull.Value) then you would be doing extra checks on SelectedIndex (check Reflector for the exact code). A minor point, but something to consider.

Parameter passing from report to SQL

Hi, I have the following SQL code in my DataSet:
(SQL Server 2005)
DECLARE @.sel1 AS varchar(20)
DECLARE @.sel2 AS varchar(20)
DECLARE @.sel3 AS varchar(20)
DECLARE @.sel4 AS varchar(20)
DECLARE @.test AS integer
set @.test = @.TimeDiff; <-- this I want to insert via URL in report
set @.sel1=convert
(char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
set @.sel2=Replace(@.sel1,'-','');
set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
set @.sel4=Replace(@.sel3,'-','');
select @.sel2 AS StartTime, @.sel4 AS EndTime;
I have declared a report parameter (TimeDiff, without @.) in Report
Parameters. It is integer and hidden and has a default value 2 (non queried).
In dataset parameters I do not have any configurations (should I have).
I tried to opass the parameter to my sql code but it is not succeeded.
I got the followng error message:
"Must declare the scalar cariable "@.TimeDiff"
The final meaning is to insert this as parameter via URL and use it in the
dataset sql code, but I can not get it into my SQL .
When running the dataset itself it asks the parameter @.TimeDiff and works OK.
What is wrong in my parameter handling ?Push the ... button (the point button) right beside your dataset
(within the data display), a couple of tabs will popup and goto the
parameters tab.
Within this tab you're able to bind the sql declared parameter to your
reporting parameter.
You'll notice the way it works via your other parameters which are
bound to the sql parameters|||I'd suggest having a stored procedure for this code but as long as you don't
use a temp table (if you use a temp table you must put this in a stored
procedure) then this will work.
I would have expected RS to automatically create a TimeDiff report parameter
to match the @.TimeDiff since @.TimeDiff is not declared. Perhaps it is the
use of the set statement. Try
select @.test = @.TimeDiff
If you click on the ..., parameters tab and there is no @.TimeDiff in the
name column then try adding this by hand and then select your report
parameter that it is mapping to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jarmopy" <jarmopy@.discussions.microsoft.com> wrote in message
news:9123A5B9-0DA9-4639-9884-D9D7A30CAA2C@.microsoft.com...
> Hi, I have the following SQL code in my DataSet:
> (SQL Server 2005)
> DECLARE @.sel1 AS varchar(20)
> DECLARE @.sel2 AS varchar(20)
> DECLARE @.sel3 AS varchar(20)
> DECLARE @.sel4 AS varchar(20)
> DECLARE @.test AS integer
> set @.test = @.TimeDiff; <-- this I want to insert via URL in report
>
> set @.sel1=convert
> (char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
> set @.sel2=Replace(@.sel1,'-','');
> set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
> set @.sel4=Replace(@.sel3,'-','');
> select @.sel2 AS StartTime, @.sel4 AS EndTime;
> I have declared a report parameter (TimeDiff, without @.) in Report
> Parameters. It is integer and hidden and has a default value 2 (non
> queried).
> In dataset parameters I do not have any configurations (should I have).
> I tried to opass the parameter to my sql code but it is not succeeded.
> I got the followng error message:
> "Must declare the scalar cariable "@.TimeDiff"
> The final meaning is to insert this as parameter via URL and use it in the
> dataset sql code, but I can not get it into my SQL .
> When running the dataset itself it asks the parameter @.TimeDiff and works
> OK.
> What is wrong in my parameter handling ?
>
>
>|||Hi, thanks for the answers.
I tried to put the following parameter setting in the dataset (... button)
@.TimeDiff = Parameter!TimeDiff.Value
but now I get the following error message:
The report parameter 'LastDate' has a DefaultValue or a ValidValue that
depends on the report parameter "TimeDiff". Forward dependencies are not
allowed"
The LastDate report parameter is coming from the dataset from field
StartTime and that is calculated from TimeDiff.
LastTime parameter is Hidden and available value is from query and the
default value is from query.
Still something wrong.|||Hi,
I solved the last problem just by changing the order of the parameters in
report side.
Thanks for nicismyname and Bruce L-C|||Hi, you should be aware of the order of your parameters. If lastdate
parameter it's dataset refers to the timediff parameter you should
first prompt the timediff parameter. The prompting order is determined
by the 'report>parameters' menu. You'll notice two arrows at the
dialog popping up after selecting 'report>parameters', use the up and
down arrow to determine the prompting order.

Parameter as Column Name?

Greetings,

I am developing a search form that uses a DDL to select a column name and a text box to define a search term. Code follows:

1cmdSearch = New SqlCommand( "select * from sites where @.columnName like @.sTerm order by site_name", conSites)23cmdSearch.Parameters.Add( "@.columnName", SqlDbType.Text).Value=ColumnParam4cmdSearch.Parameters.Add( "@.sTerm", SqlDbType.Text).Value=searchParam

The problem I'm running into is that the @.variable appears to imply single quotes about the value. This is fine for @.sTerm, I just removed the quotes from the concatenation and it reads the value correctly, but it appears to break @.columnName and the query returns no values.

If I manually substitute an appropriate column for @.columnName sans single quotes, it works.

How can I eliminate the implied single quotes from @.columnName so that the query will read it properly?

You need to build your statement dynamically within your xp and execute it using sp_executesql system stored procedure. (Don't use the column variable in your sql.)

You should create a stored procedure to handle the building and execution of the string to make the process easier and reusable. Use parameters (like you did in your current sql statement, just not for a column or table name) to prevent injection attacks.

http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||

Thanks for the advice, but I can't use stored procedures for this project, or I would've done it that way from the beginning.

Is there any way to define the @.variable to eliminate the implied quotes?

ps2goat:

You need to build your statement dynamically within your xp and execute it using sp_executesql system stored procedure. (Don't use the column variable in your sql.)

You should create a stored procedure to handle the building and execution of the string to make the process easier and reusable. Use parameters (like you did in your current sql statement, just not for a column or table name) to prevent injection attacks.

http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||

You can build the SQL dynamically with the column name, but still keep parameters for everything else. This will still limit sql injections.

A quick example:

cmdSearch =New SqlCommand("select * from sites where " & ColumnParam &" like @.sTerm order by site_name", conSites)' continue with code as normal, but no column parameter.

|||

Thanks, this did the trick. I'd tried doing it this way before but couldn't work out the syntax.

Much obliged.

ps2goat:

You can build the SQL dynamically with the column name, but still keep parameters for everything else. This will still limit sql injections.

A quick example:

cmdSearch =New SqlCommand("select * from sites where " & ColumnParam &" like @.sTerm order by site_name", conSites)' continue with code as normal, but no column parameter.

sql

Paragraph Options

I have solved my code problem but I cannot get it to linefeed
I am using basic syntax. Here is what I have

formula = ""
if {Dealer_Alert_Query.DEALER CONSUMER LETTER} = true then
formula = formula + "Paragraph 1" + "Paragraph 4"
end if
if {Dealer_Alert_Query.Design} = true then
formula = formula + "Paragraph 2" + chr(10) + "Paragraph 5"
end if
if {Dealer_Alert_Query.DlrComplaint} = yes then
formula = formula + "Paragraph 3" + "Paragraph 6"
end if

It will print everything it should up until it encounters the chr(10). I have also tried chr(13) and I have tried & instead of +.

What am I doing wrong?Do you mean that you can only see everything up until the chr(10) or chr(13)?
Maybe your field doesn't have the 'Can Grow' option ticked. Look for it in the format options of the field, I can't remember which tab it's on.

Paging on SqlCeResultSet

Hi

I am using SqlCeResult and i want to give paging on that and read in help that you can do paging on ResultSet so . I need code example so that i can understand how to implement this in resultSet.

Thanks & Regards

Vishal

Hi,

I need to implement paging on sqlceresultset. The resultset contains about 60000 records .I want to implement paging and bind it to the grid. I read that it can be done using sqlceresultset, but did not any example anywhere.

Can someone please suggest how to acoomplish this ?

Thanks

|||

There’s no need to “page” ResultSet. RS is a live cursor which loads data only as needed, not all data at once. If bound to DataGrid it would only load visible rows and loads others only as you scroll doing “paging” for you. So just bind your grid to the RS and you're done with "paging".

Paging in Stored Procedure

I got this code on internet for Stored Proc paging. This will work if my final result is the order of eployee id.
In my case my final result is in the order of emplyee name. What i have to change in below code so i can have paging in SQL plus my order is by name. I dont want to create Temp table. Becuase i think Creating temp table is overhead to sql server
and in that case i'll use Datagrid defualt paging and return all rows always.(My total number of rows will not be more than 300 and in 70% cases they are below 100)

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@.startRowIndex int,
@.maximumRows int
)
AS

DECLARE @.first_id int, @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @.first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

i'm using SQL server 2000|||

If name is unique, then just change it to:

DECLARE @.first_name varchar(100), @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_name = name FROM employees ORDER BY name

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE name >= @.first_name
ORDER BY e.name

SET ROWCOUNT 0

If name is not unique, you might get some overlap, but it won't be a critical issue unless you have a lot of overlap. You could use two columns and include the employeeId for uniqueness, if you wanted to avoid overlapping rows.

|||name will not be Unique|||

This should work:

DECLARE @.first_name varchar(100), @.employeeId int, @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_name = name, @.employeeId = employeeId FROM employees ORDER BY name

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE name > @.first_name
or (name = @.first_name
and employeeId > @.employeeId)
ORDER BY e.name, e.employeeId

SET ROWCOUNT 0

Something like that...

Paging and printing without the toolbar

Is there a way to issue the report print command or paging command from code.
I would like to hide the toolbar and use another button in our ASP.NET
application to kick off printing or tell the reportviewer to go to another
page.
ThanksPerhaps you could use the findstring to search for the literal in a report
ie
http://server/Reportserver?/SampleReports/Product
Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
Search for URL Access in Books on line and you will get all of the URL
parameters, etc... There is also code which shows you how to put and IE
browswer instance in a windows form, and put a report into it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NeedToKnow" wrote:
> Is there a way to issue the report print command or paging command from code.
> I would like to hide the toolbar and use another button in our ASP.NET
> application to kick off printing or tell the reportviewer to go to another
> page.
> Thanks|||I do not understand. I have looked through URL Access and why would I want to
find a literal. I want to send the command to reportviewer through a URL
that tells the report viewer to print the current report to the printer.
"Wayne Snyder" wrote:
> Perhaps you could use the findstring to search for the literal in a report
> ie
> http://server/Reportserver?/SampleReports/Product
> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>
> Search for URL Access in Books on line and you will get all of the URL
> parameters, etc... There is also code which shows you how to put and IE
> browswer instance in a windows form, and put a report into it...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NeedToKnow" wrote:
> > Is there a way to issue the report print command or paging command from code.
> > I would like to hide the toolbar and use another button in our ASP.NET
> > application to kick off printing or tell the reportviewer to go to another
> > page.
> >
> > Thanks|||You asked two question. He answered one of them (the one about going to
another page).
As far as printing, I don't think what you want is possible with the report
viewer control. Are you using the one from VS 2005?
I wanted to do something similar with the winform report viewer control but
found out that until it is rendered I can't request for it to print. So for
Windows I am setting a property to print and then responding to the
rendering event and if their is a request to print, I print and then close
the form. However, I am not familiar with the webform control from VS 2005.
If you are doing this in RS 2000 then I have even less to offer as a
suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
>I do not understand. I have looked through URL Access and why would I want
>to
> find a literal. I want to send the command to reportviewer through a URL
> that tells the report viewer to print the current report to the printer.
> "Wayne Snyder" wrote:
>> Perhaps you could use the findstring to search for the literal in a
>> report
>> ie
>> http://server/Reportserver?/SampleReports/Product
>> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>>
>> Search for URL Access in Books on line and you will get all of the URL
>> parameters, etc... There is also code which shows you how to put and IE
>> browswer instance in a windows form, and put a report into it...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "NeedToKnow" wrote:
>> > Is there a way to issue the report print command or paging command from
>> > code.
>> > I would like to hide the toolbar and use another button in our ASP.NET
>> > application to kick off printing or tell the reportviewer to go to
>> > another
>> > page.
>> >
>> > Thanks|||It is vs 2005.
I don't want to print the form, since my report is several pages long. Is
there a way to do this?
"Bruce L-C [MVP]" wrote:
> You asked two question. He answered one of them (the one about going to
> another page).
> As far as printing, I don't think what you want is possible with the report
> viewer control. Are you using the one from VS 2005?
> I wanted to do something similar with the winform report viewer control but
> found out that until it is rendered I can't request for it to print. So for
> Windows I am setting a property to print and then responding to the
> rendering event and if their is a request to print, I print and then close
> the form. However, I am not familiar with the webform control from VS 2005.
> If you are doing this in RS 2000 then I have even less to offer as a
> suggestion.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
> news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
> >I do not understand. I have looked through URL Access and why would I want
> >to
> > find a literal. I want to send the command to reportviewer through a URL
> > that tells the report viewer to print the current report to the printer.
> >
> > "Wayne Snyder" wrote:
> >
> >> Perhaps you could use the findstring to search for the literal in a
> >> report
> >>
> >> ie
> >> http://server/Reportserver?/SampleReports/Product
> >> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
> >>
> >>
> >> Search for URL Access in Books on line and you will get all of the URL
> >> parameters, etc... There is also code which shows you how to put and IE
> >> browswer instance in a windows form, and put a report into it...
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it''s
> >> community of SQL Professionals.
> >>
> >>
> >> "NeedToKnow" wrote:
> >>
> >> > Is there a way to issue the report print command or paging command from
> >> > code.
> >> > I would like to hide the toolbar and use another button in our ASP.NET
> >> > application to kick off printing or tell the reportviewer to go to
> >> > another
> >> > page.
> >> >
> >> > Thanks
>
>

Paging a "WITH XMLNAMESPACES - FOR XML" query

HI all, I would like to add paging to a query like this:

Code Snippet

WITH XMLNAMESPACES

(

'http://www.w3.org/2001/XMLSchema' as xsd

, 'http://www.w3.org/2001/XMLSchema-instance' as xsi

, 'http://schemas.contoso.com/' as cnt

)

SELECT Field1 as 'cnt:Field1'

, Field2 as 'cnt:Field2'

, Field3 as 'cnt:Field3'

FROM MyTable

FOR XML PATH ('MyTable');

I thought I should do something like:

Code Snippet

WITH T1 AS

(

SELECT Field1

, Field2

, Field3

, ROW_NUMBER() OVER (ORDER BY Field1) AS RowNumber

FROM MyTable

)

WITH XMLNAMESPACES

(

'http://www.w3.org/2001/XMLSchema' as xsd

, 'http://www.w3.org/2001/XMLSchema-instance' as xsi

, 'http://schemas.contoso.com/' as cnt

)

SELECT T1.Field1 as 'cnt:Field1'

, T1.Field2 as 'cnt:Field2'

, T1.Field3 as 'cnt:Field3'

FROM T1

WHERE RowNumber between 50 and 60

FOR XML PATH ('MyTable');

But this doesn't work, seems there's some sintax problem with the 2 "WITH".

Does anyone knows if it's possible to obtain what I want? What is wrong with my code?

Thanks in advance

m.

Do:

Code Snippet

WITH XMLNAMESPACES

(

'http://www.w3.org/2001/XMLSchema' as xsd

, 'http://www.w3.org/2001/XMLSchema-instance' as xsi

, 'http://schemas.contoso.com/' as cnt

),

T1 AS

(

SELECT Field1

, Field2

, Field3

, ROW_NUMBER() OVER (ORDER BY Field1) AS RowNumber

FROM MyTable

)

SELECT T1.Field1 as 'cnt:Field1'

, T1.Field2 as 'cnt:Field2'

, T1.Field3 as 'cnt:Field3'

FROM T1

WHERE RowNumber between 50 and 60

FOR XML PATH ('MyTable');

|||

Perfect (and even so quick!).

Many thanks

m.