How to validate the parameter values?

Hi,

Is there any way to validate the input paratemers for the report? For example: I want to restrict the value to be less than 100 in one parameter. How to achieve this?

Thnx in advance.

There is no way to validate and inform the user if the parameter value is not in a given range. Though you can restrict the values that user can select for the parameter by providing pre-defined available values to the parameter, either from query or non-queried.

Shyam

|||

Pre-defining the values will be helpful in case of numeric or string values. What if i want to compare date values. For example:

given date is less than or equal to today's date... I think, this is also not possible!!!

The RS Engine validates the date based on the format only. The validations are not possible.

Some alternative should be thought-of by microsoft... as validations are very necessary.

How to use string values? I really need help for this. Thanks.

Hello,

I am working with ASP.NET/VB and Microsoft SQL 2000 database.

I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book

Then I use this script in results.aspx to put the keywords in a string:

Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub

My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':

SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')

This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().

Can you tell me how to access the string values in the SQL and use it?

Thanks,
Miguel::Can you tell me how to access the string values in the SQL and use it?

Yes. You can not. A sql statement can not access your string variable. No way.

YOu have to handle this from the other side: you have to generate SQL that is correct in the first place. Means: the SQL has to contain all the data you want, so that it does not need to access the string value.

So start from the other end. How does your SQL have to look like (check it in query analyzer), then build code that generates exactly this sql statement. If you have trouble doing so (which I can not believe - I think you basically misunderstand that SQL is just a string that you pass forward and no, it can not connect to your variables anymore), a beginner programmer class will help, as well as any book teaching beginner programming - string manipulation is extremely beginner level.|||Assuming your string array keywords contain two strings "asp" and "book"

you could do this:

Dim s as string
Dim sep as string = ""
dim result as string
For each s in keywords
 result = result & sep & s
 sep = " OR "
Next

you now have a string var called result that looks like this: "asp OR book"

you then use the result string to help you complete the full sql stringsql

How to use SELECT UPPER

Hi

I want to return distinct values from a table in uppercase

Have tried

SELECT UPPER DISTINCT fieldname FROM tablename

But returns an error, what is the correct syntax.

ThanksNearly, upper is a function so you need to provide a column as an argument.
Select distinct upper(fieldname) from tablename|||Thanks,

However that now causes an error, my select statement is now:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

This is used in a datareader i.e.

Dim MySQL As String = "SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479"
Dim MyConnection As New SqlConnection(ConnectionString)
Dim DataReader As SqlDataReader
Dim SQLCommand As New SqlCommand(MySQL, MyConnection)
MyConnection.Open()
DataReader = SQLCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
DropDownList1.DataSource = DataReader
DropDownList1.DataTextField = ("maketext")
DropDownList1.DataBind()

The error message is:

DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name maketext.

Thanks

Ben|||it doesnt seem to be the problem with the upper word. check if the col name is correct..

hth|||When you run a column through a function, you need to provide the result with an alias:

SELECT DISTINCT UPPER (maketext) AS maketext from VsVehicles WHERE dealerrefnum=8479

SELECT SUM(Volume) AS Volume FROM MilkBottles|||nope an alias is not necessary...unless you want to parse through the loop ( if the query returns one ) or get the value into a variable...you dont need an alias when you use a function...alias is only a way to identify the column...

hth|||Well, I could be wrong, but he is databinding to a datareader, and setting the DataTextField to a column named "maketext."

His query:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

returns no columns named "maketext." It does include a column (with no column name) derived from the column named "maketext", but no actual column named "maketext".

My guess is that aliasing the column name will solve his problem...|||i see what you mean...i was looking at the select stmt all the while...

::nope an alias is not necessary...unless you want to parse through the loop ( if the query
::returns one ) or get the value into a variable...you dont need an alias when you use a
::function...alias is only a way to identify the column...

from my stmt, i meant the same thing by saying "get the value into a variable"

i was prbly not clear...good you clarified it out..

dinakar|||Yes that worked, thankyou

SELECT DISTINCT UPPER (maketext) AS maketext FROM VsVehicles

Thanks

Bensql

How to use SCOPE_IDENTITY() in 4-layer architecture

I have the following stored procedure:

INSERT INTO MyTable( Value1, Value 2)

VALUES( @.Value1, @.Value2)

SELECT SCOPE_IDENTITY()

How do I put this sp in the DAL typed dataset, so I can get the Identity value in the Business Layer?

From the DAL execute a query and use cmd.ExecuteScalar() this will return the identity field...

Please specify more details if this won't help you

|||

Hi,

You can use a SqlDataAdapter to execute this stored procedure as a common SELECT query. The result set will be filled into a DataSet. And the first row, first column of the first table will be the scope identity.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

How to use row values from query

Our database is normalized to the point that I need to build a dataset to
render a report from.
For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and 15
rows of county names. I need them to be in 1 row and 15 columns to make up
the data going accross the report. What is the best way to do this?
Thanks,
ShawnI you create a matrix report with County Name across the top of the matrix,
it will automatically generate the appropriate number of columns...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:55F38FB8-CBD1-4C1C-9611-8247A1A5C60E@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from.
> For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and
15
> rows of county names. I need them to be in 1 row and 15 columns to make
up
> the data going accross the report. What is the best way to do this?
> Thanks,
> Shawn
>|||I need to make a report that looks like...
Statistic A Statistic B Statistic C Statistic D
----
99 07 102 91
It would be easy but the values are all in one column in the table, like...
KeyValue | StatisticCode | StatisticValue
001| A| 99
002| B| 07
003| D| 91
004| C| 102
Thanks,
Shawn
"Wayne Snyder" wrote:
> I you create a matrix report with County Name across the top of the matrix,
> it will automatically generate the appropriate number of columns...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:55F38FB8-CBD1-4C1C-9611-8247A1A5C60E@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from.
> >
> > For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
> > (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and
> 15
> > rows of county names. I need them to be in 1 row and 15 columns to make
> up
> > the data going accross the report. What is the best way to do this?
> >
> > Thanks,
> > Shawn
> >
>
>

How to use multle values for Where clause

Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.

I have the following tables

tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing

bid_id Uci_id

123 1000

123 2000

123 1050

tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.

Uci_ID Sub_ID

1000 456

1000 2345

2000 456

1050 2345

2000 2345

This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.

Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.

This is how you have to do it

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID IN (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)

|||Try this... SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID in (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)|||

There are 2 ways.

SELECT Sub_IDfrom tblSubs_BY_UCIwhere Uci_IDin (SELECT Uci_IDfrom tblBidWHERE Bid_ID = 123 )-- or ----SELECT tb.Uci_ID , ts.Sub_IDfrom tblSubs_BY_UCI ts , tblBid tbwhere ts.Uci_ID = tb.Uci_IDand tb.Bid_ID = 123

Hope this will help.|||

Thanks.

By Changing the = to IN, it worked perfectly.

How To Use Interactive Sort on Grouping Reports?

Dear Anyone,

We created some reports that are mostly grouped reports. These are reports that doesnt have detail values but rather uses the grouping section only. We've enabled the interactive sort feature of RS2005. But unfortunately, it doesnt seem to sort at all when we click on the sort links. Can anyone pease enlighten me on why this is so?

Thanks,

Joseph

Interactive Sort has two options: the sort target scope and the sort expression scope.

It sounds like you are using just the default settings - in that case it will sort the underlying detail data, but not the groups. You will need to explicitly specify the scope in which the sort expressions should be evaluated (in your case the name of the grouping).

-- Robert

|||Same issue, are you able to get this to work? I have a group total I want to sort on, but nothing happens.|||

Please read the response with a sample on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=409872&SiteID=1&mode=1

-- Robert

How To Use Interactive Sort on Grouping Reports?

Dear Anyone,

We created some reports that are mostly grouped reports. These are reports that doesnt have detail values but rather uses the grouping section only. We've enabled the interactive sort feature of RS2005. But unfortunately, it doesnt seem to sort at all when we click on the sort links. Can anyone pease enlighten me on why this is so?

Thanks,

Joseph

Interactive Sort has two options: the sort target scope and the sort expression scope.

It sounds like you are using just the default settings - in that case it will sort the underlying detail data, but not the groups. You will need to explicitly specify the scope in which the sort expressions should be evaluated (in your case the name of the grouping).

-- Robert

|||Same issue, are you able to get this to work? I have a group total I want to sort on, but nothing happens.|||

Please read the response with a sample on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=409872&SiteID=1&mode=1

-- Robert

How to use Distinct in XML Column

We have used XML Column in our Table.

I want to select a distict values from the xml colunm.

is there any way to use distinct?

Regards

Vasanth Thangasamy

Do you want to get XML out, or a single row rowset or multiple rows?

Can you paste your xml structure?

|||

This is my XML Structure. I have stored this XML in a colunm. Here i want to fetch the disticnt of XML/Directors/Dir/Name........

<xml>

<MovieTitle>Vettaiyadu Vilaiyadu</MovieTitle>

<ImgName>KamalJyo.jpg</ImgName>

<Directors>

<Dir>

<Name>DirYou</Name>

<Status>N/A</Status>

</Dir>

<Dir>

<Name>DireMe</Name>

<Status>Pending</Status>

</Dir>

<Dir>

<Name>Gowtham</Name>

<Status>Killed</Status>

</Dir>

<Dir>

<Name>Manirat</Name>

<Status>N/A</Status>

</Dir>

</Directors>

<CastMem>

<CM>

<CM_Name>Kamal</CM_Name>

<Status>N/A</Status>

</CM>

<CM>

<CM_Name>Jyothika</CM_Name>

<Status>Aprvd</Status>

</CM>

<CM>

<CM_Name>PrakshRaj</CM_Name>

<Status>Pending</Status>

</CM>

<CM>

<CM_Name>Asai</CM_Name>

<Status>Killed</Status>

</CM>

</CastMem>

<ImgNum>2128</ImgNum>

<Tags>

<Tag>

<value>FIR</value>

</Tag>

<Tag>

<value>SEC</value>

</Tag>

<Tag>

<value>THI</value>

</Tag>

<Tag>

<value>FOR</value>

</Tag>

</Tags>

<ImgSize>

<Ht>21.2</Ht>

<Wt>22.2</Wt>

<Si>222.2</Si>

</ImgSize>

<WebAccess>True</WebAccess>

</xml>

|||

You can use the nodes method to extract the values you are interesting in and then use DISTINCT in the projection.

For example:

declare @.x xml
set @.x = N'<you xml here/>'

select distinct ref.a.value('text()[1]', 'nvarchar(25)') as name
from @.x.nodes('/xml/Directors/Dir/Name') ref(a)

If, on the other hand, you intend to use the distinct values inside of XQuery, you can use the function fn:distinct-values:

select @.x.query('fn:distinct-values(/xml/Directors/Dir/Name)')

Regards,

Galex

How to use default values of parameters

My report has 6 parameters. 3 Of them are defined with default values. How can I via the webservice method use these default values? I am building the parameters dynamically. You can see that the first 3 parameters are filled in depending on the value selected in a dropdown box. With the last 3 I want to use the default values. Is this possible and how do I do this. When with the first 3 parameters, no value is selected in the dropdown box, the default values shouls also be used but this is not yet programmed. Can anyone help me with this?
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
int intParam = 0;
foreach (ReportParameter rp in parameters)
{
rptParameters[intParam] = new ParameterValue();
rptParameters[intParam].Name = rp.Name;
switch(rp.Name.ToUpper())
{
case "OPCO":
rptParameters[intParam].Value = this.Opco;
break;
case "CONFIGTYPEGROUP":
rptParameters[intParam].Value = this.ConfigtypeGroup;
break;
case "CONFIGTYPE":
rptParameters[intParam].Value = this.ConfigType;
break;
case "ERRORCODE":
rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
case "VISITDATETIME_FROM":
rptParameters[intParam].Value = rp.DefaultValues.ToString())
break;
case "VISITDATETIME_TO":
rptParameters[intParam].Value = rp.DefaultValues.ToString();
break;
}
intParam++;
}
firstPage = rs.Render( reportPath,
format,
null,
deviceInfo,
rptParameters,
null,
null,
out encoding,
out mimeType,
out reportHistoryParameters,
out warnings,
out streamIDs);I already found the solution.
I have to write:
rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
in stead of:
rptParameters[intParam].Value = rp.DefaultValues.ToString();
This will give back the default value defined in the report
"Gert" wrote:
> My report has 6 parameters. 3 Of them are defined with default values. How can I via the webservice method use these default values? I am building the parameters dynamically. You can see that the first 3 parameters are filled in depending on the value selected in a dropdown box. With the last 3 I want to use the default values. Is this possible and how do I do this. When with the first 3 parameters, no value is selected in the dropdown box, the default values shouls also be used but this is not yet programmed. Can anyone help me with this?
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
> ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
> int intParam = 0;
> foreach (ReportParameter rp in parameters)
> {
> rptParameters[intParam] = new ParameterValue();
> rptParameters[intParam].Name = rp.Name;
> switch(rp.Name.ToUpper())
> {
> case "OPCO":
> rptParameters[intParam].Value = this.Opco;
> break;
> case "CONFIGTYPEGROUP":
> rptParameters[intParam].Value = this.ConfigtypeGroup;
> break;
> case "CONFIGTYPE":
> rptParameters[intParam].Value = this.ConfigType;
> break;
> case "ERRORCODE":
> rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> case "VISITDATETIME_FROM":
> rptParameters[intParam].Value = rp.DefaultValues.ToString())
> break;
> case "VISITDATETIME_TO":
> rptParameters[intParam].Value = rp.DefaultValues.ToString();
> break;
> }
> intParam++;
> }
> firstPage = rs.Render( reportPath,
> format,
> null,
> deviceInfo,
> rptParameters,
> null,
> null,
> out encoding,
> out mimeType,
> out reportHistoryParameters,
> out warnings,
> out streamIDs);|||If you want the server to use the current default values, then don't pass in
anything for this values. Only pass in the three parameters that don't have
defaults and the server will then use the current default value during
rendering.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert" <Gert@.discussions.microsoft.com> wrote in message
news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> I already found the solution.
> I have to write:
> rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> in stead of:
> rptParameters[intParam].Value = rp.DefaultValues.ToString();
> This will give back the default value defined in the report
>
> "Gert" wrote:
> > My report has 6 parameters. 3 Of them are defined with default values.
How can I via the webservice method use these default values? I am building
the parameters dynamically. You can see that the first 3 parameters are
filled in depending on the value selected in a dropdown box. With the last 3
I want to use the default values. Is this possible and how do I do this.
When with the first 3 parameters, no value is selected in the dropdown box,
the default values shouls also be used but this is not yet programmed. Can
anyone help me with this?
> >
> > ReportParameter[] parameters = null;
> >
> > parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
reportHistoryParameters, credentials);
> >
> > ParameterValue[] rptParameters = new
ParameterValue[parameters.GetLength(0)];
> >
> > int intParam = 0;
> > foreach (ReportParameter rp in parameters)
> > {
> > rptParameters[intParam] = new ParameterValue();
> > rptParameters[intParam].Name = rp.Name;
> > switch(rp.Name.ToUpper())
> > {
> > case "OPCO":
> > rptParameters[intParam].Value = this.Opco;
> > break;
> > case "CONFIGTYPEGROUP":
> > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > break;
> > case "CONFIGTYPE":
> > rptParameters[intParam].Value = this.ConfigType;
> > break;
> > case "ERRORCODE":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > case "VISITDATETIME_FROM":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > break;
> > case "VISITDATETIME_TO":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > break;
> > }
> >
> > intParam++;
> > }
> >
> > firstPage = rs.Render( reportPath,
> > format,
> > null,
> > deviceInfo,
> > rptParameters,
> > null,
> > null,
> > out encoding,
> > out mimeType,
> > out reportHistoryParameters,
> > out warnings,
> > out streamIDs);|||I can't use your option because I have to loop through all the report parameterss because I have different reports with different paramaters and I don't know in the beginning of the program for which parameter the user will select a value or not. If no value selected, the default value of the report is retrieved.
I use the following code and this works perfect. I use a Hash table(see below) to store the paramaters and its values if they are selected.
********************************************************
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
int intParam = 0;
foreach (ReportParameter rp in parameters)
{
rptParameters[intParam] = new ParameterValue();
rptParameters[intParam].Name = rp.Name.ToUpper();
if (hashParameters.ContainsKey(rp.Name.ToUpper()))
{
if (hashParameters[rp.Name].ToString() != "")
rptParameters[intParam].Value = hashParameters[rp.Name].ToString();
else
rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
}
intParam++;
}
********************************************************
The Hash table is filled as follows:
********************************************************
hashParameters = new Hashtable();
hashParameters.Add("OPCO", this.Opco);
hashParameters.Add("CONFIGTYPEGROUP", this.ConfigtypeGroup);
hashParameters.Add("CONFIGTYPE", this.ConfigType);
hashParameters.Add("ERRORCODE", "");
hashParameters.Add("VISITDATETIME_FROM", "");
hashParameters.Add("VISITDATETIME_TO", "");
hashParameters.Add("COUNTERNUMBER", "");
hashParameters.Add("ERRORDATETIME_FROM", "");
hashParameters.Add("ERRORDATETIME_TO", "");
hashParameters.Add("PARAMETERNUMBER", "");
hashParameters.Add("MODIFICATIONNR", "");
********************************************************
"Daniel Reib [MSFT]" wrote:
> If you want the server to use the current default values, then don't pass in
> anything for this values. Only pass in the three parameters that don't have
> defaults and the server will then use the current default value during
> rendering.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Gert" <Gert@.discussions.microsoft.com> wrote in message
> news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> > I already found the solution.
> > I have to write:
> > rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> > in stead of:
> > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> >
> > This will give back the default value defined in the report
> >
> >
> > "Gert" wrote:
> >
> > > My report has 6 parameters. 3 Of them are defined with default values.
> How can I via the webservice method use these default values? I am building
> the parameters dynamically. You can see that the first 3 parameters are
> filled in depending on the value selected in a dropdown box. With the last 3
> I want to use the default values. Is this possible and how do I do this.
> When with the first 3 parameters, no value is selected in the dropdown box,
> the default values shouls also be used but this is not yet programmed. Can
> anyone help me with this?
> > >
> > > ReportParameter[] parameters = null;
> > >
> > > parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
> reportHistoryParameters, credentials);
> > >
> > > ParameterValue[] rptParameters = new
> ParameterValue[parameters.GetLength(0)];
> > >
> > > int intParam = 0;
> > > foreach (ReportParameter rp in parameters)
> > > {
> > > rptParameters[intParam] = new ParameterValue();
> > > rptParameters[intParam].Name = rp.Name;
> > > switch(rp.Name.ToUpper())
> > > {
> > > case "OPCO":
> > > rptParameters[intParam].Value = this.Opco;
> > > break;
> > > case "CONFIGTYPEGROUP":
> > > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > > break;
> > > case "CONFIGTYPE":
> > > rptParameters[intParam].Value = this.ConfigType;
> > > break;
> > > case "ERRORCODE":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > > case "VISITDATETIME_FROM":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > > break;
> > > case "VISITDATETIME_TO":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > > break;
> > > }
> > >
> > > intParam++;
> > > }
> > >
> > > firstPage = rs.Render( reportPath,
> > > format,
> > > null,
> > > deviceInfo,
> > > rptParameters,
> > > null,
> > > null,
> > > out encoding,
> > > out mimeType,
> > > out reportHistoryParameters,
> > > out warnings,
> > > out streamIDs);
>
>|||Sure you could, just dynamically allocate an array and add to that array as
you find parameters the user has set. Then use the dynamic array to pass to
render. The only problem with your code would be if the default values
changed between your call to GetReportParameters and Render. In your code
you will use the old value. If you are displaying this value to the user,
then this is probably what you want. If you are displaying something like
'Use default' then the user may not get the actual default at render time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert" <Gert@.discussions.microsoft.com> wrote in message
news:C8CD66F1-CFB0-4A33-A90E-00919BB04B1B@.microsoft.com...
> I can't use your option because I have to loop through all the report
parameterss because I have different reports with different paramaters and I
don't know in the beginning of the program for which parameter the user will
select a value or not. If no value selected, the default value of the report
is retrieved.
> I use the following code and this works perfect. I use a Hash table(see
below) to store the paramaters and its values if they are selected.
> ********************************************************
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
reportHistoryParameters, credentials);
> ParameterValue[] rptParameters = new
ParameterValue[parameters.GetLength(0)];
> int intParam = 0;
> foreach (ReportParameter rp in parameters)
> {
> rptParameters[intParam] = new ParameterValue();
> rptParameters[intParam].Name = rp.Name.ToUpper();
> if (hashParameters.ContainsKey(rp.Name.ToUpper()))
> {
> if (hashParameters[rp.Name].ToString() != "")
> rptParameters[intParam].Value =hashParameters[rp.Name].ToString();
> else
> rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> }
> intParam++;
> }
> ********************************************************
> The Hash table is filled as follows:
> ********************************************************
> hashParameters = new Hashtable();
> hashParameters.Add("OPCO", this.Opco);
> hashParameters.Add("CONFIGTYPEGROUP", this.ConfigtypeGroup);
> hashParameters.Add("CONFIGTYPE", this.ConfigType);
> hashParameters.Add("ERRORCODE", "");
> hashParameters.Add("VISITDATETIME_FROM", "");
> hashParameters.Add("VISITDATETIME_TO", "");
> hashParameters.Add("COUNTERNUMBER", "");
> hashParameters.Add("ERRORDATETIME_FROM", "");
> hashParameters.Add("ERRORDATETIME_TO", "");
> hashParameters.Add("PARAMETERNUMBER", "");
> hashParameters.Add("MODIFICATIONNR", "");
> ********************************************************
>
> "Daniel Reib [MSFT]" wrote:
> > If you want the server to use the current default values, then don't
pass in
> > anything for this values. Only pass in the three parameters that don't
have
> > defaults and the server will then use the current default value during
> > rendering.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Gert" <Gert@.discussions.microsoft.com> wrote in message
> > news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> > > I already found the solution.
> > > I have to write:
> > > rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> > > in stead of:
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > >
> > > This will give back the default value defined in the report
> > >
> > >
> > > "Gert" wrote:
> > >
> > > > My report has 6 parameters. 3 Of them are defined with default
values.
> > How can I via the webservice method use these default values? I am
building
> > the parameters dynamically. You can see that the first 3 parameters are
> > filled in depending on the value selected in a dropdown box. With the
last 3
> > I want to use the default values. Is this possible and how do I do this.
> > When with the first 3 parameters, no value is selected in the dropdown
box,
> > the default values shouls also be used but this is not yet programmed.
Can
> > anyone help me with this?
> > > >
> > > > ReportParameter[] parameters = null;
> > > >
> > > > parameters = rs.GetReportParameters(reportPath, historyID,
forRendering,
> > reportHistoryParameters, credentials);
> > > >
> > > > ParameterValue[] rptParameters = new
> > ParameterValue[parameters.GetLength(0)];
> > > >
> > > > int intParam = 0;
> > > > foreach (ReportParameter rp in parameters)
> > > > {
> > > > rptParameters[intParam] = new ParameterValue();
> > > > rptParameters[intParam].Name = rp.Name;
> > > > switch(rp.Name.ToUpper())
> > > > {
> > > > case "OPCO":
> > > > rptParameters[intParam].Value = this.Opco;
> > > > break;
> > > > case "CONFIGTYPEGROUP":
> > > > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > > > break;
> > > > case "CONFIGTYPE":
> > > > rptParameters[intParam].Value = this.ConfigType;
> > > > break;
> > > > case "ERRORCODE":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > > > case "VISITDATETIME_FROM":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > > > break;
> > > > case "VISITDATETIME_TO":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > > > break;
> > > > }
> > > >
> > > > intParam++;
> > > > }
> > > >
> > > > firstPage = rs.Render( reportPath,
> > > > format,
> > > > null,
> > > > deviceInfo,
> > > > rptParameters,
> > > > null,
> > > > null,
> > > > out encoding,
> > > > out mimeType,
> > > > out reportHistoryParameters,
> > > > out warnings,
> > > > out streamIDs);
> >
> >
> >

