multi parameters

Hi,
I want to keyin many values in a parameter textbox, and I try the syntax
like following but it doesn't work, and response the syntax error, why?
SELECT * FROM Product
WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value +"))
Is any mistake in my syntax or how should I modify it?
Thanks!
AngiAngi,
I presume your query string is actually
="SELECT * FROM Product" +
"WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value + "))"
and that you enter values into the parameter field seperated by commas, i.e. 10,11,12
Then it should work. If you still have a problem, try using & instead of + for concatenation.
Regards
Chris McGuigan
"angi" wrote:
> Hi,
> I want to keyin many values in a parameter textbox, and I try the syntax
> like following but it doesn't work, and response the syntax error, why?
> SELECT * FROM Product
> WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value +"))
> Is any mistake in my syntax or how should I modify it?
>
> Thanks!
> Angi
>
>|||Chris,
Thanks to you first.
Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
Dataset SQL dialog or StoreProcedure?
Third, my situation like following..
If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
Excute function be disable, and in Preview, there is an error msg "syntax
error near '=' "
Really have no idea >"<
Thanks again, Chris.
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> Angi,
> I presume your query string is actually
> ="SELECT * FROM Product" +
> "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value + "))"
> and that you enter values into the parameter field seperated by commas,
i.e. 10,11,12
> Then it should work. If you still have a problem, try using & instead of +
for concatenation.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Hi,
> >
> > I want to keyin many values in a parameter textbox, and I try the syntax
> > like following but it doesn't work, and response the syntax error, why?
> >
> > SELECT * FROM Product
> > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > Parameters!Month.Value +"))
> >
> > Is any mistake in my syntax or how should I modify it?
> >
> >
> > Thanks!
> > Angi
> >
> >
> >|||Chris,
Thank you very much!
I know what's the problem with my syntax, I break(use Enter) the syntax, so
when I preview the Report appears syntax error!
With "int" data type, we can enter data value 1,2,5 directly, and I change
to "char" data type, the value must be write as '1','2','5'
Any convenience way to enter "char" value?
And I'm not VB programmer and the newbie of RS, but if there is any chance
to learn, I must to be learning more.
Thanks for your time and your kind. :)
Regards!
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> Hi Angi,
> To write dynamic queries like this, you need to be in the 'Generic Query
Designer' in the 'Data' tab of the report. By default you are in the
'Graphical Query Designer'. To switch, press the button to the left of the
'Run' button (!).
> A dynamic query is actually a SQL query built up in a Visual Basic string.
> I'm guessing you're not a VB programmer, if that's the case you don't have
to learn too much. String handling in VB is similar to string handling in
SQL. The big difference is that SQL strings are delimited by a single quote
('), in VB it's double quotes (").
> I hope that helps.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Chris,
> >
> > Thanks to you first.
> > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > Dataset SQL dialog or StoreProcedure?
> > Third, my situation like following..
> > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > Excute function be disable, and in Preview, there is an error msg
"syntax
> > error near '=' "
> >
> > Really have no idea >"<
> >
> > Thanks again, Chris.
> >
> > Angi
> >
> >
> > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
o¡Ps?D
> > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > Angi,
> > > I presume your query string is actually
> > > ="SELECT * FROM Product" +
> > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > Parameters!Month.Value + "))"
> > >
> > > and that you enter values into the parameter field seperated by
commas,
> > i.e. 10,11,12
> > >
> > > Then it should work. If you still have a problem, try using & instead
of +
> > for concatenation.
> > >
> > > Regards
> > > Chris McGuigan
> > > "angi" wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to keyin many values in a parameter textbox, and I try the
syntax
> > > > like following but it doesn't work, and response the syntax error,
why?
> > > >
> > > > SELECT * FROM Product
> > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value +"))
> > > >
> > > > Is any mistake in my syntax or how should I modify it?
> > > >
> > > >
> > > > Thanks!
> > > > Angi
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Angi,
The only way to make the entry of strings easier for the end user is to try and put the quotes in for them, when you build the SQL string.
Say the WHERE part of your SQL looks like this;
"WHERE Item IN (" + Parameters!Item.Value + ")"
Add a single quote before and after the parameter, and replace any commas with a comma surrounded by single quotes like this;
"WHERE Item IN ('" + Replace(Parameters!Item.Value, ",", "','") + "')"
Note where the single quotes are!
This would convert an entry of 1,2,3 into '1','2','3'
Just watch out for spaces around the commas, that may stop the where clause working properly. You can get round this but that will require more code.
Regards
Chris McGuigan
"angi" wrote:
> Chris,
> Thank you very much!
> I know what's the problem with my syntax, I break(use Enter) the syntax, so
> when I preview the Report appears syntax error!
> With "int" data type, we can enter data value 1,2,5 directly, and I change
> to "char" data type, the value must be write as '1','2','5'
> Any convenience way to enter "char" value?
> And I'm not VB programmer and the newbie of RS, but if there is any chance
> to learn, I must to be learning more.
> Thanks for your time and your kind. :)
> Regards!
> Angi
> "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> > Hi Angi,
> > To write dynamic queries like this, you need to be in the 'Generic Query
> Designer' in the 'Data' tab of the report. By default you are in the
> 'Graphical Query Designer'. To switch, press the button to the left of the
> 'Run' button (!).
> >
> > A dynamic query is actually a SQL query built up in a Visual Basic string.
> > I'm guessing you're not a VB programmer, if that's the case you don't have
> to learn too much. String handling in VB is similar to string handling in
> SQL. The big difference is that SQL strings are delimited by a single quote
> ('), in VB it's double quotes (").
> >
> > I hope that helps.
> > Regards
> > Chris McGuigan
> >
> > "angi" wrote:
> >
> > > Chris,
> > >
> > > Thanks to you first.
> > > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > > Dataset SQL dialog or StoreProcedure?
> > > Third, my situation like following..
> > > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > > Excute function be disable, and in Preview, there is an error msg
> "syntax
> > > error near '=' "
> > >
> > > Really have no idea >"<
> > >
> > > Thanks again, Chris.
> > >
> > > Angi
> > >
> > >
> > > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
> o¡Ps?D
> > > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > > Angi,
> > > > I presume your query string is actually
> > > > ="SELECT * FROM Product" +
> > > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value + "))"
> > > >
> > > > and that you enter values into the parameter field seperated by
> commas,
> > > i.e. 10,11,12
> > > >
> > > > Then it should work. If you still have a problem, try using & instead
> of +
> > > for concatenation.
> > > >
> > > > Regards
> > > > Chris McGuigan
> > > > "angi" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I want to keyin many values in a parameter textbox, and I try the
> syntax
> > > > > like following but it doesn't work, and response the syntax error,
> why?
> > > > >
> > > > > SELECT * FROM Product
> > > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > > Parameters!Month.Value +"))
> > > > >
> > > > > Is any mistake in my syntax or how should I modify it?
> > > > >
> > > > >
> > > > > Thanks!
> > > > > Angi
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql