Real new person question here but during my query practise I created some
parameter queries in the enterprise manager however theses will not work when
I open query analyser. Can anyone tell where I’ve boobed?
Many thanks
Sam
What did you create?
Stored procedures?
Which database - are you in the correct database?
Easiest to create them using query analyser using create proc then you can
save the script.
"Sam" wrote:
> Real new person question here but during my query practise I created some
> parameter queries in the enterprise manager however theses will not work when
> I open query analyser. Can anyone tell where I’ve boobed?
> Many thanks
> Sam
>
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Friday, March 30, 2012
Parameter Queries in Access Projects
Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:
SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]
How can we do this in Access projects for views or stored procedure or user defined functions?
ThanksYou are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.
Regards,
Hugh Scott
Originally posted by Sia
Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:
SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]
How can we do this in Access projects for views or stored procedure or user defined functions?
Thanks|||I know that its not easy but do you know of any sample that I can look at?
Thanks
Originally posted by hmscott
You are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.
Regards,
Hugh Scott|||Consider this:
SQL Stored Procedure:
CREATE PROC spDoThis
@.Parm1 as Varchar(255),
@.Parm2 as Int
AS
UPDATE MyTable
SET Column1 = @.Parm1
WHERE ID = @.Parm2
ADO Code (in your form):
Private Function Update_Click()
Dim oConn, oComm
' Instantiate ActiveX objects
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
' Set connection string
oConn.ConnectionString="Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security=False;" & _
"Catalogue=MyDatabase;" & _
"Server=MyServer"
oConn.Open
' Set the ADO Command to the name of the stored proc
Set oComm.ActiveConnection = oConn
oComm.CommandText = "spDoThis"
oComm.Parameters.Refresh
' Assign the values to the stored proc parameters from your form
oComm.Parameters("Parm1") = [MyForm].[MyText].[Value]
oComm.Parameters("Parm2") = [MyForm].[MyRecordID].[Value]
oComm.Execute
' Clean up ActiveX objects
Set oComm = Nothing
oConn.Close
Set oConn = Nothing
End Function
Notes:
1. This was written from the top of my head in a hurry (ie, don't nag me about syntax).
2. I have not tested this.
3. Your mileage may vary.
Hope this helps.
Regards,
Hugh Scott
Originally posted by Sia
I know that its not easy but do you know of any sample that I can look at?
Thankssql
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:
SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]
How can we do this in Access projects for views or stored procedure or user defined functions?
ThanksYou are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.
Regards,
Hugh Scott
Originally posted by Sia
Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:
SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]
How can we do this in Access projects for views or stored procedure or user defined functions?
Thanks|||I know that its not easy but do you know of any sample that I can look at?
Thanks
Originally posted by hmscott
You are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.
Regards,
Hugh Scott|||Consider this:
SQL Stored Procedure:
CREATE PROC spDoThis
@.Parm1 as Varchar(255),
@.Parm2 as Int
AS
UPDATE MyTable
SET Column1 = @.Parm1
WHERE ID = @.Parm2
ADO Code (in your form):
Private Function Update_Click()
Dim oConn, oComm
' Instantiate ActiveX objects
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
' Set connection string
oConn.ConnectionString="Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security=False;" & _
"Catalogue=MyDatabase;" & _
"Server=MyServer"
oConn.Open
' Set the ADO Command to the name of the stored proc
Set oComm.ActiveConnection = oConn
oComm.CommandText = "spDoThis"
oComm.Parameters.Refresh
' Assign the values to the stored proc parameters from your form
oComm.Parameters("Parm1") = [MyForm].[MyText].[Value]
oComm.Parameters("Parm2") = [MyForm].[MyRecordID].[Value]
oComm.Execute
' Clean up ActiveX objects
Set oComm = Nothing
oConn.Close
Set oConn = Nothing
End Function
Notes:
1. This was written from the top of my head in a hurry (ie, don't nag me about syntax).
2. I have not tested this.
3. Your mileage may vary.
Hope this helps.
Regards,
Hugh Scott
Originally posted by Sia
I know that its not easy but do you know of any sample that I can look at?
Thankssql
Parameter Queries
If I understand what I am doing ...
I can run an unnamed parameter query in Query Analyzer
SELECT au_lname, au_fname
FROM dbo.authors
WHERE (state = ?)
However, that is about all I can do with it. I have not been able to do
anything with named parameter queries.
I guess the Books Online is a little off on these?
Is it possible to create some form of parameter query that accomplishes the
same thing as they do in Access?
Thanks.
Mike.You need a stored proc
--first create the procedure
Create Procedure prAuthorStates @.State char(2)
as
set nocount on
SELECT au_lname, au_fname,state
FROM dbo.authors
WHERE (state = @.State)
set nocount off
GO
--then execute the proc
exec prAuthorStates 'CA'
The code that you potsde can be used from ASP/PHP/JSP etc by using
prepared statements but not from Query Analyzer
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MikeV06 wrote:
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes th
e
> same thing as they do in Access?
> Thanks.
> Mike.|||Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
enter the parameter value (in a dialog box like MS Access does). That
functionality is not built in to SQL Server. You would need to build your
own GUI for that...
Keith Kratochvil
"MikeV06" <me@.privacy.net> wrote in message
news:189zasiw6e1fr$.dlg@.mycomputer06.invalid.com...
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes
> the
> same thing as they do in Access?
> Thanks.
> Mike.|||On Wed, 31 May 2006 14:04:08 -0500, Keith Kratochvil wrote:
> Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
> enter the parameter value (in a dialog box like MS Access does). That
> functionality is not built in to SQL Server. You would need to build your
> own GUI for that...
Thanks to you both. I thought as much. It is a shame though. The Query
Designer will pop up a dialog box for filling in the values. Works with the
? unnamed one OK; has a problem with the named one.
I guess I will have to find another way.
Thanks again.
I can run an unnamed parameter query in Query Analyzer
SELECT au_lname, au_fname
FROM dbo.authors
WHERE (state = ?)
However, that is about all I can do with it. I have not been able to do
anything with named parameter queries.
I guess the Books Online is a little off on these?
Is it possible to create some form of parameter query that accomplishes the
same thing as they do in Access?
Thanks.
Mike.You need a stored proc
--first create the procedure
Create Procedure prAuthorStates @.State char(2)
as
set nocount on
SELECT au_lname, au_fname,state
FROM dbo.authors
WHERE (state = @.State)
set nocount off
GO
--then execute the proc
exec prAuthorStates 'CA'
The code that you potsde can be used from ASP/PHP/JSP etc by using
prepared statements but not from Query Analyzer
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MikeV06 wrote:
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes th
e
> same thing as they do in Access?
> Thanks.
> Mike.|||Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
enter the parameter value (in a dialog box like MS Access does). That
functionality is not built in to SQL Server. You would need to build your
own GUI for that...
Keith Kratochvil
"MikeV06" <me@.privacy.net> wrote in message
news:189zasiw6e1fr$.dlg@.mycomputer06.invalid.com...
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes
> the
> same thing as they do in Access?
> Thanks.
> Mike.|||On Wed, 31 May 2006 14:04:08 -0500, Keith Kratochvil wrote:
> Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
> enter the parameter value (in a dialog box like MS Access does). That
> functionality is not built in to SQL Server. You would need to build your
> own GUI for that...
Thanks to you both. I thought as much. It is a shame though. The Query
Designer will pop up a dialog box for filling in the values. Works with the
? unnamed one OK; has a problem with the named one.
I guess I will have to find another way.
Thanks again.
Parameter Queries
I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank you
See "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you
|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank you
See "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you
|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you
Parameter Queries
Is there a way in Transact-SQL to do a "runtime" parameter query within a MS
Access adp frontend? This is easily done in a pure MS Access mdb using the
square brackets, e.g. [ENTER Customer Number].Hi
SQL Server will not allow this, but you may want to ask in an access
newsgroup to find the best solution.
John
"Peter Marshall at OCC" wrote:
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
>|||If you call a SQL Server stored proc that takes parameters without passing a
ny MSAccess will contrive to prompt you for values. The
prompt will be the name of the parameter, with the window title of : "Enter
Parameter Value".
Good enough?
If you want to replace the prompt, you'll have to code
"Peter Marshall at OCC" <peter.marshall@.ohiocoatingscompany.com> wrote in message news:ubCn
sarvFHA.596@.TK2MSFTNGP12.phx.gbl...
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
Access adp frontend? This is easily done in a pure MS Access mdb using the
square brackets, e.g. [ENTER Customer Number].Hi
SQL Server will not allow this, but you may want to ask in an access
newsgroup to find the best solution.
John
"Peter Marshall at OCC" wrote:
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
>|||If you call a SQL Server stored proc that takes parameters without passing a
ny MSAccess will contrive to prompt you for values. The
prompt will be the name of the parameter, with the window title of : "Enter
Parameter Value".
Good enough?
If you want to replace the prompt, you'll have to code
"Peter Marshall at OCC" <peter.marshall@.ohiocoatingscompany.com> wrote in message news:ubCn
sarvFHA.596@.TK2MSFTNGP12.phx.gbl...
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
Parameter Queries
I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you
Parameter Queries
I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I ha
d
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how t
o
> do it in Query Designer. Is there a way to save this query with the set u
p
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank yousql
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I ha
d
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how t
o
> do it in Query Designer. Is there a way to save this query with the set u
p
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank yousql
parameter properties in report manager - "Has Default"
ok I've designed a report template that uses cascading paramters based
on several sp's. The user selects a value for the first parameter and
that value is passed to the sp that populates the second parameter
list. The value selected for the second param is used to populate the
third param list etc...
I want the first parameter to be required and the other 3 to be
optional - the report should still run if they are not selected. RS
BOL told me to go into the Report manager, select "Properties"
then"Parameters" and then select the "Has Default" check box for the
three optional paramters. This in turn enables the "Null" checkbox, so
I checked that box so null will be the default value passed if these
parameters are not selected.
By all accounts this should allow the report to run with only the first
parameter selected, but when run from Report Manager it still keeps
prompting the user to enter values for the 3 other optional parameters.
Any ideas? help?Never mind! found a workaround...
on several sp's. The user selects a value for the first parameter and
that value is passed to the sp that populates the second parameter
list. The value selected for the second param is used to populate the
third param list etc...
I want the first parameter to be required and the other 3 to be
optional - the report should still run if they are not selected. RS
BOL told me to go into the Report manager, select "Properties"
then"Parameters" and then select the "Has Default" check box for the
three optional paramters. This in turn enables the "Null" checkbox, so
I checked that box so null will be the default value passed if these
parameters are not selected.
By all accounts this should allow the report to run with only the first
parameter selected, but when run from Report Manager it still keeps
prompting the user to enter values for the 3 other optional parameters.
Any ideas? help?Never mind! found a workaround...
Parameter Prompts
I don't see a way to attach an expression to a parameter prompt. I have a
custom report extension I have written that works great. I make a call to it
and it retrieves label values to report items. I would like to use that to
retrieve the prompt value for my parameters. Basically everything on the
reports are dynamic including labels because its a hosted application that
has mutliple clients and each can customise their view of the world. Is this
possible? If not is my only alternative to create by own pages to get the
parameters and pass them into the report thru URL or web service?Expressions are not supported for parameter prompt yet.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alex Telford" <atelford6@.nospam.hotmail.com> wrote in message
news:OKqtoLcfEHA.708@.TK2MSFTNGP09.phx.gbl...
>I don't see a way to attach an expression to a parameter prompt. I have a
> custom report extension I have written that works great. I make a call to
> it
> and it retrieves label values to report items. I would like to use that to
> retrieve the prompt value for my parameters. Basically everything on the
> reports are dynamic including labels because its a hosted application that
> has mutliple clients and each can customise their view of the world. Is
> this
> possible? If not is my only alternative to create by own pages to get the
> parameters and pass them into the report thru URL or web service?
>|||Alex,
I have a urgent project requirement to code for rdl reports to create
the content of the report dynamically. I just noticed at the forum
that you did the same. Can you please help with ideas/code.
basically I wish to send commandtext (of a dataset) to the rdl report
at runtime.
thanks,
anand sagar
"Alex Telford" <atelford6@.nospam.hotmail.com> wrote in message news:<OKqtoLcfEHA.708@.TK2MSFTNGP09.phx.gbl>...
> I don't see a way to attach an expression to a parameter prompt. I have a
> custom report extension I have written that works great. I make a call to it
********
custom report extension I have written that works great. I make a call to it
and it retrieves label values to report items. I would like to use that to
retrieve the prompt value for my parameters. Basically everything on the
reports are dynamic including labels because its a hosted application that
has mutliple clients and each can customise their view of the world. Is this
possible? If not is my only alternative to create by own pages to get the
parameters and pass them into the report thru URL or web service?Expressions are not supported for parameter prompt yet.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alex Telford" <atelford6@.nospam.hotmail.com> wrote in message
news:OKqtoLcfEHA.708@.TK2MSFTNGP09.phx.gbl...
>I don't see a way to attach an expression to a parameter prompt. I have a
> custom report extension I have written that works great. I make a call to
> it
> and it retrieves label values to report items. I would like to use that to
> retrieve the prompt value for my parameters. Basically everything on the
> reports are dynamic including labels because its a hosted application that
> has mutliple clients and each can customise their view of the world. Is
> this
> possible? If not is my only alternative to create by own pages to get the
> parameters and pass them into the report thru URL or web service?
>|||Alex,
I have a urgent project requirement to code for rdl reports to create
the content of the report dynamically. I just noticed at the forum
that you did the same. Can you please help with ideas/code.
basically I wish to send commandtext (of a dataset) to the rdl report
at runtime.
thanks,
anand sagar
"Alex Telford" <atelford6@.nospam.hotmail.com> wrote in message news:<OKqtoLcfEHA.708@.TK2MSFTNGP09.phx.gbl>...
> I don't see a way to attach an expression to a parameter prompt. I have a
> custom report extension I have written that works great. I make a call to it
********
Parameter Prompt with Wildcard
I'd like to create a report parameter to allow the user to enter only a
partial field using wildcard(s) in SRS. The list is lengthy and the user
does not always know the full name to enter.
Is this possible?
Thanks,
KarenVery much it can be done. Just you need to keep in mind about the single
quotes you are using for string paramters. eg '%userrequest' which you will
be passing the parameters to the "where" clause.
Amarnath
"Moving rpts from Access to Rptg Services" wrote:
> I'd like to create a report parameter to allow the user to enter only a
> partial field using wildcard(s) in SRS. The list is lengthy and the user
> does not always know the full name to enter.
> Is this possible?
> Thanks,
> Karen|||Thank you for our reply! in the where clause I currently have this:
WHERE (dbo.UP_Agents.AgentName = @.AgentName)
is this where I would add the wildcard? Or would I add in a separate
string? (sorry, i'm learning this as I go)
Thank you,
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen|||AHA, this worked:
WHERE (dbo.UP_Agents.AgentName LIKE '%' + @.AgentName + '%')
Thanks for getting me started.
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen
partial field using wildcard(s) in SRS. The list is lengthy and the user
does not always know the full name to enter.
Is this possible?
Thanks,
KarenVery much it can be done. Just you need to keep in mind about the single
quotes you are using for string paramters. eg '%userrequest' which you will
be passing the parameters to the "where" clause.
Amarnath
"Moving rpts from Access to Rptg Services" wrote:
> I'd like to create a report parameter to allow the user to enter only a
> partial field using wildcard(s) in SRS. The list is lengthy and the user
> does not always know the full name to enter.
> Is this possible?
> Thanks,
> Karen|||Thank you for our reply! in the where clause I currently have this:
WHERE (dbo.UP_Agents.AgentName = @.AgentName)
is this where I would add the wildcard? Or would I add in a separate
string? (sorry, i'm learning this as I go)
Thank you,
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen|||AHA, this worked:
WHERE (dbo.UP_Agents.AgentName LIKE '%' + @.AgentName + '%')
Thanks for getting me started.
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen
Parameter prompt value
is there a way to get parameter prompt value inside the report?
If i have parameter 1 with a prompt value "Customer ID", how can i get
"Customer ID" string value?Not really documented anywhere that I have seen but this is how to do it.
Parameters!ParamName.Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1141676084.764341.275170@.e56g2000cwe.googlegroups.com...
> is there a way to get parameter prompt value inside the report?
> If i have parameter 1 with a prompt value "Customer ID", how can i get
> "Customer ID" string value?
>
If i have parameter 1 with a prompt value "Customer ID", how can i get
"Customer ID" string value?Not really documented anywhere that I have seen but this is how to do it.
Parameters!ParamName.Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1141676084.764341.275170@.e56g2000cwe.googlegroups.com...
> is there a way to get parameter prompt value inside the report?
> If i have parameter 1 with a prompt value "Customer ID", how can i get
> "Customer ID" string value?
>
Parameter Prompt text (filter labels)
Is there a way to manipulate the label that appears next to a filter list
(the report parameter prompt) on the fly when generating a report?
For example, I have 2 people viewing a report -- one is in France and one is
in the US. In the report, the filter labels are currently all in English. I
have the French and English text stored in a database table -- so I was
thinking that I could use an expression to get the appropriate text from the
database, but I do not see an opportunity to use an expression for the prompt.
Any other ideas on how I could modify the text in a filter label on the fly?
Thank you,
--
LaurieTOn Mar 21, 11:05 am, LaurieT <Laur...@.discussions.microsoft.com>
wrote:
> Is there a way to manipulate the label that appears next to a filter list
> (the report parameter prompt) on the fly when generating a report?
> For example, I have 2 people viewing a report -- one is in France and one is
> in the US. In the report, the filter labels are currently all in English. I
> have the French and English text stored in a database table -- so I was
> thinking that I could use an expression to get the appropriate text from the
> database, but I do not see an opportunity to use an expression for the prompt.
> Any other ideas on how I could modify the text in a filter label on the fly?
> Thank you,
> --
> LaurieT
The only thing I can think of is to try to conditionally modify the
underlying XML in the RDL file itself (though this is a long shot).
Ofcourse, there's always the custom application route that access the
rdl files internally (or maybe custom assembly: though I'm not
experienced in this area). Sorry to not be of greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I guess that I have the same issue with Report Column and Row labels. Here I
see that I can enter en expression, but when I try to put a query into the
expression, it seems to fail. Any ideas on this?
--
LaurieT
"EMartinez" wrote:
> On Mar 21, 11:05 am, LaurieT <Laur...@.discussions.microsoft.com>
> wrote:
> > Is there a way to manipulate the label that appears next to a filter list
> > (the report parameter prompt) on the fly when generating a report?
> >
> > For example, I have 2 people viewing a report -- one is in France and one is
> > in the US. In the report, the filter labels are currently all in English. I
> > have the French and English text stored in a database table -- so I was
> > thinking that I could use an expression to get the appropriate text from the
> > database, but I do not see an opportunity to use an expression for the prompt.
> >
> > Any other ideas on how I could modify the text in a filter label on the fly?
> >
> > Thank you,
> > --
> > LaurieT
> The only thing I can think of is to try to conditionally modify the
> underlying XML in the RDL file itself (though this is a long shot).
> Ofcourse, there's always the custom application route that access the
> rdl files internally (or maybe custom assembly: though I'm not
> experienced in this area). Sorry to not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql
(the report parameter prompt) on the fly when generating a report?
For example, I have 2 people viewing a report -- one is in France and one is
in the US. In the report, the filter labels are currently all in English. I
have the French and English text stored in a database table -- so I was
thinking that I could use an expression to get the appropriate text from the
database, but I do not see an opportunity to use an expression for the prompt.
Any other ideas on how I could modify the text in a filter label on the fly?
Thank you,
--
LaurieTOn Mar 21, 11:05 am, LaurieT <Laur...@.discussions.microsoft.com>
wrote:
> Is there a way to manipulate the label that appears next to a filter list
> (the report parameter prompt) on the fly when generating a report?
> For example, I have 2 people viewing a report -- one is in France and one is
> in the US. In the report, the filter labels are currently all in English. I
> have the French and English text stored in a database table -- so I was
> thinking that I could use an expression to get the appropriate text from the
> database, but I do not see an opportunity to use an expression for the prompt.
> Any other ideas on how I could modify the text in a filter label on the fly?
> Thank you,
> --
> LaurieT
The only thing I can think of is to try to conditionally modify the
underlying XML in the RDL file itself (though this is a long shot).
Ofcourse, there's always the custom application route that access the
rdl files internally (or maybe custom assembly: though I'm not
experienced in this area). Sorry to not be of greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I guess that I have the same issue with Report Column and Row labels. Here I
see that I can enter en expression, but when I try to put a query into the
expression, it seems to fail. Any ideas on this?
--
LaurieT
"EMartinez" wrote:
> On Mar 21, 11:05 am, LaurieT <Laur...@.discussions.microsoft.com>
> wrote:
> > Is there a way to manipulate the label that appears next to a filter list
> > (the report parameter prompt) on the fly when generating a report?
> >
> > For example, I have 2 people viewing a report -- one is in France and one is
> > in the US. In the report, the filter labels are currently all in English. I
> > have the French and English text stored in a database table -- so I was
> > thinking that I could use an expression to get the appropriate text from the
> > database, but I do not see an opportunity to use an expression for the prompt.
> >
> > Any other ideas on how I could modify the text in a filter label on the fly?
> >
> > Thank you,
> > --
> > LaurieT
> The only thing I can think of is to try to conditionally modify the
> underlying XML in the RDL file itself (though this is a long shot).
> Ofcourse, there's always the custom application route that access the
> rdl files internally (or maybe custom assembly: though I'm not
> experienced in this area). Sorry to not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql
Parameter Prompt RTL
How i can display parameter prompt in other language like arabic
(right to left)
Regard
LPW
Plz
some body reply i need to display parameter prompt in other language
Parameter Prompt List & Filter selection
I have created a parameter for a report that displays a drop-down list of
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper
Parameter prompt based on expression
Hi,
Is it possible to construct the prompt (i.e. the text that is displayed next to the parameter text box when the report runs) for a parameter from an expression
Actually i have written some custom code and i want my parameter prompts to be populated based on the returned values from that custom code. I don't want these do be fixed texts.
Thanks,
Simranjeev
Can you use SWITCH option?
Igor.
|||But the Switch option is also used inside an expression, and i don't know how to create the parameter labels from expressions.
On my report, I have a parameter called "StartDate". I've given it the label "Start Date:". Now i want that when the locale of the user is English, this label should be displayed as "Start Date:" and when the locale of the user changes to some other language, lets say German, then the label should be displayed as the translated value for "Start Date:" in German.
I've already done this for the textboxes on my reports, by calling custom code which reads from resource files depending on user locale. I wanted to know if this is possible for parameter labels as well, as i couldn't find any option to do so.
Parameter prompt
Is there any way to localized parameter prompt?
Thanks,
EricThe Prompt is not localizable.
--
| Thread-Topic: Parameter prompt
| thread-index: AcT0207o98XxeJ+DQ+W2xqXZFRj5lQ==| X-WBNR-Posting-Host: 67.71.201.187
| From: "=?Utf-8?B?QWl3YQ==?=" <Aiwa@.discussions.microsoft.com>
| Subject: Parameter prompt
| Date: Fri, 7 Jan 2005 09:07:03 -0800
| Lines: 4
| Message-ID: <E3106DF7-D3E3-485A-9C25-ED8F8A0C6D2D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38976
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is there any way to localized parameter prompt?
|
| Thanks,
| Eric
|
Thanks,
EricThe Prompt is not localizable.
--
| Thread-Topic: Parameter prompt
| thread-index: AcT0207o98XxeJ+DQ+W2xqXZFRj5lQ==| X-WBNR-Posting-Host: 67.71.201.187
| From: "=?Utf-8?B?QWl3YQ==?=" <Aiwa@.discussions.microsoft.com>
| Subject: Parameter prompt
| Date: Fri, 7 Jan 2005 09:07:03 -0800
| Lines: 4
| Message-ID: <E3106DF7-D3E3-485A-9C25-ED8F8A0C6D2D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38976
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is there any way to localized parameter prompt?
|
| Thanks,
| Eric
|
Parameter Problems ! ! !
Hi,
I have gone through all the suggestions on this site, but non of them have
solved my problem. I am new to reporting services, and I am facing a problem
and I dont' know whether the url based reporting is going to solve this for
me (i really hope it can).
I have a datagrid with link button which basically passes the parameters to
a report like id, startDate and and an endDate, so basically the report shows
data for that id between the startdate and the endDate. Now the parameters
are passed fine and it shows up on the text boxes in the report, however the
problem is that though I want to use the id as a parameter from the url, I do
not want to give the user a text box to change that value i.e. basically i
want it hidden and at the same time I want it to be able to use the value i
pass in the url... can this be done.. I would really appreciate if someone
could direct me on the right path...I have tried hiding prompts throught the
reporting manager, and using just a "space" in the reporting string, but
there's still text box showing up which I don't want to show... sooo plzzz
some one help me out. Thank you.. in advanceYes. It can be done.
Here is an example Url.
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
Replace the values that in brackets that match your requirements.
<ReportDirectory> - use if you've created a folder where your report
resides.
<ReportName> - the name of the report that you want to render.
<Parameter> - the parameter name that you are going to pass a value to the
report.
<ParameterValue> - this is the actual parameter value being passed.
If you have additional parameters you can add them to the end of the Url
using this syntax:
&<ParameterName2>=<ParameterValue2>
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
I hope this helps. I use it all the time in my c# applications.
Yosh
"Safder" <Safder@.discussions.microsoft.com> wrote in message
news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a
> problem
> and I dont' know whether the url based reporting is going to solve this
> for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters
> to
> a report like id, startDate and and an endDate, so basically the report
> shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however
> the
> problem is that though I want to use the id as a parameter from the url, I
> do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value
> i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught
> the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo
> plzzz
> some one help me out. Thank you.. in advance|||Hi,
Thanks for the suggestion, unfortunately this did not work, is there any
pre-reqs to doing this .. i mean do i have to change any properties of the
report before doing this.? if not.. i tried using this same url you have
suggested, but all the text boxes still appear. Also i'de like to specify
that i have 3 parameters out of which i want to show only two. The three
parameters once again are id, startDate and endDate. So I'de like to show the
start and end dates and would like to give the user the option to change
those parameters, however i do not want to give the user the option to change
the id, and hence i want the "id" parameter to be hidden. Please let me know
if i can do this. Thank you.
"Yosh" wrote:
> Yes. It can be done.
> Here is an example Url.
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
> Replace the values that in brackets that match your requirements.
> <ReportDirectory> - use if you've created a folder where your report
> resides.
> <ReportName> - the name of the report that you want to render.
> <Parameter> - the parameter name that you are going to pass a value to the
> report.
> <ParameterValue> - this is the actual parameter value being passed.
> If you have additional parameters you can add them to the end of the Url
> using this syntax:
> &<ParameterName2>=<ParameterValue2>
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
> I hope this helps. I use it all the time in my c# applications.
> Yosh
>
> "Safder" <Safder@.discussions.microsoft.com> wrote in message
> news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a
> > problem
> > and I dont' know whether the url based reporting is going to solve this
> > for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters
> > to
> > a report like id, startDate and and an endDate, so basically the report
> > shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however
> > the
> > problem is that though I want to use the id as a parameter from the url, I
> > do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value
> > i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught
> > the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo
> > plzzz
> > some one help me out. Thank you.. in advance
>
>|||In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
parameter a label and it should be hidden from the user.
--
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.
"Safder" wrote:
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a problem
> and I dont' know whether the url based reporting is going to solve this for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters to
> a report like id, startDate and and an endDate, so basically the report shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however the
> problem is that though I want to use the id as a parameter from the url, I do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo plzzz
> some one help me out. Thank you.. in advance|||If I'm understanding you, you want to have an ID parameter filled in
programmatically but not available to the user to change. I have the same
requirement. All I do is set the parameter to hidden under Report : Report
Parameters and check the Hidden checkbox.|||That is for RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bob Fisher" <bob.fisher@.mben.com> wrote in message
news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> If I'm understanding you, you want to have an ID parameter filled in
> programmatically but not available to the user to change. I have the same
> requirement. All I do is set the parameter to hidden under Report :
> Report Parameters and check the Hidden checkbox.
>|||Hi,
Thank you for the suggestion, I tried what you told me to do in SQL Server
2000 i.e. from "Report manager" i checked the "Prompt user" but i didnt give
any "prompt string" , but what happens in that case is only a text box
without a label shows up,and that does not help I am lookin for something
which does not show a label or a text box. Please let me konw if it's
possible. Bob fisher actually summed up pretty well about what i want,
however his suggestion was for 2005, i am lookin for something which would
work for 2000. Thank you
"Wayne Snyder" wrote:
> In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
> parameter a label and it should be hidden from the user.
> --
> 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.
>
> "Safder" wrote:
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a problem
> > and I dont' know whether the url based reporting is going to solve this for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters to
> > a report like id, startDate and and an endDate, so basically the report shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however the
> > problem is that though I want to use the id as a parameter from the url, I do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo plzzz
> > some one help me out. Thank you.. in advance|||how would you do it in Reporting services 2000
"Bruce L-C [MVP]" wrote:
> That is for RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > If I'm understanding you, you want to have an ID parameter filled in
> > programmatically but not available to the user to change. I have the same
> > requirement. All I do is set the parameter to hidden under Report :
> > Report Parameters and check the Hidden checkbox.
> >
> >
>
>|||Hi Safder
if you want your paramaeter to be hidden from user. After deployment to
Portal, you will have to go to report manager and go to you report properties
and go to parameters section and uncheck the prompt user check box
"Safder" wrote:
> how would you do it in Reporting services 2000
> "Bruce L-C [MVP]" wrote:
> > That is for RS 2005.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> > news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > > If I'm understanding you, you want to have an ID parameter filled in
> > > programmatically but not available to the user to change. I have the same
> > > requirement. All I do is set the parameter to hidden under Report :
> > > Report Parameters and check the Hidden checkbox.
> > >
> > >
> >
> >
> >sql
I have gone through all the suggestions on this site, but non of them have
solved my problem. I am new to reporting services, and I am facing a problem
and I dont' know whether the url based reporting is going to solve this for
me (i really hope it can).
I have a datagrid with link button which basically passes the parameters to
a report like id, startDate and and an endDate, so basically the report shows
data for that id between the startdate and the endDate. Now the parameters
are passed fine and it shows up on the text boxes in the report, however the
problem is that though I want to use the id as a parameter from the url, I do
not want to give the user a text box to change that value i.e. basically i
want it hidden and at the same time I want it to be able to use the value i
pass in the url... can this be done.. I would really appreciate if someone
could direct me on the right path...I have tried hiding prompts throught the
reporting manager, and using just a "space" in the reporting string, but
there's still text box showing up which I don't want to show... sooo plzzz
some one help me out. Thank you.. in advanceYes. It can be done.
Here is an example Url.
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
Replace the values that in brackets that match your requirements.
<ReportDirectory> - use if you've created a folder where your report
resides.
<ReportName> - the name of the report that you want to render.
<Parameter> - the parameter name that you are going to pass a value to the
report.
<ParameterValue> - this is the actual parameter value being passed.
If you have additional parameters you can add them to the end of the Url
using this syntax:
&<ParameterName2>=<ParameterValue2>
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
I hope this helps. I use it all the time in my c# applications.
Yosh
"Safder" <Safder@.discussions.microsoft.com> wrote in message
news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a
> problem
> and I dont' know whether the url based reporting is going to solve this
> for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters
> to
> a report like id, startDate and and an endDate, so basically the report
> shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however
> the
> problem is that though I want to use the id as a parameter from the url, I
> do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value
> i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught
> the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo
> plzzz
> some one help me out. Thank you.. in advance|||Hi,
Thanks for the suggestion, unfortunately this did not work, is there any
pre-reqs to doing this .. i mean do i have to change any properties of the
report before doing this.? if not.. i tried using this same url you have
suggested, but all the text boxes still appear. Also i'de like to specify
that i have 3 parameters out of which i want to show only two. The three
parameters once again are id, startDate and endDate. So I'de like to show the
start and end dates and would like to give the user the option to change
those parameters, however i do not want to give the user the option to change
the id, and hence i want the "id" parameter to be hidden. Please let me know
if i can do this. Thank you.
"Yosh" wrote:
> Yes. It can be done.
> Here is an example Url.
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
> Replace the values that in brackets that match your requirements.
> <ReportDirectory> - use if you've created a folder where your report
> resides.
> <ReportName> - the name of the report that you want to render.
> <Parameter> - the parameter name that you are going to pass a value to the
> report.
> <ParameterValue> - this is the actual parameter value being passed.
> If you have additional parameters you can add them to the end of the Url
> using this syntax:
> &<ParameterName2>=<ParameterValue2>
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
> I hope this helps. I use it all the time in my c# applications.
> Yosh
>
> "Safder" <Safder@.discussions.microsoft.com> wrote in message
> news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a
> > problem
> > and I dont' know whether the url based reporting is going to solve this
> > for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters
> > to
> > a report like id, startDate and and an endDate, so basically the report
> > shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however
> > the
> > problem is that though I want to use the id as a parameter from the url, I
> > do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value
> > i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught
> > the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo
> > plzzz
> > some one help me out. Thank you.. in advance
>
>|||In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
parameter a label and it should be hidden from the user.
--
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.
"Safder" wrote:
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a problem
> and I dont' know whether the url based reporting is going to solve this for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters to
> a report like id, startDate and and an endDate, so basically the report shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however the
> problem is that though I want to use the id as a parameter from the url, I do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo plzzz
> some one help me out. Thank you.. in advance|||If I'm understanding you, you want to have an ID parameter filled in
programmatically but not available to the user to change. I have the same
requirement. All I do is set the parameter to hidden under Report : Report
Parameters and check the Hidden checkbox.|||That is for RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bob Fisher" <bob.fisher@.mben.com> wrote in message
news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> If I'm understanding you, you want to have an ID parameter filled in
> programmatically but not available to the user to change. I have the same
> requirement. All I do is set the parameter to hidden under Report :
> Report Parameters and check the Hidden checkbox.
>|||Hi,
Thank you for the suggestion, I tried what you told me to do in SQL Server
2000 i.e. from "Report manager" i checked the "Prompt user" but i didnt give
any "prompt string" , but what happens in that case is only a text box
without a label shows up,and that does not help I am lookin for something
which does not show a label or a text box. Please let me konw if it's
possible. Bob fisher actually summed up pretty well about what i want,
however his suggestion was for 2005, i am lookin for something which would
work for 2000. Thank you
"Wayne Snyder" wrote:
> In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
> parameter a label and it should be hidden from the user.
> --
> 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.
>
> "Safder" wrote:
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a problem
> > and I dont' know whether the url based reporting is going to solve this for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters to
> > a report like id, startDate and and an endDate, so basically the report shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however the
> > problem is that though I want to use the id as a parameter from the url, I do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo plzzz
> > some one help me out. Thank you.. in advance|||how would you do it in Reporting services 2000
"Bruce L-C [MVP]" wrote:
> That is for RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > If I'm understanding you, you want to have an ID parameter filled in
> > programmatically but not available to the user to change. I have the same
> > requirement. All I do is set the parameter to hidden under Report :
> > Report Parameters and check the Hidden checkbox.
> >
> >
>
>|||Hi Safder
if you want your paramaeter to be hidden from user. After deployment to
Portal, you will have to go to report manager and go to you report properties
and go to parameters section and uncheck the prompt user check box
"Safder" wrote:
> how would you do it in Reporting services 2000
> "Bruce L-C [MVP]" wrote:
> > That is for RS 2005.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> > news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > > If I'm understanding you, you want to have an ID parameter filled in
> > > programmatically but not available to the user to change. I have the same
> > > requirement. All I do is set the parameter to hidden under Report :
> > > Report Parameters and check the Hidden checkbox.
> > >
> > >
> >
> >
> >sql
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:
>
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 with report
I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name
The data source for this report is an oracle db
SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND
(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")
GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,
sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||
Can you mark the helpful response as an answer?
Parameter problem with OLAP and spaces
Hello all,
I have an issue with parameters. Let me explain.....
I have report1 which uses analysis services as it's data source. The report displays a list of our companies sites along with some costs etc. Site names can include spaces and numbers but they are stored in the relational database as varchar[50]. On the relevant text box of report1 I have defined a data detsination using report2 and passing it the site name field.value.
On report2 I have added the site name field as a filter dimension and set it as a parameter. On the report parameters I have set the parameter as being non queried.
When I run report1 and click a value to "drill down" it only works for values that have no spaces and no numbers. If If convert spaces to underscores I get a little further, but sites that have numbers, for example 15_OxfordStreet still do not work. The error message I get is..
"Parser: The syntax for '_Oxford' is incorrect.
Can anyone suggest anything to help?
Many thanks
Matt
You may need to escape the member names. To avoid similar issues, the SSAS team introduced undocumented function UrlEscapeFragment. If you open the sample AW cube, flip to the Actions tab in the Cube Designer, select the Sales Reason Comparision action, and expand the Parameters section, you will see how this function is used to escape the member name.|||Teo,
Thanks for your reply. I'm still learning SQL 2005 and although I tried your suggestion I could not get it to work. However I did fix the problem. The issue was that I was passing through the "friendly name" of the value, instead of [data1].[field name].&[0] etc. as the jump to parameter!
Arrrrgggg...so simple when you think about it!
M
Subscribe to:
Posts (Atom)