Problem with Large data amounts

I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.

Unfortunately, Reporting Services has some memory limitations in the current (SQL 2K and SQL 2K5). Because we support things like dataset aggregates (you can have sum(x) on the first page of your report), we materialize the entire dataset. Some things you might try:

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Is there any way to be sure to dispose of the memory being allocated by the reportviewer? I can generate one report, close it, wait, open another, and the memory allocation just keeps growing and growing.

Any help is appreciated.

|||

I seem to be having a similar problem with a Xeon and 4Gig of memory. In my case, after the out of memory error happens, remote connections stop working stating that there is insufficient memory. In other words I can't even run a select statement in Management Studio after it hits the memory error. I have to restart the SQL Service.

HELP!!!

|||

First off. A human does not look at 300,000 records. Most of the time I have seen this it has been to export to Excel. When exporting to Excel export as CSV ASCII (although I don't think Excel can handle more that 64,000 records).

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

Is this still a problem with Reporting Services? We are having a similar problem with .net choking on large data sets and were considering using Reporting Services as an alternative. Is Microsoft an enterprise player or not?

We are required to periodically return several hundred thousand rows from SQL Server. I'm tired of being told that is a design problem. Unfortunately when our client says he needs to print a detail report from his general ledger for his auditor we don't have the luxury of telling him Microsoft doesn't think he needs that large of a report. If he can't get the data he needs from us, he'll be happy to go down the street to get it (read Oracle).

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

Problem with Large data amounts

I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.

Unfortunately, Reporting Services has some memory limitations in the current (SQL 2K and SQL 2K5). Because we support things like dataset aggregates (you can have sum(x) on the first page of your report), we materialize the entire dataset. Some things you might try:

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Is there any way to be sure to dispose of the memory being allocated by the reportviewer? I can generate one report, close it, wait, open another, and the memory allocation just keeps growing and growing.

Any help is appreciated.

|||

I seem to be having a similar problem with a Xeon and 4Gig of memory. In my case, after the out of memory error happens, remote connections stop working stating that there is insufficient memory. In other words I can't even run a select statement in Management Studio after it hits the memory error. I have to restart the SQL Service.

HELP!!!

|||

First off. A human does not look at 300,000 records. Most of the time I have seen this it has been to export to Excel. When exporting to Excel export as CSV ASCII (although I don't think Excel can handle more that 64,000 records).

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

Is this still a problem with Reporting Services? We are having a similar problem with .net choking on large data sets and were considering using Reporting Services as an alternative. Is Microsoft an enterprise player or not?

We are required to periodically return several hundred thousand rows from SQL Server. I'm tired of being told that is a design problem. Unfortunately when our client says he needs to print a detail report from his general ledger for his auditor we don't have the luxury of telling him Microsoft doesn't think he needs that large of a report. If he can't get the data he needs from us, he'll be happy to go down the street to get it (read Oracle).

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

sql

Problem with Large data amounts

I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.

Unfortunately, Reporting Services has some memory limitations in the current (SQL 2K and SQL 2K5). Because we support things like dataset aggregates (you can have sum(x) on the first page of your report), we materialize the entire dataset. Some things you might try:

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Is there any way to be sure to dispose of the memory being allocated by the reportviewer? I can generate one report, close it, wait, open another, and the memory allocation just keeps growing and growing.

Any help is appreciated.

|||

I seem to be having a similar problem with a Xeon and 4Gig of memory. In my case, after the out of memory error happens, remote connections stop working stating that there is insufficient memory. In other words I can't even run a select statement in Management Studio after it hits the memory error. I have to restart the SQL Service.

HELP!!!

|||

First off. A human does not look at 300,000 records. Most of the time I have seen this it has been to export to Excel. When exporting to Excel export as CSV ASCII (although I don't think Excel can handle more that 64,000 records).

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

Is this still a problem with Reporting Services? We are having a similar problem with .net choking on large data sets and were considering using Reporting Services as an alternative. Is Microsoft an enterprise player or not?

We are required to periodically return several hundred thousand rows from SQL Server. I'm tired of being told that is a design problem. Unfortunately when our client says he needs to print a detail report from his general ledger for his auditor we don't have the luxury of telling him Microsoft doesn't think he needs that large of a report. If he can't get the data he needs from us, he'll be happy to go down the street to get it (read Oracle).

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

Problem with Large data amounts

I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.

Unfortunately, Reporting Services has some memory limitations in the current (SQL 2K and SQL 2K5). Because we support things like dataset aggregates (you can have sum(x) on the first page of your report), we materialize the entire dataset. Some things you might try:

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Is there any way to be sure to dispose of the memory being allocated by the reportviewer? I can generate one report, close it, wait, open another, and the memory allocation just keeps growing and growing.

Any help is appreciated.

|||

I seem to be having a similar problem with a Xeon and 4Gig of memory. In my case, after the out of memory error happens, remote connections stop working stating that there is insufficient memory. In other words I can't even run a select statement in Management Studio after it hits the memory error. I have to restart the SQL Service.

HELP!!!

|||

First off. A human does not look at 300,000 records. Most of the time I have seen this it has been to export to Excel. When exporting to Excel export as CSV ASCII (although I don't think Excel can handle more that 64,000 records).

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

Is this still a problem with Reporting Services? We are having a similar problem with .net choking on large data sets and were considering using Reporting Services as an alternative. Is Microsoft an enterprise player or not?

We are required to periodically return several hundred thousand rows from SQL Server. I'm tired of being told that is a design problem. Unfortunately when our client says he needs to print a detail report from his general ledger for his auditor we don't have the luxury of telling him Microsoft doesn't think he needs that large of a report. If he can't get the data he needs from us, he'll be happy to go down the street to get it (read Oracle).

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

Problem with Group Totals and Counts

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

Hi Maria,

Nice to see a well articulated problem for a change

What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.

You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:

In a textbox inside the list I would expect to see 1. This is because you are groupping by this field

In a textbox outside the list I'd expect to see 23

|||

Adam - thank you so much for responding.

I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.

-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.

-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.

It is very very strange as it defies all logic for me!

|||

Can you elaborate further please. Can you please post the following:

the Group expression for your list control

the filter applied to the list

the textbox expression that displays the incorrect result

|||

Hi Adam,

(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value

(2) The filter applied to the list is the following:

=sum(Fields!CMCM.Value) <= =-3.0

(3) The textbox expression for the field that displays the incorrect result:

=CountDistinct(Fields!ACCT_UNIT.Value,"list4")

Thanks!

Maria

|||

Ok I figured something out...

If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.

|||Why not define it at both levels?|||

I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.

I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?

Problem with Group Totals and Counts

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

Hi Maria,

Nice to see a well articulated problem for a change

What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.

You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:

In a textbox inside the list I would expect to see 1. This is because you are groupping by this field

In a textbox outside the list I'd expect to see 23

|||

Adam - thank you so much for responding.

I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.

-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.

-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.

It is very very strange as it defies all logic for me!

|||

Can you elaborate further please. Can you please post the following:

the Group expression for your list control

the filter applied to the list

the textbox expression that displays the incorrect result

|||

Hi Adam,

(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value

(2) The filter applied to the list is the following:

=sum(Fields!CMCM.Value) <= =-3.0

(3) The textbox expression for the field that displays the incorrect result:

=CountDistinct(Fields!ACCT_UNIT.Value,"list4")

Thanks!

Maria

|||

Ok I figured something out...

If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.

|||Why not define it at both levels?|||

I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.

I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?

Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…

Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…

Problem with datetime expression

I have the following expression in a textbox in a table based on a dataset:
=IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
HH:mm:ss"))
The result I recieve when there is supposed to be a date is the date
mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
Any ideas?
Best regards,
Peter!On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
> I have the following expression in a textbox in a table based on a dataset:
> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
> HH:mm:ss"))
> The result I recieve when there is supposed to be a date is the date
> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
> Any ideas?
> Best regards,
> Peter!
I assume that Fields!Opened.Value is a string value (because you are
using it twice there) that either contains the text "No" or a Date.
Try wrapping the second Value in a CDate() function to force a
conversion to DateTime. If the Format command is fed a value that it
can't convert, it returns the formatting string, not the value.
= IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
Opened.Value), "yyyy-MM-dd HH:mm:ss") )
-- Scott|||Orne wrote:
> On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
>> I have the following expression in a textbox in a table based on a dataset:
>> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
>> HH:mm:ss"))
>> The result I recieve when there is supposed to be a date is the date
>> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
>> Any ideas?
>> Best regards,
>> Peter!
> I assume that Fields!Opened.Value is a string value (because you are
> using it twice there) that either contains the text "No" or a Date.
> Try wrapping the second Value in a CDate() function to force a
> conversion to DateTime. If the Format command is fed a value that it
> can't convert, it returns the formatting string, not the value.
> = IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
> Opened.Value), "yyyy-MM-dd HH:mm:ss") )
> -- Scott
Hi Scott!
Thanks for the tip, now the date works but I get the #Error on the When
the value contains "No".
/Peter|||On Dec 7, 11:05 am, Peter Larsson <scape...@.hotmail.com> wrote:
> Orne wrote:
> > On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
> >> I have the following expression in a textbox in a table based on a dataset:
> >> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
> >> HH:mm:ss"))
> >> The result I recieve when there is supposed to be a date is the date
> >> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
> >> Any ideas?
> >> Best regards,
> >> Peter!
> > I assume that Fields!Opened.Value is a string value (because you are
> > using it twice there) that either contains the text "No" or a Date.
> > Try wrapping the second Value in a CDate() function to force a
> > conversion to DateTime. If the Format command is fed a value that it
> > can't convert, it returns the formatting string, not the value.
> > = IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
> > Opened.Value), "yyyy-MM-dd HH:mm:ss") )
> > -- Scott
> Hi Scott!
> Thanks for the tip, now the date works but I get the #Error on the When
> the value contains "No".
> /Peter- Hide quoted text -
> - Show quoted text -
Ok, just made some test data. I think that the second half of the IIF
statement is still being evaluated, and for those rows where the value
is "No", the CDate is failing, therefore the whole IIF is failing.
So, before we do the CDate, we have to check again if the value is a
non-convertable date, then if it is not convertable, set it to
Nothing. CDate(Nothing) is still Nothing, so then the second half of
the IIF would succeed.
The following works the way I think you want it to:
=IIF( Fields!Opened.Value = "No", "No", Format( CDate( IIF(Fields!
Opened.Value = "No", Nothing, Fields!Opened.Value) ), "yyyy-MM-dd
HH:mm:ss" ) )
-- Scott

Problem with dates in Dataset Select statement

I'm using the designer to create/modify strongly typed datasets. I have one select statement that I'm having considerable trouble with. The user selects search parameters from a form. These search parameters are used to filter the data sent to a gridview control. Three of these parameters are almost enough to make me abandon the dataset in this case.

The first two are minimum and maximum age. I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated. In ASP I'd do something like this:

strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"

I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.

The third parameter is astrological sign. Again, I calculate it based on the birth date. I don't have a field for it. I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign. I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?

I need to get the age issue fixed. The sign is a nice to have, since I can do it another way if I have to.

BTW: I did decide to abandon the dataset in favor of a SqlDataSource control. This allowed me to build my select string in the code behind the way I would have in ASP. This resulted in paging and sorting not working properly. Sorting would be nice, paging is necessary. I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn). Any comments or feedback on this would be avidly read and appreciated.

Diane

You have to put the Datediff portion in the Filter column of the Query Designer. Then SQL will use it in the WHERE clause.|||

Please show us your code. That would be easier for us to figure out why it does not owrk for you.

Also, please take a look the following link, it shows how to use SqlDataSource with parameters.

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/sqldatasource.aspx

Here is an example from the above link:

<asp:DropDownList ID="DropDownList1" ... runat="server"/>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Pubs %>"
SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">
<SelectParameters>
<asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

Hope this helps

|||

Yes, I tried that. I get an error, so I must be doing something wrong. I tried

(@.lowAge < DATEDIFF(yyyy, [DATE OF BIRTH], date))

And I get an error: 'date' in expression not part of the query

Diane

|||

I can't use the SqlDataSource control this way. I have a huge select statement and many of the parameters can be empty. For example. users can select to search by state or not. So in my code behind I check if a state has been selected. If so, I add it to the select string. I have to check first, or I'll have a string that looks for records with no state in them, which is not what I want. So I build the select string in the code behind. If strSQL is the string I've built, I set SqlDataSource1.SelectCommand = strSQL. This works great for the first page, but the grid disappears if I try to do any paging. Google and forum searches turned up others with the same problem, but no answers. I suspect there's an answer for this, but that it's much more complicated than using the dataset which I'd rather use anyway.

I'd hate to say it, but at this point, faster is better. I'm at the end of a long project. My customer would like this done so he can move on to the next phase, and I'm ready for something new.

Diane

|||

I've narrowed down my problem.If I want to do this: (DATEDIFF(yyyy, [DATE OF BIRTH], Date) <= @.rUpperAge), how do I specify the current date in the dataset designer? It doesn't recognize Date, Date() Now, Now() or DateTime.now. I even tried to make today's date a parameter (DATEDIFF(yyyy, [DATE OF BIRTH], @.thisDate) <= @.rUpperAge). Didn't accept it. So how do I specify that I'm looking for a date difference between the field value and today?

Diane

|||

I never used the query designer. So I cannot give you any suggestion on that.

Regarding get the today's date, since it is used to build the query, so you shoudl use the sql function: GetDate().

|||

I second that

You should use getdate() to get the current date since you are generating sql query.

Now() is a VB keyword and would be used within VB code.

|||

Thanks everyone, GetDate() as part of the select string did it. I had tried to use it this way:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & GetDate() & "') <= " & Request.Form("UpperAge") & ")"

But this worked:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], GetDate()) <= " & Request.Form("UpperAge") & ")"

