Hi all,
I have the following problem:
I have a private method that returns a SqlDataReader. For this to work I
have not to close the DB connection in the above method. I do this only to
have the possibility to iterate through the entire rows set in a while loop,
located in the calling method.
I have included a few lines of code to get the number of rows fetched from
the DB. I do this with SqlParameter("R ETURN_VALUE", SqlDbType.Int) [ I am
using a stored procedure that return @@rowcount].
I have found out that I am getting the appropriate value for the stored
procedure parameter ("RETURN_VALUE" ) ONLY when I have explicitly close the
DB connection. Unfortunately when the control is returned to the calling
method the usual error message
Invalid attempt to Read when reader is closed
is being received as the connection is closed and there's no such DataReader
already.
Does anyone know a workaround for this?
Thanks,
Martin
------------------------
private SqlDataReader GetReader(strin g parameter, string date)
{
DateTime MyDateTime;
.....//. date parsing
SqlConnection myConn = new SqlConnection(C onnectionString ());
SqlCommand myCmd = new SqlCommand();
SqlDataReader myReader=null;
myCmd.CommandTy pe = CommandType.Sto redProcedure;
myCmd.Connectio n = myConn;
myCmd.CommandTe xt = "GetData";
myCmd.CommandTi meout = 250;
SqlParameter Param1 = new SqlParameter();
Param1 = myCmd.Parameter s.Add("@paramet er", SqlDbType.VarCh ar, 12);
Param1.Directio n = ParameterDirect ion.Input;
Param1.Value = parameter;
SqlParameter Param2 = new SqlParameter();
Param2 = myCmd.Parameter s.Add("@date", SqlDbType.VarCh ar, 20);
Param2.Directio n = ParameterDirect ion.Input;
Param2.Value = MyDateTime.Date .ToShortDateStr ing();
SqlParameter outValue = new SqlParameter();
outValue = myCmd.Parameter s.Add("RETURN_V ALUE", SqlDbType.Int);
outValue.Direct ion = ParameterDirect ion.ReturnValue ;
myConn.Open();
myReader = myCmd.ExecuteRe ader();
// this works only is myConn.Close() is executed but then
// we cannot return a READER to the calling method???
intRowsReturned =
Convert.ToInt32 (myCmd.Paramete rs["RETURN_VAL UE"].Value);
return myReader;
}
// the calling method
private void btnSQLGet_Click (object sender, System.EventArg s e)
{
/// ... .
// get the data in a reader
SqlDataReader myReader = GetReader(cboPa rameter.Text, txtDate.Text);
if (myReader==null )
return;
///.....
} 2 8086
You can't read output parameters in a DataReader until the DataReader is
closed. Close the DataReader, and leave the Connection opened to read the
value.
--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Martin Raychev" <ma*******@hotm ail.com> wrote in message
news:#R******** ******@TK2MSFTN GP09.phx.gbl... Hi all, I have the following problem: I have a private method that returns a SqlDataReader. For this to work I have not to close the DB connection in the above method. I do this only to have the possibility to iterate through the entire rows set in a while
loop, located in the calling method. I have included a few lines of code to get the number of rows fetched from the DB. I do this with SqlParameter("R ETURN_VALUE", SqlDbType.Int) [ I am using a stored procedure that return @@rowcount]. I have found out that I am getting the appropriate value for the stored procedure parameter ("RETURN_VALUE" ) ONLY when I have explicitly close the DB connection. Unfortunately when the control is returned to the calling method the usual error message Invalid attempt to Read when reader is closed is being received as the connection is closed and there's no such
DataReader already. Does anyone know a workaround for this? Thanks,
Martin ------------------------
private SqlDataReader GetReader(strin g parameter, string date)
{
DateTime MyDateTime;
.....//. date parsing
SqlConnection myConn = new SqlConnection(C onnectionString ()); SqlCommand myCmd = new SqlCommand();
SqlDataReader myReader=null; myCmd.CommandTy pe = CommandType.Sto redProcedure; myCmd.Connectio n = myConn; myCmd.CommandTe xt = "GetData"; myCmd.CommandTi meout = 250;
SqlParameter Param1 = new SqlParameter(); Param1 = myCmd.Parameter s.Add("@paramet er", SqlDbType.VarCh ar, 12); Param1.Directio n = ParameterDirect ion.Input; Param1.Value = parameter; SqlParameter Param2 = new SqlParameter(); Param2 = myCmd.Parameter s.Add("@date", SqlDbType.VarCh ar, 20); Param2.Directio n = ParameterDirect ion.Input; Param2.Value = MyDateTime.Date .ToShortDateStr ing();
SqlParameter outValue = new SqlParameter(); outValue = myCmd.Parameter s.Add("RETURN_V ALUE", SqlDbType.Int); outValue.Direct ion = ParameterDirect ion.ReturnValue ;
myConn.Open(); myReader = myCmd.ExecuteRe ader(); // this works only is myConn.Close() is executed but then
// we cannot return a READER to the calling method???
intRowsReturned = Convert.ToInt32 (myCmd.Paramete rs["RETURN_VAL UE"].Value); return myReader; } // the calling method
private void btnSQLGet_Click (object sender, System.EventArg s e)
{
/// ... .
// get the data in a reader
SqlDataReader myReader = GetReader(cboPa rameter.Text, txtDate.Text); if (myReader==null ) return; ///.....
}
1) the return value and parameters values of a stored proc are returned
after all result sets in the proc are returned.
2) a datareader is a forward only cursor, so to get to the return value, you
need to read thru all rows, and result sets
3) a close will read thru all rows and result sets for you, but is not
necessary, the following code will also work:
// process all result sets to get to parameters and reutn value
do
{
while (dr.Read())
;
} while (dr.NextResult( ))
// now you can access output parameters and the return value
returning a row count makes little sesnse, as you could add up the row
count yourself, as you have to read them all to get to the count return
value.
-- bruce (sqlwork.com)
"Martin Raychev" <ma*******@hotm ail.com> wrote in message
news:#R******** ******@TK2MSFTN GP09.phx.gbl... Hi all, I have the following problem: I have a private method that returns a SqlDataReader. For this to work I have not to close the DB connection in the above method. I do this only to have the possibility to iterate through the entire rows set in a while
loop, located in the calling method. I have included a few lines of code to get the number of rows fetched from the DB. I do this with SqlParameter("R ETURN_VALUE", SqlDbType.Int) [ I am using a stored procedure that return @@rowcount]. I have found out that I am getting the appropriate value for the stored procedure parameter ("RETURN_VALUE" ) ONLY when I have explicitly close the DB connection. Unfortunately when the control is returned to the calling method the usual error message Invalid attempt to Read when reader is closed is being received as the connection is closed and there's no such
DataReader already. Does anyone know a workaround for this? Thanks,
Martin ------------------------
private SqlDataReader GetReader(strin g parameter, string date)
{
DateTime MyDateTime;
.....//. date parsing
SqlConnection myConn = new SqlConnection(C onnectionString ()); SqlCommand myCmd = new SqlCommand();
SqlDataReader myReader=null; myCmd.CommandTy pe = CommandType.Sto redProcedure; myCmd.Connectio n = myConn; myCmd.CommandTe xt = "GetData"; myCmd.CommandTi meout = 250;
SqlParameter Param1 = new SqlParameter(); Param1 = myCmd.Parameter s.Add("@paramet er", SqlDbType.VarCh ar, 12); Param1.Directio n = ParameterDirect ion.Input; Param1.Value = parameter; SqlParameter Param2 = new SqlParameter(); Param2 = myCmd.Parameter s.Add("@date", SqlDbType.VarCh ar, 20); Param2.Directio n = ParameterDirect ion.Input; Param2.Value = MyDateTime.Date .ToShortDateStr ing();
SqlParameter outValue = new SqlParameter(); outValue = myCmd.Parameter s.Add("RETURN_V ALUE", SqlDbType.Int); outValue.Direct ion = ParameterDirect ion.ReturnValue ;
myConn.Open(); myReader = myCmd.ExecuteRe ader(); // this works only is myConn.Close() is executed but then
// we cannot return a READER to the calling method???
intRowsReturned = Convert.ToInt32 (myCmd.Paramete rs["RETURN_VAL UE"].Value); return myReader; } // the calling method
private void btnSQLGet_Click (object sender, System.EventArg s e)
{
/// ... .
// get the data in a reader
SqlDataReader myReader = GetReader(cboPa rameter.Text, txtDate.Text); if (myReader==null ) return; ///.....
} This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Raymond Hettinger |
last post by:
I would like to get everyone's thoughts on two new dictionary methods:
def count(self, value, qty=1):
try:
self += qty
except KeyError:
self = qty
def appendlist(self, key, *values):
try:
|
by: Yasutaka Ito |
last post by:
Hi,
My friend had a little confusion about the working of DataReader after
reading an article from MSDN. Following is a message from him...
<!-- Message starts -->
I was going thru DataReader in ADO.NET in MSDN and there is a confusion
regarding the buffering
of data in case of DATAREADER. The link for MSDN JAN 2004 is -
|
by: Steven D'Aprano |
last post by:
Suppose I create a class with some methods:
py> class C:
.... def spam(self, x):
.... print "spam " * x
.... def ham(self, x):
.... print "ham * %s" % x
....
py> C().spam(3)
spam spam spam
|
by: Johnny |
last post by:
I'm a rookie at C# and OO so please don't laugh! I have a form
(fclsTaxCalculator) that contains a text box (tboxZipCode) containing a zip
code. The user can enter a zip code in the text box and click a button to
determine whether the zip code is unique. If the zip code is not unique,
another form/dialog is displayed (fclsLookup) - lookup form/dialog. The zip
code and zipid are both passed to the lookup form/dialog by reference. I...
|
by: blue |
last post by:
We often get connection pooling errors saying that there are no available
connections in the pool.
I think the problem is that we are passing around open readers all over the
place. I am planning on changing this in our code and I expect this to fix
our problem.
We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we...
| |
by: Max |
last post by:
Anyone know why I'm always getting 0 returned? My stored procedure
returns -1.
Dim iErrorCode As Int32
iErrorCode = Convert.ToInt32(SqlHelper.ExecuteScalar(AppVars.strConn, _
"gpUpdateMember", _
Convert.ToInt32(lblMember_id.Text), _
-snip-
-Max
|
by: Miguel |
last post by:
Hi,
I'm new to .NET and am using VB .NET as I am from a VB background.
I am having difficulty understanding the way .NET handles, passes and uses
objects.
I have had a look at the Micrsoft Data Access Application blocks for .NET
and am struggling to understand the following:
1. When creating a function that returns a datareader, there is a datareader
instantiated inside the function. This datareader is then returned to the
|
by: ^MisterJingo^ |
last post by:
Hi all,
I have a form with 4 dropdownlist controls which I populate with data
from DB tables. I have a class with a method which constructs a
dataset, putting each DB table into a dataset table. I then return the
DS and bind the tables to the relevant controls.
I've been reading that DataReaders are much more efficient than
DS's for getting data from the database.
So would it better to have 4 methods, each returning a dataReader, or...
|
by: Greger |
last post by:
Hi,
I am building an architecture that passes my custom objects to and from
webservices. (Our internal architecture requires me to use webservices to any
suggestion to use other remoting techniques are not feasible)
The question is;
Given that I have a Person object with a private set for id.
What is the recommended approac in passing that object to the web service
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |