Datasets

When using datasets in reports, is it possible to reuse them in other
reports? For example a generic dataset that returns all employee names to
use in parameters. Another report needs the same parameter and we need to
create another dataset. Is there some mechanism to share the datasets
across reports?
This is regarding Reporting Services on SQL 2000.
ThanksCreate a sproc with parms?
--
William Stacey [MVP]
"isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
| When using datasets in reports, is it possible to reuse them in other
| reports? For example a generic dataset that returns all employee names to
| use in parameters. Another report needs the same parameter and we need to
| create another dataset. Is there some mechanism to share the datasets
| across reports?
|
| This is regarding Reporting Services on SQL 2000.
|
| Thanks
|
||||Yes we do have a stored proc that has params. I was wondering about the
dataset within the report itself? Having two reports that share the same
proc (via the dataset) to get a lookup is one way (and possibly the only
way) to do it, but what about sharing the same dataset within the report
designer? I don't think MS SQL reporting services is set up to do this but
I'd like to be wrong to minimize the amount of coding I need to do for each
report.
As I'm getting up and running, I imagine one day we will be generating some
or all of our reports from code and generating the RDL on the fly. As this
question pertains to SQL Reporting 2000 we are a bit behind the times but it
would be nice to know for either version (SQL 2000 or SQL 2005) as we will
one day migrate to 2005.
Thanks
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OX5J5tqIGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Create a sproc with parms?
> --
> William Stacey [MVP]
> "isicoder" <isicoder@.newsgroups.nospam> wrote in message
> news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> | When using datasets in reports, is it possible to reuse them in other
> | reports? For example a generic dataset that returns all employee names
> to
> | use in parameters. Another report needs the same parameter and we need
> to
> | create another dataset. Is there some mechanism to share the datasets
> | across reports?
> |
> | This is regarding Reporting Services on SQL 2000.
> |
> | Thanks
> |
> |
>|||From what I've found, you can't reuse datasets, unfortunately. You're not
the first one to ask...
Stored procedures are the best way to go, but unfortunately you can't have a
list reusable datasets. That would have been great...
Kaisa M. Lindahl
"isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:%23M28JR5IGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Yes we do have a stored proc that has params. I was wondering about the
> dataset within the report itself? Having two reports that share the same
> proc (via the dataset) to get a lookup is one way (and possibly the only
> way) to do it, but what about sharing the same dataset within the report
> designer? I don't think MS SQL reporting services is set up to do this
> but I'd like to be wrong to minimize the amount of coding I need to do for
> each report.
> As I'm getting up and running, I imagine one day we will be generating
> some or all of our reports from code and generating the RDL on the fly.
> As this question pertains to SQL Reporting 2000 we are a bit behind the
> times but it would be nice to know for either version (SQL 2000 or SQL
> 2005) as we will one day migrate to 2005.
> Thanks
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OX5J5tqIGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> Create a sproc with parms?
>> --
>> William Stacey [MVP]
>> "isicoder" <isicoder@.newsgroups.nospam> wrote in message
>> news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
>> | When using datasets in reports, is it possible to reuse them in other
>> | reports? For example a generic dataset that returns all employee names
>> to
>> | use in parameters. Another report needs the same parameter and we need
>> to
>> | create another dataset. Is there some mechanism to share the datasets
>> | across reports?
>> |
>> | This is regarding Reporting Services on SQL 2000.
>> |
>> | Thanks
>> |
>> |
>>
>

Dataset′s

Im trying to make a dataset from a stored procedure. My problem is that RS wont use all of the rows returned.

My SP returns 9 rows. Some of them are dublicates and some ar on of a kind. And it seeems that RS just displays one of each kind. No mater how many there are i the resultset.

Ive read that RS cant handle more than one resultset, but i think that the result returned is just one resultset.

If a run the SP in the Data tab it works just fine, it displays all rows. But when im using it in previewmode its not.

The SP is built around a cursor. It makes a temporary table with info. Then just JOINs it with a real table. Would not that create a SINGLE resultset witch RS should read?

Thanks in advanceIf it is working in preview, you are doing something in the layout of your report. Maybe you are grouping by some repeating value which will cause for the number of rows to be reduced. Try to start with a blank slate and drop the fields in the detail row of a table.|||

Thanks, it workt. I started of with a blank report. You where right, it was somehing in the layout section that flawed.

Dataset with multiple tables is not getting populated

Hi,

I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.

