Partitions and FileGroups: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

Passing parameter in MDX query

Hi,
I am facing problem during passing the parameters in MDX query. I need tp
pass two parameter in my dataset, but not sure whether I am writing correct
query or not. as I am getting error during parsing the query.
here the query
SELECT {
[Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
} on columns,
NON EMPTY NonEmptyCrossJoin(
[Component Parts].[Part].Members,
NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
Code].Members)
)
on rows FROM [CUBE NAME]
WHERE (" + Parameters!ManufactureDate.Value + ", " +
Parameters!Suppliername.Value + ")
--
The two parameter I have declared in parameter box with multiselect value
option.
while executiing the query I am gettiign belwo message.
"The tuple function expects a tuplet set of expression for the argument.A
string or numeric was used"
Please help me to write the correct syntax for this mdx.
Appreciate your help
Regards
SomuMosha Pasumansky has written an article called "Writing multiselect friendly
MDX calculations":
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
Maybe you'll find your answer there. :)
Have you tried your query without parameters in an MDX parser? If not, try
it and you might figure out how the query should be before trying with the
parameters.
Kaisa M. Lindahl
"Somu" <Somu@.discussions.microsoft.com> wrote in message
news:65941D3D-8CBB-494C-9E95-3F4FD983E31F@.microsoft.com...
> Hi,
> I am facing problem during passing the parameters in MDX query. I need tp
> pass two parameter in my dataset, but not sure whether I am writing
> correct
> query or not. as I am getting error during parsing the query.
> here the query
> SELECT {
> [Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
> } on columns,
> NON EMPTY NonEmptyCrossJoin(
> [Component Parts].[Part].Members,
> NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
> Code].Members)
> )
> on rows FROM [CUBE NAME]
> WHERE (" + Parameters!ManufactureDate.Value + ", " +
> Parameters!Suppliername.Value + ")
> --
> The two parameter I have declared in parameter box with multiselect value
> option.
> while executiing the query I am gettiign belwo message.
> "The tuple function expects a tuplet set of expression for the argument.A
> string or numeric was used"
> Please help me to write the correct syntax for this mdx.
> Appreciate your help
> Regards
> Somu

Passing output parameter from procedure to variable

I have a stored procedure that runs a query and the result of the query is a
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:

> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks

Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

Passing Object Variable as input parameter to an Execute SQL Task Query

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

Please give me solutions for the above two..

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||

Darren,

Thanks for your solution,

Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.

When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.

Moreover I want the solution for the step 2 as well|||

DarrenSQLIS wrote:
@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx

@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)

Just cast it to an integer and all will be well.

select cast(@.@.IDENTITY as int) as 'Ident'|||

Thanks Phil,

It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.

There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

|||

Subhash wrote:

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

That works for me, using OLE DB connection though. What type of connection are you using?

|||

Hi Rafael,

I am using OLEDB connection.

When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.

Wednesday, March 28, 2012

Passing multi-valued parameter to a Sp

Hello,

I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.

Any help appereciated.

Thanks

