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.

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 lik
e
> @.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:
>
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values[vbcol=seagreen]
>|||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)[/vbcol
]
thanks
red
"Simon Worth" wrote:
[vbcol=seagreen]
> 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...
> list
> selected)
> like
> values
>
>|||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[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
>
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]|||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...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many[vbcol=seagreen]
>|||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.)
>
> THERE
>
>

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
red
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
|||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[vbcol=seagreen]
> 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:
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values
>
|||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...
> list
> selected)
> like
> values
>
>
|||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[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]
|||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,[vbcol=seagreen]
> 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...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many
>
|||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.)
>
> THERE
>
>

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.
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

Passing Form values from ASP to RS?

Hi everybody,
does anybody knows, if it is possible to pass input from an ASP-page (not
.NET) to RS and populate the dataset with the given values? We are trying to
use the RS so the user is able to print out, export to excel or pdf AFTER
he/she did the collection of the data. For example a search for address data
where the user types in the postal code and/or the cityname, gets the result
inside the browser and is then capable to print out the specific data. I
figured out that to use parameters for this, but as I don't know which
parameters a user typed in, I am not able to build the sql statement in
advance.
Any help would be great.
Regards
MichaelI have not tried this from a form but I have built a URL dynamically to do
the same thing. You could use some client side javascript to build the URL
dynamically or you could just build an interim ASP page with a redirect.
Good Luck
Bill
Michael Bender wrote:
>Hi everybody,
>does anybody knows, if it is possible to pass input from an ASP-page (not
>.NET) to RS and populate the dataset with the given values? We are trying to
>use the RS so the user is able to print out, export to excel or pdf AFTER
>he/she did the collection of the data. For example a search for address data
>where the user types in the postal code and/or the cityname, gets the result
>inside the browser and is then capable to print out the specific data. I
>figured out that to use parameters for this, but as I don't know which
>parameters a user typed in, I am not able to build the sql statement in
>advance.
>Any help would be great.
>Regards
>Michael
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Hi Bill,
thx for your tips. Maybe I'm not getting it, but I don't have the problem to
built the URL dynamically in the ASP page. A more exact example would be:
In a form where a user is able to select from search criteria like: First
Name, Last Name, Postal Code, Town and Country the user only chooses to look
for data based on a certain postal code.
My problem now is to use ONLY the parameter postal code in the SQL statement
to generate the report in RS. (eg: SELECT * from Table_AdressData where
postalcode = @.postalcode) I understand that I can't use all the parameters
in the sql statement (Select * from Table... where postalcode = @.postalcode
and firstname = @.firstname and etc.) because then the statement would fail
if the user does not enter every parameter as a search criteria.
Thx.
Michael
"William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
news:58c684e7207be@.uwe...
>I have not tried this from a form but I have built a URL dynamically to do
> the same thing. You could use some client side javascript to build the
> URL
> dynamically or you could just build an interim ASP page with a redirect.
> Good Luck
> Bill
> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Your question really has nothing to do with integration with ASP. This is
strictly a RS question. For each of your parameters set the default to All
(the string all, if the data type is integer then set the default to an
integer that will not return data).
Then design your query like this:
select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
(field2 = @.Param2 or @.Param2 = 'All')
Then in your URL leave off the other parameters and send only the one you
care about. Or, don't have defaults and pass all the parameter but pass the
ones not filled in with the word All
One reason you might not want to use defaults is if the report is opened on
its own and all the parameters have defaults it will immediate execute.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Bender" <technik@.salescom.de> wrote in message
news:dnmn4d$lnb$03$1@.news.t-online.com...
> Hi Bill,
> thx for your tips. Maybe I'm not getting it, but I don't have the problem
> to built the URL dynamically in the ASP page. A more exact example would
> be:
> In a form where a user is able to select from search criteria like: First
> Name, Last Name, Postal Code, Town and Country the user only chooses to
> look for data based on a certain postal code.
> My problem now is to use ONLY the parameter postal code in the SQL
> statement to generate the report in RS. (eg: SELECT * from
> Table_AdressData where postalcode = @.postalcode) I understand that I can't
> use all the parameters in the sql statement (Select * from Table... where
> postalcode = @.postalcode and firstname = @.firstname and etc.) because then
> the statement would fail if the user does not enter every parameter as a
> search criteria.
> Thx.
> Michael
>
>
> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>|||Thanks very much, Bruce.
Regards
Michael
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OACNUv$$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Your question really has nothing to do with integration with ASP. This is
> strictly a RS question. For each of your parameters set the default to All
> (the string all, if the data type is integer then set the default to an
> integer that will not return data).
> Then design your query like this:
> select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
> (field2 = @.Param2 or @.Param2 = 'All')
> Then in your URL leave off the other parameters and send only the one you
> care about. Or, don't have defaults and pass all the parameter but pass
> the ones not filled in with the word All
> One reason you might not want to use defaults is if the report is opened
> on its own and all the parameters have defaults it will immediate execute.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Bender" <technik@.salescom.de> wrote in message
> news:dnmn4d$lnb$03$1@.news.t-online.com...
>> Hi Bill,
>> thx for your tips. Maybe I'm not getting it, but I don't have the problem
>> to built the URL dynamically in the ASP page. A more exact example would
>> be:
>> In a form where a user is able to select from search criteria like: First
>> Name, Last Name, Postal Code, Town and Country the user only chooses to
>> look for data based on a certain postal code.
>> My problem now is to use ONLY the parameter postal code in the SQL
>> statement to generate the report in RS. (eg: SELECT * from
>> Table_AdressData where postalcode = @.postalcode) I understand that I
>> can't use all the parameters in the sql statement (Select * from Table...
>> where postalcode = @.postalcode and firstname = @.firstname and etc.)
>> because then the statement would fail if the user does not enter every
>> parameter as a search criteria.
>> Thx.
>> Michael
>>
>>
>> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
>> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to
>>do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page
>>(not
>>.NET) to RS and populate the dataset with the given values? We are
>>trying to
>>use the RS so the user is able to print out, export to excel or pdf
>>AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>>
>sql

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing a report parameter to Reporting Services from VB