I have seen the problem earlier and could not resolved it. Please let me know if any one can help.

Thanks in advance

Rohit

From your description, i think it really has no records.

because you can really see five tables without any error.

it means the SP does populate five tables and pass them to Dataset.

|||

Hi

Thanks for your reply.

There is data in the database and when I run the sproc directly through the execute command it returns me the results that I want i.e. all the 5 tables with the rows. However when I use the data adapter method using the fill dataset it just populates fhe first 3 tables and the next 2 are blank. Between the tables in the strongly typed dataset I have no relations set either.

Thanks in advance.

|||Try to useSqlDataAdapter to fill a DataSet, instead of using strongly typed dataset.sql

Dataset to XML

Hi
I have an SQL Server stored procedure wich returns ceratin data and I have
it in a Dataset. I am supposed to change that data into an specific standard
XML format and write it to file.
I am using Visual Studio 2005 wich is completely new to me.
Could you help with some hints about how can I do that.
I have been looking a lot about this from the Net but every thing I have
found is very simple: GetXml() But that is not enough for me. I need to
change that XML so that I get it in the previously defined standard format
Thanks for any help
GabrielHello Gabriel,
Likely the fastest way to deal with this would be the write an XSL/T that
transforms the dataset XML to the desired format. Otherwise, if you have
SQL Server 2005 and are allowed to replace the stored procedure, you could
write a FOR XML PATH query and probably get pretty close to what you're look
ing
for straight out of SQL Server.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks a lot Kent.
I am completely new in this field and I have to study a lot. Your message
give me good hints to to start with.
greetings from Finland
Gabriel
"Kent Tegels" wrote:

> Hello Gabriel,
> Likely the fastest way to deal with this would be the write an XSL/T that
> transforms the dataset XML to the desired format. Otherwise, if you have
> SQL Server 2005 and are allowed to replace the stored procedure, you could
> write a FOR XML PATH query and probably get pretty close to what you're lo
oking
> for straight out of SQL Server.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>sql

Dataset to XML

Hi
I have an SQL Server stored procedure wich returns ceratin data and I have
it in a Dataset. I am supposed to change that data into an specific standard
XML format and write it to file.
I am using Visual Studio 2005 wich is completely new to me.
Could you help with some hints about how can I do that.
I have been looking a lot about this from the Net but every thing I have
found is very simple: GetXml() But that is not enough for me. I need to
change that XML so that I get it in the previously defined standard format
Thanks for any help
Gabriel
Hello Gabriel,
Likely the fastest way to deal with this would be the write an XSL/T that
transforms the dataset XML to the desired format. Otherwise, if you have
SQL Server 2005 and are allowed to replace the stored procedure, you could
write a FOR XML PATH query and probably get pretty close to what you're looking
for straight out of SQL Server.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Thanks a lot Kent.
I am completely new in this field and I have to study a lot. Your message
give me good hints to to start with.
greetings from Finland
Gabriel
"Kent Tegels" wrote:

> Hello Gabriel,
> Likely the fastest way to deal with this would be the write an XSL/T that
> transforms the dataset XML to the desired format. Otherwise, if you have
> SQL Server 2005 and are allowed to replace the stored procedure, you could
> write a FOR XML PATH query and probably get pretty close to what you're looking
> for straight out of SQL Server.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>

Dataset Refresh...