How to use database values in an enum or class, so developer has intellisense support....

I have a SQL database table with all languages used in my application.

I would like to use an enum or constant class with all the languages, so every developer of the app sees directly what languages are available.

Can this be done?

Use Enum.Parse method as shows following code snippet:

enum Language{ English, German, Slovak}Language lang = (Language)Enum.Parse(typeof(Language),"Slovak");

|||

That will not work, I think I have to work the way around.

Put all constants in a constant class and then insert these values into a SQL database table.

Anyone a better solution?

|||Why not work? When you put all enum values into database and after (when you will read values from database) you use Enum.Parse(typeof(Language), VALUE_FROM_DATABASE) method it will work.|||So with the parse method, you can add values to the enum?|||

Maybe you can just put your languages into drop down list ( you have to display them anyway to the user to see or select ) and just use this control as storage for you languages info? It will work mostly as enum you have language name and its ID (index) if you need. You can do a lot with it.

Thanks

JPazgier

How to use BETWEEN with custom-ordered values

We have a 10 digit primary key value in this format: M000123456. The
order for this key is first determined by positions 3 and 4 in this
example, then positions 1 and 2. So a brief sample of correct ordering
would look like this:
M000001501
M000011501
M000021501
M000001601
M000011601
M000021601

Now my question: how can I use a BETWEEN (or > and <) in my WHERE
clause to get a range of values for this column? I use the following
ORDER BY clause to control how the results are sorted, but I can't get
the same logic to work with BETWEEN in a WHERE clause.

ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
How do I return values between M000011501 and M000011601 for example?On 19 Dec 2004 16:36:14 -0800, ian.proffer@.gmail.com wrote:

>We have a 10 digit primary key value in this format: M000123456. The
>order for this key is first determined by positions 3 and 4 in this
>example, then positions 1 and 2. So a brief sample of correct ordering
>would look like this:
>M000001501
>M000011501
>M000021501
>M000001601
>M000011601
>M000021601
>Now my question: how can I use a BETWEEN (or > and <) in my WHERE
>clause to get a range of values for this column? I use the following
>ORDER BY clause to control how the results are sorted, but I can't get
>the same logic to work with BETWEEN in a WHERE clause.
>ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
>How do I return values between M000011501 and M000011601 for example?

Hi Ian,

Usually, this kind of requirement is a sign of a bad table design. I have
the suspicion that both the two digits in position 3 and 4 and the two
digits in position 1 and 2 have a specific meaning in your business. If
that is the case, you should probably store these as seperate columns. You
can always paste the different values together for outputting as one
column.

Anyway, based on your ORDER BY clause, the BETWEEN predicate would read

WHERE SUBSTRING(<columnname>, 7, 2) + SUBSTRING (<columnname>, 5, 2)
BETWEEN SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2)
AND SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi

It may be easier if you stored the correctly ordered string rather than the
one used for display, and have the display string in a view/computed column
or part of the select statement.

You don't say how the rest of the string is arranged, but something like:

SELECT SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2),
* FROM <tablename>
WHERE SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2) between
SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2) + SUBSTRING
('M000011501', 1, 4) + SUBSTRING ('M000011501', 9, 2)
and SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2) +
SUBSTRING ('M000011601', 1, 4) + SUBSTRING ('M000011601', 9, 2)
ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)

will give your result.

John

<ian.proffer@.gmail.com> wrote in message
news:1103502974.453433.144600@.f14g2000cwb.googlegr oups.com...
> We have a 10 digit primary key value in this format: M000123456. The
> order for this key is first determined by positions 3 and 4 in this
> example, then positions 1 and 2. So a brief sample of correct ordering
> would look like this:
> M000001501
> M000011501
> M000021501
> M000001601
> M000011601
> M000021601
> Now my question: how can I use a BETWEEN (or > and <) in my WHERE
> clause to get a range of values for this column? I use the following
> ORDER BY clause to control how the results are sorted, but I can't get
> the same logic to work with BETWEEN in a WHERE clause.
> ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
> How do I return values between M000011501 and M000011601 for example?|||Thanks for the reply Hugo. (I don't usually multi-post, btw,
but...sorry.) Your solution works great, even though I didn't
accurately post my sample data (where M000251501 is followed by
M000001601).

And oh, if only I could redesign the table! Out of my control however
with this application <sigh>.