You can use multivalue input params from RS to a stored proc. The input param comes in like a single string like @.Input = 'Val1,Val2,Val3' and then you can split these with a table function which returns 'Val1',Val2','Val3' . Then, use an IN stmt in your where statement in the stored proc ie WHERE Val IN (SELECT Item FROM dbo.SplitParams ('''+ @.Input + ''', '',''))'. See this thread for more info.

sql

Passing multi-valued parameter to a Sp

Hello,

Any help appereciated.

Thanks

passing multiple values to a stored procedure

hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
--
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
> > hi
> >
> > my query in the stored proc is like ::
> >
> > select * from tableA where name like @.name
> > where @. name is varchar type
> >
> > but the input i get from a list box(where multiple values can be
selected)
> >
> > i think i have to write the query like
> > selct * from tableA where name like @.name1 or name like @.name2 or name
like
> > @.name3...
> >
> > but the problem is that i have no idea before hand to tell how many
values
> > will be sent as params...
> >
> > can some one help me
> > '
> >
> > thanks
> > red
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> > meant LIKE '%'+@.name+'%'
> >
> > Anyway, if you are using = it will be much more efficient than LIKE if you
> > have an index on name. And you can pass in a (limited) comma-separated
> list
> > and translate it to a table, and join on that table. See
> > http://www.aspfaq.com/2248 for an example.
> >
> > A
> >
> >
> > On 3/3/05 8:25 PM, in article
> > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > <red60man@.discussions.microsoft.com> wrote:
> >
> > > hi
> > >
> > > my query in the stored proc is like ::
> > >
> > > select * from tableA where name like @.name
> > > where @. name is varchar type
> > >
> > > but the input i get from a list box(where multiple values can be
> selected)
> > >
> > > i think i have to write the query like
> > > selct * from tableA where name like @.name1 or name like @.name2 or name
> like
> > > @.name3...
> > >
> > > but the problem is that i have no idea before hand to tell how many
> values
> > > will be sent as params...
> > >
> > > can some one help me
> > > '
> > >
> > > thanks
> > > red
> >
>
>|||Did you see my post? WHY ARE YOU YELLING?
--
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > selct * from tableA where name IN (@.name1, @.name2,
@.name3,........@.nameN)
> thanks
> red
> "Simon Worth" wrote:
> > You could try passing the selected text fields back as comma seporated
> > values, and then you can do an IN statement
> > something like
> > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> >
> > That assumes that you really didn't want to use the like statement as in
> > your example you didn't provide any type of wildcard character
> >
> > --
> > Simon Worth
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
you
> > > meant LIKE '%'+@.name+'%'
> > >
> > > Anyway, if you are using = it will be much more efficient than LIKE if
you
> > > have an index on name. And you can pass in a (limited)
comma-separated
> > list
> > > and translate it to a table, and join on that table. See
> > > http://www.aspfaq.com/2248 for an example.
> > >
> > > A
> > >
> > >
> > > On 3/3/05 8:25 PM, in article
> > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > <red60man@.discussions.microsoft.com> wrote:
> > >
> > > > hi
> > > >
> > > > my query in the stored proc is like ::
> > > >
> > > > select * from tableA where name like @.name
> > > > where @. name is varchar type
> > > >
> > > > but the input i get from a list box(where multiple values can be
> > selected)
> > > >
> > > > i think i have to write the query like
> > > > selct * from tableA where name like @.name1 or name like @.name2 or
name
> > like
> > > > @.name3...
> > > >
> > > > but the problem is that i have no idea before hand to tell how many
> > values
> > > > will be sent as params...
> > > >
> > > > can some one help me
> > > > '
> > > >
> > > > thanks
> > > > red
> > >
> >
> >
> >|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> > HI,
> >
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > > selct * from tableA where name IN (@.name1, @.name2,
> @.name3,........@.nameN)
> >
> > thanks
> > red
> >
> > "Simon Worth" wrote:
> >
> > > You could try passing the selected text fields back as comma seporated
> > > values, and then you can do an IN statement
> > > something like
> > > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> > >
> > > That assumes that you really didn't want to use the like statement as
in
> > > your example you didn't provide any type of wildcard character
> > >
> > > --
> > > Simon Worth
> > >
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
> you
> > > > meant LIKE '%'+@.name+'%'
> > > >
> > > > Anyway, if you are using = it will be much more efficient than LIKE
if
> you
> > > > have an index on name. And you can pass in a (limited)
> comma-separated
> > > list
> > > > and translate it to a table, and join on that table. See
> > > > http://www.aspfaq.com/2248 for an example.
> > > >
> > > > A
> > > >
> > > >
> > > > On 3/3/05 8:25 PM, in article
> > > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > > <red60man@.discussions.microsoft.com> wrote:
> > > >
> > > > > hi
> > > > >
> > > > > my query in the stored proc is like ::
> > > > >
> > > > > select * from tableA where name like @.name
> > > > > where @. name is varchar type
> > > > >
> > > > > but the input i get from a list box(where multiple values can be
> > > selected)
> > > > >
> > > > > i think i have to write the query like
> > > > > selct * from tableA where name like @.name1 or name like @.name2 or
> name
> > > like
> > > > > @.name3...
> > > > >
> > > > > but the problem is that i have no idea before hand to tell how
many
> > > values
> > > > > will be sent as params...
> > > > >
> > > > > can some one help me
> > > > > '
> > > > >
> > > > > thanks
> > > > > red
> > > >
> > >
> > >
> > >
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
>
>

Passing MS SQL2005 query result into Javascript function

I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.

This is how I retrieve the longitude:

<asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"

SelectCommand="SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"></asp:SqlDataSource>

I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:

var map = new GMap2(document.getElementById("map"));

var lat = <%=lat%>;

var lon = <%=lon%>;

var center = new GLatLng(lat,lon);

map.setCenter(center, 13);

However, lat & long do not contain the retrieved result but rather a useless System.something string.

How do I assign the retrieved results to these variables and port them over to Javascript as required?

Many thanks!

Could you show the code that you use to apply the results from the query(ies) to the variableslon andlat? Only it seems the fault is at that part.|||

I think the problem we are having is that we're not sure how to assign the results to a variable.

Do you know how we pass the returned value into a variable in ASP first?

|||

SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)

When we execute that query in SQL Management Studio we get the latest longitude entry from our database no problem (we also do it for latitude).

However, we assumed that this was passed into the ID of the piece of ASP code where we have it and hence accessible to the javascript, but it's not. How do we assign the result of this SQL execution to a string variable and have it accessible to javascript?

I know it sounds simple but I'm just finding rubbish in Google.
Thanks!

|||

You need to assign the two values you get from the database to string variables then write those strings to the page as part of your javascript code block.

Something like this:

SqlCommand cmd = new SqlCommand("SELECT lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)", conn);
string lon = (string)cmd.ExecuteScalar();

lon is now the variable containing a string representaion of the value, which you should be able to use in the way you did before:

<%= lon %>

Have a look at this link for more on the command object and retrieving scalar values:http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx

|||

Thanks Mike.

However, when I put this code between the...

-><asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"
AND
-> </asp:SqlDataSource>

..tags, I get an error that "SqlCommand" is not a valid attribute of the element SQqlDataSource.

|||Am I just to put this C# code between percentage brackets?<% like these?? %>|||

<asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"SelectCommand="SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"></asp:SqlDataSource>

Should I dump this method of connecting to the database altogether? Your method seems to be just using pure c#, whereas we are placing this in a page of html/ASP.

Sorry for sounding completely remedial!

|||Probably easier not to use the Sqldatasource in this instance. Try the C# in the code behind. The sqlDatasource is fine for quickly binding data to a GridView or something.|||

Thanks!I've managed to get the code working in a .cs file and have referenced it at the top of my Routes.aspx file asCodeFile="Routes.aspx.cs"

I set breakpoints and stepped through the code and have a while loop to read the last two longitude and latitude entries from the database.

In the .cs file I set up the variables as doubles.

while (rdr.Read())
{
Double lon=rdr.GetDouble(0);
Double lat=rdr.GetDouble(1);
}

Back in the main aspx file I set::

var lat = <%=lat%>
var lon = <%=lon%>

However I get the error "lat" and "lon" do not exist in the current context. Sad
AFAIK, the .cs code should execute before the aspx file is run?

|||

The cs file and the aspx file both form (partial) parts of the same page class. It all gets executed at the same time. Anyway, it maybe better if you used literal controls:

public string lon;
public string lat;

while (rdr.Read())
{
lon=rdr.GetDouble(0).ToString();
lat=rdr.GetDouble(1).ToString();

}

litLon.Text = lon;
litLat.Text = lat;

Then in your aspx:

var lat = <asp:Literal ID="litLat" runat="server" />
var lon =<asp:Literal ID="litLon" runat="server" />

|||Thank you! This is now working..|||

Hi Guys,

Im now trying to use an array from the C# page and pass it to Javascript on the aspx page.

Is this similar to what we have above?

Thanks for all your help.

|||

Im still not sure how to do this exactly.

Ive been searching forums for a while now.

Ive read a couple of post that say its not possible to pass a C# array back to the Javascript. Can some one confirm this?

Any tips at all?

Thanks

|||

If I were you, I'd create a new post for this. The original question was answered, so this thread is marked as resolved. That means not many people will be looking at it. Also, a new thread with a relevant subject line will be easier for people to search in the future.

When you post, mention the nature of the array - whether it's one dimensional etc, and how you are generating it.

sql

Monday, March 26, 2012

Passing LoginName into SQL query

How do I pass the user's LoginName into the WHERE clause of a SQL query?

I created the following query, but I don't know how to get the user's LoginName and pass its value into Param1. Nothing I try works.

SELECT property.propertyid, property.shortdesc, property.shortdesclink, property.text, property.UserID, [User].UserID AS Expr1, [User].Name FROM property INNER JOIN [User] ON property.UserID = [User].UserID WHERE ([User].Name = @.Param1)

hi, when ever u connecting with sqlserver then

following code will help u in passing parameter

Dim sqlcmd as new sqlcommand

sqlcmd.commandtext =" your query"

Sqlcmd.commandtype= commandtype.text

sqlcmd.parameters.add("@.param1", "loginName")

just add the above lines i hope it will help u.

Smile [:)]

Passing LinkButton text value as Sql Query parameter!

Hi,

Could you inform me programmatically how can I pass LinkButton text value as Sql Query parameter? Big Smile

I tried the

1command.CommandText ="SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = " & SqlDbType.Text = LinkButton12.Text &")"

but it does not work!!!

 Thanks in advance!!!!

Try changing the line to this:

command.CommandText = "SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = '" & LinkButton12.Text & "')"

The statement "SqlDbType.Text = LinkButton12.Text" probably evaluated to a boolean which threw off your query.|||Ok,thank you very much ZeroDefinition... Smile

Passing in query paramater values through WebUserControls

Hi,

I don't want to pass in parameter values in the url to get a certain record from my database (for a better search engine result) so instead i'm using webuser controls (this is amust) with Public Values that should be read by the SqlDataSource. So in the page that contains the usercontrol there is something like:

UserControl.Value = "myvalue"

Now I want the SqlDataSource in the usercontrol to read this value and pass it into a query (in the parameter of the WHERE statement, eg.: @.recordID = myvalue).

Thanks in advance!

Hi,

You should create a public attribute in your user control(eg. below). When using the user control, regard it as a input parameter. In this way ,the parameter can be passed to the SqlDataSource in the user control.

private string _para1;public string para1{get{return _para1; }set{ _para1=value; }}

Thanks.

Passing in a Parameter

Hi All,
I have a Stored Proc and would like to pass in a VARCHAR parameter. The
problem is that the query will need to use it in a IN clause...
DECLARE @.PARAM AS VARCHAR(20)
SET @.PARAM = 'Dan', 'Mike', 'Lisa'
SELECT *
FROM Orders
WHERE Name IN (@.PARAM)
is there a away around this?
Thanks,
Kunkel
On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:

>Hi All,
>I have a Stored Proc and would like to pass in a VARCHAR parameter. The
>problem is that the query will need to use it in a IN clause...
>DECLARE @.PARAM AS VARCHAR(20)
>SET @.PARAM = 'Dan', 'Mike', 'Lisa'
>SELECT *
>FROM Orders
>WHERE Name IN (@.PARAM)
>is there a away around this?
Hi Kunkel,
Several. And they are all described and compared at
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||thanks for that link! it was very helpful. but after i implemented the
articles ideas, i came across this solution:
DECLARE @.Var AS VARCHAR(100)
SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
DECLARE @.SQL AS varchar(1000)
SET @.SQL = 'SELECT *
FROM ORDERS
WHERE
NAME IN (' + @.Var + ')'
Exec(@.SQL)
"Hugo Kornelis" wrote:

> On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:
>
> Hi Kunkel,
> Several. And they are all described and compared at
> http://www.sommarskog.se/arrays-in-sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 28 Apr 2005 15:30:02 -0700, Kunkel wrote:

>thanks for that link! it was very helpful. but after i implemented the
>articles ideas, i came across this solution:
>DECLARE @.Var AS VARCHAR(100)
>SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
>DECLARE @.SQL AS varchar(1000)
>SET @.SQL = 'SELECT *
>FROM ORDERS
>WHERE
>NAME IN (' + @.Var + ')'
>Exec(@.SQL)
Hi Kunkel,
This is dynamic SQL, and I believe that this techinique is discussed at
Erland's site as well. Please don't do this if you can avoid it. It is a
severe breach of security - you give malicious users the ability to
inject SQL.
Erland has a great article with an in-depth explanation of pros and cons
of dynamic SQL as well: http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Passing Formula as a parameter to SQL Query in AddCommand

Hi,

I'am new to Crystal reports,

I created a formula based on that i have to fetch column value from database

for that i wrote a very simple query in SQLCOMMAND

as SELECT <TABLE.CPLUMN> FROM <TABLE>

WHERE <TABLE.OTHERCOLUMN>='@.CRYSTAL REPORT FORMULA'

when closing the commandbox i'am getting error "OLE DB ERROR NO DATABSE RECORDS FOUND"

if i pass a some hardcoded vale(data whic exists in database) then the query is not throwing any error..

Thanks
MikeI don't think you can do that. You can create and use parameters in you query but not formulas. Your query looks really simple, so I have some doubts if it was that necessary to create it in your case.

Why don't you use something like that:

if {TABLE.OTHERCOLUMN}>=here print what you have in your '@.CRYSTAL REPORT FORMULA' then {TABLE.OTHERCOLUMN}

This formula does the same.

Friday, March 23, 2012

Passing DateTime Parameter to MDX Query

HI All,
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >

Passing date Value

Hi,
I have a problem while passing a date value to datefunction.My query is like that ..
declare @.todate varchar(20),@.FromDate varchar(20)
set @.todate='18/10/2005'
set @.FromDate = '11/10/2005'
print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@.todate)), 0)
But i getting the error like this..
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any body help me ..
Thanks and Regards
AJOSH

See my post
http://forums.asp.net/1083329/ShowPost.aspx

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

sql

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil
|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

Friday, March 30, 2012

Passing parameter in MDX query

Passing output parameter from procedure to variable

Passing OLAP Parameters Within a URL

Passing Object Variable as input parameter to an Execute SQL Task Query

Wednesday, March 28, 2012

Passing multi-valued parameter to a Sp

Passing multi-valued parameter to a Sp

passing multiple values to a stored procedure

passing multiple values to a stored procedure

passing multiple values to a stored procedure

Passing MS SQL2005 query result into Javascript function

Monday, March 26, 2012

Passing LoginName into SQL query

Passing LinkButton text value as Sql Query parameter!

Passing in query paramater values through WebUserControls

Passing in a Parameter

Passing Formula as a parameter to SQL Query in AddCommand

Friday, March 23, 2012

Passing DateTime Parameter to MDX Query

Passing date Value

Passing Date Parameter to Oracle Query

Passing Date Parameter to Oracle Query

Passing Date Parameter to Oracle Query

Partitions and FileGroups

Blog Archive

About Me