Hi Folks,
I have a dataset in Reporting Services which points to an stored procedure.
This SP returns all data. Then the data is filtered in the dataset based on
the report parameters is choosen.
1. Will the dataset be refreshed every time i hit the View Report button or
will it get refreshed only the first time the view report is choosen and
would have the data cached till the session ends.
2. Is it better to do the filtering of data in the SP by passing parameters
from the report or is it better to use filters on a dataset.
Thank you
RamdasThe dataset gets refreshed each time you click on View Report with one
important note. In development the designer caches the data and only
refreshes if the parameter changes. If deployed to the server it refreshes
every time.
You are much better off to filter with the query or the SP. I pretty much
never use report filters. The very best thing to do for performance is have
the database return only those records you want.
In RS 2005 all rendering operations are done in RAM. If you have a large
resultset you will be using up a lot of your server's resources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> Hi Folks,
> I have a dataset in Reporting Services which points to an stored
> procedure.
> This SP returns all data. Then the data is filtered in the dataset based
> on
> the report parameters is choosen.
> 1. Will the dataset be refreshed every time i hit the View Report button
> or
> will it get refreshed only the first time the view report is choosen and
> would have the data cached till the session ends.
> 2. Is it better to do the filtering of data in the SP by passing
> parameters
> from the report or is it better to use filters on a dataset.
> Thank you
> Ramdas
>|||Hi Bruce,
Thank you for the insight.
One more question, is it possible to refresh the report without clicking the
View Report button.
Thank you
"Bruce L-C [MVP]" wrote:
> The dataset gets refreshed each time you click on View Report with one
> important note. In development the designer caches the data and only
> refreshes if the parameter changes. If deployed to the server it refreshes
> every time.
> You are much better off to filter with the query or the SP. I pretty much
> never use report filters. The very best thing to do for performance is have
> the database return only those records you want.
> In RS 2005 all rendering operations are done in RAM. If you have a large
> resultset you will be using up a lot of your server's resources.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> > Hi Folks,
> > I have a dataset in Reporting Services which points to an stored
> > procedure.
> > This SP returns all data. Then the data is filtered in the dataset based
> > on
> > the report parameters is choosen.
> >
> > 1. Will the dataset be refreshed every time i hit the View Report button
> > or
> > will it get refreshed only the first time the view report is choosen and
> > would have the data cached till the session ends.
> >
> > 2. Is it better to do the filtering of data in the SP by passing
> > parameters
> > from the report or is it better to use filters on a dataset.
> >
> > Thank you
> >
> > Ramdas
> >
>
>|||If you have a report that you want to refresh automatically then you can set
that in the dataset tab. Click on the ...
For instance, I have a report that refreshes every 5 minutes automatically.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:D205F7FF-A85D-4ACD-95EF-D68050FA3EFE@.microsoft.com...
> Hi Bruce,
> Thank you for the insight.
> One more question, is it possible to refresh the report without clicking
> the
> View Report button.
> Thank you
> "Bruce L-C [MVP]" wrote:
>> The dataset gets refreshed each time you click on View Report with one
>> important note. In development the designer caches the data and only
>> refreshes if the parameter changes. If deployed to the server it
>> refreshes
>> every time.
>> You are much better off to filter with the query or the SP. I pretty much
>> never use report filters. The very best thing to do for performance is
>> have
>> the database return only those records you want.
>> In RS 2005 all rendering operations are done in RAM. If you have a large
>> resultset you will be using up a lot of your server's resources.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ram" <Ram@.discussions.microsoft.com> wrote in message
>> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
>> > Hi Folks,
>> > I have a dataset in Reporting Services which points to an stored
>> > procedure.
>> > This SP returns all data. Then the data is filtered in the dataset
>> > based
>> > on
>> > the report parameters is choosen.
>> >
>> > 1. Will the dataset be refreshed every time i hit the View Report
>> > button
>> > or
>> > will it get refreshed only the first time the view report is choosen
>> > and
>> > would have the data cached till the session ends.
>> >
>> > 2. Is it better to do the filtering of data in the SP by passing
>> > parameters
>> > from the report or is it better to use filters on a dataset.
>> >
>> > Thank you
>> >
>> > Ramdas
>> >
>>

Dataset Question

I have a dataset which is calling a stored proc in my SSRS 2005 report. The stored proc just returns one value which is the result of a SUM in my stored proc's sql query.

When I reference that field from my dataset, SSRS puts SUM() around the fieldname. This is causing an issue because I have a complicated expression in my table footer and need to just reference that field by it's name without SUM.

Why is it putting SUM around my one field? If I take out the SUM around that field, SSRS can't recognize it as a valid dataset field anymore

Instead of using it from dataset option use Fields(Datasource) option in the expression|||I'm not quite sure I understand why you are putting Datasource as the field in parenthesis unless there's more behind that...which is not obious to me in your syntax example. Can you give me an example in full syntax?sql

dataset or datareader?

i need help to know what is the best practice

i have a stored proc which returns 4 different resultselts

will that be easy to use dataset or datareader?

my purpose of using dataset/datareader is to load the data in a class

thanks.

The DataReader is substantially faster than using a DataAdapter to fill a DataSet. Since you are just looking to fill other objects with the read data anyways, the DataReader would definitely be your best bet. Using the DataSet in this context would just be an extra (and unnecessary) step.

|||

Datareder is faster but it also work in connected envorionment. So be sure that if you are development n tier application and want to pass data between tiers and want to access them remotely then it would not work. Then you need to work with data sets. However in general Data Reader is faster.

|||

Datareder is faster but it also work in connected envorionment. So be sure that if you are development n tier application and want to pass data between tiers and want to access them remotely then it would not work. Then you need to work with data sets. However in general Data Reader is faster.

the above underline staement is not correct.,

you can work with datareader if you are developing in n tier application and currently i have n tier env and passing the datareader from daab to web app and i'm closing my datareader in the web_app.

|||

Datareder is faster but it also work in connected envorionment. So be sure that if you are development n tier application and want to pass data between tiers and want to access them remotely then it would not work. Then you need to work with data sets. However in general Data Reader is faster.

the above underline statement is not correct.,

you can work with data-reader if you are developing in n tier application and currently i have n tier env and passing the data-reader from daab to web app and i'm closing my data-reader in the web_app.

|||

nisarkhan:

you can work with data-reader if you are developing in n tier application

Nisarkhhan is correct -- there is no problem doing this. I do it too. All you do is use a DataReader to build some type of collection and pass the collection around. Or you can make the collection a property of a class and pass the class around. I too do this all the time.

dataset object dispose after first use

I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?

Thanks!

Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.DfltValData)
For Each row In dt.Rows
.

.

.// read value from row

.HAHAHAHA!! Not to laugh at your problem, but search a bit and you'll find a VERY recent discussion on this very topic.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1406350&SiteID=1|||

Use a MULTICAST if you need to work with same rows again.

There is a code work-around (shown in the forum post Phil referenced), but it's ugly.

|||I don't understand how multicast would work if we have multiple dataflow.|||

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

|||

jwelch wrote:

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

Exactly. Or simply use a multicast to populate 2 variables in the first place with exactly the same data.

-Jamie

dataset linked to stored procedure return no data

I created a new dataset for a new report that gets data from a stored
procedure. But when I run the dataset in the Data tab, it only returns the
column names with no data. The stored procedure runs fine in the Query
Analyzer with several returned records. Any help will be appreciated!On Mar 7, 4:14 pm, obnddc <obn...@.discussions.microsoft.com> wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!
Have you verified that the command type of the dataset is set to
stored procedure (instead of text)?
Enrique Martinez
Sr. SQL Server Developer|||Since it is returning all the columns it means it has accessed stored proc,
Just check the datasource using "test connection" if possible recreate the
datasource, more over do a "Refresh". Check for the server you connected
using Query Analyzer and the datasource are same.
Amarnath
"obnddc" wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!|||Thanks for the input, Emartinez and Amarnath!
I found out that the problem lay in the data itself. I have an input
parameter used in the where condition, like "WHERE tbl_name.customerID LIKE
@.custID"
The test data I used has several spaces after cutomerID charactors. It is
interesting to see the LIKE statement will ignore the spaces in Query
Analyzer but fail in SQL reporting services.
I tried to LTRIM and RTRIM customerID, or use @.custID+'%' but none worked.
Anyone here can help? Thanks.|||Finally I found out, it's not the spaces but the parameters.
I have begin date and end date as input parameters, and end date is
optional. When it is null, getdate(). In Query Analyzer, I left it empty, it
returned records. In SQL report, I have to enter an end date and the date I
entered happens to be the only date that has records. So no record met the
dates, no record reurned.
What I learned:
Before you conclude same process ran differently in different envirements,
make sure they ran under EXACTLY same conditions!
Thanks to all!

DataSet and Insert method

hi,

i created a query to insert a row in DataSet in Visual Studio 2005. i gave the method name to the query i created. as i understood it returns '1' if successful or '0' if not.

is it possible to get the ID or the row instead?

what did it say,or whats the eror code if there is?|||

there is no error. the point is i would like to get the id of the row that i insert instead of default int value.

|||Hi,

are you using a SQL 2k5? There is a new ouput clause in the syntax, where you can get values back. Look in the BOL for more information about that, or raise a hand if you need further assistance.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

i am using SQLExpress...

sorry should have mentioned earlier.

|||

Hi,

ok then go the OUTPUT way (described in the BOL)

INSERT INTO Sometable (Columnlisthere....)
OUTPUT INSERTED.*
VALUES ...Values here...

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens

DataSet - Should be easy but I dont know how

I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.

This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how? Any help would be awesome.

Sub Button1_Click(sender As Object, e As EventArgs)
?? = MyQueryMethod(txtPhone.Text)

Thanks,
Matt

Dim oDataSet as System.Data.DataSet = MyQueryMethod(txtPhone.Text)
If oDataSet = Nothing Then
 ' your dataset is jacked up