I have a Report that I want to access that has a parameter input for the date, Production_Date. I want to allow the user to use the DateTimePicker to select the date to use for Production_Date. I can't seem to find the correct way to format what I'm tring to do. My production date field is in a format without punctuation so that it becomes:

Dim ProductionDate = ((DateTimePicker1.Value.Month * 1000000) + (DateTimePicker1.Value.Day * 10000) + DateTimePicker1.Value.Year)

which gives me a value that I want to send as a parameter for the Reporting Services report that I have located on a tab in my project. The report is:

Me.ReportViewer1

I want to send the ProductionDate to the report where the report looks for Production_Date. Obviously, I 'm very new to this. I'd appreciate any suggestions. As it runs now, the default date is loaded (today's date) from the expression I calculated in Reporting services:

=(Now.Month*1000000)+(Now.Day*10000)+Now.Year

This is a big roadblock right now and I can't seem to put together a legal means to pass this value. Thanks for any help.

Have you tried passing DateTimePicker1.Value.ToString() to either Me.ReportViewer1.LocalReport.SetParameters() or Me.ReportViewer1.ServerReport.SetParameters() in a ReportParameter object?|||

Brian,

Thanks for the response. I'm not structuring the object correctly. I'm trying to understand how to set it up. I am enclosing where I'm at from copying some book examples, but I still have a way to go. In this case, I'm not really worrying about the calendar component just yet. I just want to understand how to pass this parameter to my report. In my case, the parameter is a date without delimiters for day or year. The parameter name is Production_Date. I'd like to allow my users to pass simple parameters such as the Production date or shift using the calendar component or a radio button for shift. In this report, there is only one parameter, the Production_Date. I would think this would be a common way to interact with Reporting Services because they use a third of the screen for their header. I'm still trying to get this figured out. Thanks again for the response.

Dim rp = New Microsoft.Reporting.WinForms.ReportParameter()

Me.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote

Me.ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://plant10plc/reportserver/")

Me.ReportViewer1.ServerReport.ReportPath = "/PLC Data Browser 2006/Report1"

Dim reportParameters = ReportViewer1.ServerReport.GetParameters()

rp.Name = "@.Production_Date"

rp.Value = 3292006

Me.ReportViewer1.ServerReport.SetParameters(reportParameters.rp)

Me.ReportViewer1.ShowParameterPrompts = False

Me.ReportViewer1.RefreshReport()

|||

The report viewer has a built in calendar prompt. If you set your parameter data type of date, it will show up in the built in parameter prompting. By using a datatype of date instead of string, you will need to use a formatted data (3/29/2006 instead of 3292006).

As far as your code, there are a few things to change:

1. You don't need to call GetParameters. The return value also doesn't have an rp property (that you are passing in to SetParameters)

2. Your parameter name shouldn't have the @.

3. You should pass in an array or ReportParameter objects to SetParameters. That array should have one element: rp.

|||

I spent time trying to modify my Date type from the calculated integer to the real datetime data type. In my system, I use a data collection service to collect information from the machines in a factory. I can't pass a string I create into a datetime field, but I can pass the timestamp() of the transaction. I kept my original value for keeping my records inserts and updates correct (my ProductionDate starts at 6:30am). What I found is that if I create a date, it enters a valus as 3/30/2006 12:00:00 am. If my software does it, it creates a date as 3/30/2006 3:45:00 pm, and updates to 3/30/2006 3:46:00 pm and so on. When I use the automatic date picker in Reporting Services, it only shows the fields that used M/D/YYYY 12:00:00 am. I could make it work, but there's no actual "date" datatype in SQL Server. This is really frustrating, but life continues....

In returning back to my original issue, I looked up the ReportParameter Members from the help files. It shows that there are Public Properties of ReportParameter which are Value and Name among others. Here's my code now:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim rp = New Microsoft.Reporting.WinForms.ReportParameter

Me.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote

Me.ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://plant10plc/reportserver/")

Me.ReportViewer1.ServerReport.ReportPath = "/PLC Data Browser 2006/Report1"

rp.Name = "ProductionDate"

rp.Value = 3292006

Me.ReportViewer1.ServerReport.SetParameters(rp)

Me.ReportViewer1.ShowParameterPrompts = False

Me.ReportViewer1.RefreshReport()

End Sub

End Class

When I run the code, it says "Public member 'Value' on type 'ReportParameter' not found." As I stated before, I'm kind of new to this. I appreciate the advice and would appreciate tips from Brian or anyone else that can explain what's probably a rookie mistake.

Thanks,

Jack

|||

The property name is called Values, not Value. And it is a collection, so you will need to write something like this:

rp.Values.Add(3292006)

Also, SetParameters takes an array of ReportParameters, not a single instance, so you will need to create an array of one element and assign rp to that first element.

Passing a file path to a SP

I'm trying to write a SP that
accept in input a parameter with the name
of a file (with complete path)
but I noticed some problems...

It's right this way? Thanks!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[BI]
@.FileToImport nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.SQL nvarchar(200)

SET @.SQL = "BULK INSERT tmptable FROM '"+@.FileToImport+"'"
EXEC (@.SQL)
ENDTry enclosing the literal in single-quotes. Specify 2 single-quotes inside
the literal where you have an embedded single-quote:

SET @.SQL = 'BULK INSERT tmptable FROM '''+@.FileToImport+''''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Maury" <maurizio.alberti_TOGLI_@.gmail.com> wrote in message
news:qS7Yf.64843$A83.1587071@.twister1.libero.it...
> I'm trying to write a SP that
> accept in input a parameter with the name
> of a file (with complete path)
> but I noticed some problems...
> It's right this way? Thanks!
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[BI]
> @.FileToImport nvarchar(100)
> AS
> BEGIN
> SET NOCOUNT ON;
> DECLARE @.SQL nvarchar(200)
> SET @.SQL = "BULK INSERT tmptable FROM '"+@.FileToImport+"'"
> EXEC (@.SQL)
> END|||Dan Guzman ha scritto:
> Try enclosing the literal in single-quotes. Specify 2 single-quotes inside
> the literal where you have an embedded single-quote:
> SET @.SQL = 'BULK INSERT tmptable FROM '''+@.FileToImport+''''
IT'S OK!!!!
Thank you very very much....
(and sorry I'm a newbie in SQL Server)

Pass literal text along with a parameter from Report Server

Hi, Is there any way to pass "list{" + Parameters!Parameter3.Value +
"}" as the whole parameter? Right now I have the text part in the
input text box and the user types the value inside the braces. I'm
talking to Informix db which is why I have this strange format.
Thanks, FredKeep the literal text in the query itself with quotes and allow users to just
enter the values and you build the query with literal text's
Amarnath
"fparc" wrote:
> Hi, Is there any way to pass "list{" + Parameters!Parameter3.Value +
> "}" as the whole parameter? Right now I have the text part in the
> input text box and the user types the value inside the braces. I'm
> talking to Informix db which is why I have this strange format.
> Thanks, Fred
>

pass list of id's in xml as i/p to stored proc

hii

I have a problem in when i pass xml as input to stored procedure. The problem i m facing is that :

Say i have a table which has id as primary key in it.Now in my sp i want to delete some rows from that table and in xml i am passin more than 1 id ( it can be four even).

But before deleteion i want to check that delete the with that ID iff the ID exists else raise error for the id w/c does not exist like 'This Id does not exist.'

what i want is to loop between the id's which are being passed as i/p to sp..

and delete the row if it exists elso diaplsy message this id does not exist..

somewhat like this

If Exists(Select ProfileName From Area52 Where Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) ))

Begin

Select * From Area52Docs

WHERE Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) )

DELETE FROM Area52Docs

WHERE Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) )

DELETE FROM Area52

FROM @.XMLString.nodes('Area52') AS T(Item)

where Area52id=T.Item.value( '@.Area52ID', 'uniqueidentifier')

select T.Item.value( '@.Area52ID', 'uniqueidentifier') FROM @.XMLString.nodes('Area52') AS T(Item)

Select 12

End

Else RaisError('This Record Does Not Exist',11,1)

the thing i want to do is as above..but its not workin..it works only for the first id int he list of id's

plzz do help...

Here it is,

Code Snippet

Declare @.Area52ID as XML

Set @.Area52ID = '<root>

<Area52 Area52ID="1"/>

<Area52 Area52ID="2"/>

<Area52 Area52ID="3"/>

</root>'

Declare @.Deletable Table

(

Area52ID int,

IsExists bit

);

Insert Into @.Deletable(Area52ID)

select

T.C.value('@.Area52ID','int') Area52ID

from

@.Area52ID.nodes('/root/Area52') as T(C)

Update @.Deletable

Set

IsExists = case when [Docs].Area52ID is null then 0 else 1 end

From

@.Deletable [Status]

Left Outer Join Area52Docs [Docs]

on [Status].Area52ID = [Docs].Area52ID;

Delete from Area52Docs

Where Exists (Select 1 From @.Deletable [Status]

Where [Status].Area52ID = [Area52Docs].Area52ID)

Select

Area52ID,

Case IsExists When 1 Then 'Area52ID =' + Cast(Area52ID as varchar) + ', Is Deleted Successfully'

When 0 Then 'Area52ID =' +Cast(Area52ID as varchar) + ', Is Not Exists' End

From

@.Deletable

|||

Hii

The approach to reach my problem is very correct as has been told by you.

But this is being done by first fetching the Id's in a table variable..that alternative i was also thinkin will work and its working..But what i was thinking if i could do this wtihout taking any intermediary table and direct from the xml...

Thanx

Supriya

Pass fields as array to custom function?

I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?

You would need to create a function in the code window that accepts the values and inserts them into an array.

ex.

Code Snippet

Function CreateArray(ByVal field1 As String, ByVal field2 As String) As Array
Dim ar(1) As String
ar(0) = field1
ar(1) = field2
Return ar
End Function

I haven't tested it, but I think it will work.

Simone

|||

You can use the Split function.

http://msdn2.microsoft.com/en-us/library/6x627e5f(VS.80).aspx

|||That is true but you would still need to create a string to split using the required fields. Depending on the data those fields contain, you would need to be careful if they hold the delimeter used in the split function.

Pass Datasource Name as input parameter

hi,

how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?

Thanks,

shanthi

Hi,

Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

Pass Datasource Name as input parameter

hi,

how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?

Thanks,

shanthi

Hi,

Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.