Diane

Problem With Dataset()

Hello all

i m facing a problem "There is already an open DataReader associated with this Command which must be closed first."

what is this alll the another page like thispage is currently working properly

so what is this problem

pleasse help me

ashwani

hi Friend,

do check your code you might be using same datareader without closing it before .

|||

Hi,

There is already an open DataReader associated with this Command which must be closed first.

From the error message you provided, it seems that you are using DataReader in your application, right?

Generally, a datareader represents an open connection to the database. If you want to run 2 readers simultaneously, you need to use two separate connections.

Thanks.

Problem with DataSet or SQL query

I've faced the following problem while forming DataSet. I'd like to ask the following SQL query:

___

select Users.UserID,Users.FirstName,Users.LastName, Users.Email, data.Suffix, data.Unit,data.Telephone, data.IM,

data.Website
from
(select UserID,
max(case PropertyName when 'Suffix' then PropertyValue end)Suffix,
max(case PropertyName when 'Unit' then PropertyValue end)Unit,
max(case PropertyName when 'Telephone' then PropertyValue end)Telephone,
max(case PropertyName when 'IM' then PropertyValue end)IM,
max(case PropertyName when 'Website' then PropertyValue end)Website
from
(
SELECT UserProfile.UserID, UserProfile.PropertyValue, UserProfile.PropertyDefinitionID,

ProfilePropertyDefinition.PropertyName FROM UserProfile,ProfilePropertyDefinition
WHERE UserProfile.PropertyDefinitionID=ProfilePropertyDefinition.PropertyDefinitionID
)table2
group by UserID)
data
inner join Users on Users.UserID = data.UserID
WHERE data.Suffix = 'IT';