Else
 ' You got a dataset to work with
End If
' Release memory allocations to the variable.
oDataSet.Dispose()

|||Simply amazing! It's so easy once someone tells you how it is. Worked like a charm...Now I'm all jacked up!!!

Much Respect!!!!

Thanks,
Matt|||Well actually, it didn't quite do as I expected but I'm close. When I execute this code, I am expecting the dataset to have no records or one record, but either way the DataSet is never nothing (at least from my testing).

Maybe I'm just going about my problem the wrong way. I am trying to validate phone number so that I don't have someone enter a duplicate key in my database. So I run an SQL select statement that returns a dataset. The dataset should have one phone number or no phone number and this is how I'm trying to ward off any duplicate keys.

Is there an easier way to prevent a duplicate key entry? Sorry, I'm new at this stuff!!|||You can code as

If oDataSet.Tables(0).Rows.Count = 0 Then
 ' your have no records
Else
 'Response.Write(oDataSet.Tables(0).Rows(0)(0).ToString())
End If

HTH|||Perfect...thanks.

DataRow syntax

command.CommandText = "SELECT UserName from Users WHERE UserID = " = userID

Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can't get it out. Should I be using a dataSet for this operation?

The rest of the code so far:

SqlDataAdapter dataAdapter =newSqlDataAdapter();

dataAdapter.SelectCommand = command;

dataAdapter.TableMappings.Add("Table","Users");

dataSet =newDataSet();

dataAdapter.Fill(dataSet);

Using that code, your data would be in dataSet.tables[0].rows[0][0].

If that's always just returning one value, you might want to look into using ExecuteScalar instead of the adapter and dataset.

|||

You can just use executeScalar method of SQl command below is example from VB.Net help for scalar modified a little:

Public Function AddProductCategory( _ ByVal UserID As Integer, ByVal connString As String) As Integer Dim Username As string = "" Dim sql As String = "SELECT UserName from Users WHERE UserID = @.USERID" Using conn As New SqlConnection(connString) Dim cmd As New SqlCommand(sql, conn) cmd.Parameters.Add("@.USERID", SqlDbType.Int) cmd.Parameters("@.USERID").Value = newName Try conn.Open() userName = Convert.ToInt32(cmd.ExecuteScalar()) Catch ex As Exception Console.WriteLine(ex.Message) End Try End Using Return newProdIDEnd Function

Thanks

|||

Got it working ... thank you for your help!

DataReader returns different results when there are null fields

If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked.
The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.

// This code is called 10 times with a select * from where item="xxx"
P21Conn.Open();

SqlDataReader myReader = cmd.ExecuteReader();

if (myReader.Read()) {

thanks
Rodin your select or sproc, use ISNULL or COALESCE around the columns that have null values.

cs|||Thank you

I did that and now it works perferct

Rod

Datareader does not return result

the following is the code,...When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...

Connection is open and the query is simple select statement

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

Thanks

Niranch

niranch,

can you show me exact syntax of your query string within the context of how you are using it in the c# code? You may have some quotes being escaped incorrectly or something such that when run from the app code, it does not return any results and therefore you are getting your error. so, make sure that your query string is written correctly, particularly how you have used single or double quotes...--jp

|||

hi jp,

thank you for your suggestion...I just figured out the problem...

It is just that, when I run the same set of statements in my code behind file, I get the result, but when I run it in a seperated .CS file(DAL), I get the error mentioned in my post...I am trying to return datareader object to the Business tier...

I dont know why...

Thanks

Niranch

|||ahh so you are having trouble with the data moving through tiers. can you show some code of how your are interacting between the tiers?|||

hi this is my code...the same set to commands ,connections works fine in codebehind file

codebehid file

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database)

class where i have database access

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader);

|||

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader); <-- at this point is your reader null? if you put a throw statement here like if(myReader == null) throw new Exception("Null Reader") will you get an exception? is the reader already null here, or are you getting results here and just not when you set

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database) you get the null reader here? that make sense? at what point are you not getting any results? -- jp

|||

I get the problem, Exactly after executing the command

DAL Page

myreader = mycommand.executereader(), i get a null reader. "Invalid read attempt when there is no data" is the error...only in this page, i have my connection open...

i dont know if it is ok to send a datareader object as return object to another page............

|||

thankyou, i got the problem solved,,,

I transfered the contents from reader to datatable and passed as return objec to presentation tier...It worked fine.............