Thanks again,
-- Ian

How to use a checkbox for Boolean Report Parameter?

All booleans values that I set inside the report parameters show up as true/false radio buttons on the report. Is there anyway to make these checkboxes instead of radio buttons?

You can convert radiobuttons to dropdown by specifying available values "true" and "false"

To get checkboxes you could use String or Integer parameter instead of Boolean

|||

Can you explain,I tried this option it is not working. I create a Sp,it accepts 1 or 0 as input parameter.

Thanks,

Prabu

|||

In the available values grid enter two rows:

1st: set label to True, value to 1

2nd: label to False, value to 0

|||

It would be a nice feature in further releases: Show a single checkbox for a boolean instead of two radiobuttons. Then unchecked would be false and checked would be true.

It would also be nice to define the default return value ... now the radiobutton (true) returns true. (Well it makes sence but sometimes its usefull to turn it around and keep the radiobuttons)

How to use a checkbox for Boolean Report Parameter?

All booleans values that I set inside the report parameters show up as true/false radio buttons on the report. Is there anyway to make these checkboxes instead of radio buttons?

You can convert radiobuttons to dropdown by specifying available values "true" and "false"

To get checkboxes you could use String or Integer parameter instead of Boolean

|||

Can you explain,I tried this option it is not working. I create a Sp,it accepts 1 or 0 as input parameter.

Thanks,

Prabu

|||

In the available values grid enter two rows:

1st: set label to True, value to 1

2nd: label to False, value to 0

|||

It would be a nice feature in further releases: Show a single checkbox for a boolean instead of two radiobuttons. Then unchecked would be false and checked would be true.

It would also be nice to define the default return value ... now the radiobutton (true) returns true. (Well it makes sence but sometimes its usefull to turn it around and keep the radiobuttons)