__

Nothing happens while fulfilling as well (i.e. if to set up Breakpoint and watch the DataSet condition - it will be empty there). If to change the request to let's say some standart - then DataSet downloading begins - it seems there's something wrong with the request

I'm checking...If to look with the holp of request master and press "perform", data is reflected in a normal way...But as soon as i press "Bild", emptiness is shown as usual

Please, prompt me what to do

Initial code in the file .aspx.cs:

----------

UserInfo ds =new UserInfo();

UserInfoTableAdapters.UserInformationTableAdapter da =new UserInfoTableAdapters.UserInformationTableAdapter();

da.Fill(ds.UserInformation);

Info.DataSource = ds.UserInformation;

Info.DataBind();

Hello Alag,

Did the query gave the correct result in the SQL Query Analyzer?

If you are using an xsd dataset definition, execute the menu option Preview Data ... to see the xsd dataset works fine.

Jeroen Molenaar.

|||

Result on SQL Query Analyzer is correct.

AND RESULT CORRECT IN "Execute Query" in DataSet Ouery Builder.. But if press "Bild"..((( no correct

|||

Bild sounds like build, but what error message is the compiler reporting when "bildung".

Jeroen Molenaar.

|||

No error. Just nothing displays on the screen .. And as you see that empty DataSet.

Sorry for the poor English. I am from Russia, and the school taught him poorly.

|||

I thought "bild" was German. But your English is much, much better than my Russian!

Are you indeed using an xsd dataset?

Jeroen Molenaar.

PS: Ja nie gavarjoe paroeskie.

|||

I am using "Add New Item" -> "DataSet"

PS. You russian is good)

|||

In that case you are using an xsd dataset file.

When you open the UserInfo dataset and select the datatable UserInformation. Give a right click on the mouse and select the option: Preview data ... preview.

Does it give any result?

Jeroen Molenaar.

|||

Yes, the results show

|||

We are now moving to the asp page. Can you show more of the code you are using?

Jeroen Molenaar.

|||

Default.aspx.cs:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclass_Default : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

UserInfo ds =newUserInfo();UserInfoTableAdapters.UserInformationTableAdapter da =new UserInfoTableAdapters.UserInformationTableAdapter();

da.Fill(ds.UserInformation);

Info.DataSource = ds.UserInformation;

Info.DataBind();

}

}

|||

Default.aspx

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="background-color:#F7F7F7">
<form id="form1" runat="server">
<div>

<asp:DataList ID="Info" runat="server">
<ItemTemplate>
<table width="700" border="0" cellpadding="0" cellspacing="2" style="font-family:Tahoma; background-color:white;">
<tr>
<td style="width:130; background-color:#F7F7F7;"><asp:Image ID="Image1" runat="server" ImageUrl='' /></td>
<td valign="top" style="background-color:#F7F7F7"><table width="100%" border="0" cellpadding="0" cellspacing="0" style="background-color:#F7F7F7">
<tr>
<td style=" font-size: 13px;padding-left: 12px;text-decoration:underline;color:#003366;font-weight:bold; height:30px; background:background.png" >
<asp:Label ID="Label7" runat="server"><%# DataBinder.Eval(Container.DataItem, "FirstName")%></asp:Label>
<asp:Label ID="Label8" runat="server"><%# DataBinder.Eval(Container.DataItem, "LastName")%></asp:Label>
</td>
</tr>
<tr>
<td style="height:4px;"></td>
</tr>
...

<tr>
<td style="height:4px;"></td>
</tr>
</table></td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</div>
</form>
</body>
</html>

|||

Looks fine to me. Final piece of code: markup language of the default.asp.

Jeroen Molenaar.

PS: Whoah, that was fast!

|||

I don't understand ((.. what I want? The code above is default.aspx

|||

When I was typing my reply, you already added the markup code from default.aspx. My question to add the code was too late

The problem is propably in the definition of your DataList control on the page, but I don't know exactly. The Data Access/SQL server forum is not a perfect place for this problem.

You better ask your question in another forum about ASP, for example the getting started forum.

Good luck!

Jeroen Molenaar.

Problem with dataset

My data from this dataset works until I get to a column that has no value in it then it starts not showing the value in the three columns after the no value column. This is my code to handle the no value column.

if ((myDataset.Tables[0].Rows[i][j].ToString()) == "")

I'm not sure of the C# syntax, but try:

if ((myDataset.Tables[0].Rows[i][j] is DBNull)

|||

It ended up that because of a javascript issue it was entering a bunch of spaces in that column and therefore the column wasn't null or "" empty and I couldn't check the length cause of the spaces. So I replaced " " with "" (2 spaces with 0 spaces) then checked if length is less than 2. It worked perfect.