I am trying to set up a class to handle my database accesses.
I can't seem to figure out how to get the return value from my dataReader
from these routines (most of which I got elsewhere). They do work pretty
well, except for the change I made to get the return value.
For example, I have the following:
*************** *************** *************** *************** **********
Public Overloads Function RunProcedure( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter( ), _
ByRef returnValue As Integer) _
As SqlDataReader
Dim returnReader As SqlDataReader
myConnection.Op en()
Dim command As SqlCommand = _
BuildIntCommand (storedProcName , parameters)
returnReader = command.Execute Reader( CommandBehavior .CloseConnectio n)
' Connection will be closed automatically
returnValue = CInt(command.Pa rameters("Retur nValue").Value)
Return returnReader
End Function
Private Function BuildIntCommand ( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter( )) _
As SqlCommand
Dim command As SqlCommand = _
BuildQueryComma nd(storedProcNa me, parameters)
Dim parameter As New SqlParameter()
With parameter
.ParameterName = "ReturnValu e"
.DbType = SqlDbType.Int
.Size = 4
.Direction = ParameterDirect ion.ReturnValue
.IsNullable = False
.Precision = 0
.Scale = 0
.SourceColumn = String.Empty
.SourceVersion = DataRowVersion. Default
.Value = Nothing
End With
command.Paramet ers.Add(paramet er)
Return command
End Function
Private Function BuildQueryComma nd( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter( )) _
As SqlCommand
Dim command As New SqlCommand(stor edProcName, myConnection)
command.Command Type = CommandType.Sto redProcedure
Dim parameter As SqlParameter
For Each parameter In parameters
command.Paramet ers.Add(paramet er)
Next
Return command
End Function
*************** *************** *************** *************** ****
The problem seems to be that I found out I either need to close the
connection or run NextResult() to get the return value. I would need to get
access to the Command object to get the return value (I think). Since I
would need to Read the DataReader before I close the connection - I have no
access to Command (since I am only passing back the DataReader to the
caller).
Is there anyway to get the return value from the DataReader itself?
Thanks,
Tom 3 2363
Sql return values are a lot like method/function return values. They aren't
available until the function has finished running. You need to close your
reader to access the return value or output parameters iirc... If you
really need to access the return value before closing the reader try
returning the return value as a seperate result set if possible...
HTH,
Ben
"tshad" <ts**********@f tsolutions.com> wrote in message
news:OX******** ******@TK2MSFTN GP09.phx.gbl... I am trying to set up a class to handle my database accesses.
I can't seem to figure out how to get the return value from my dataReader from these routines (most of which I got elsewhere). They do work pretty well, except for the change I made to get the return value.
For example, I have the following: *************** *************** *************** *************** ********** Public Overloads Function RunProcedure( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( ), _ ByRef returnValue As Integer) _ As SqlDataReader
Dim returnReader As SqlDataReader
myConnection.Op en() Dim command As SqlCommand = _ BuildIntCommand (storedProcName , parameters)
returnReader = command.Execute Reader( CommandBehavior .CloseConnectio n) ' Connection will be closed automatically returnValue = CInt(command.Pa rameters("Retur nValue").Value)
Return returnReader
End Function
Private Function BuildIntCommand ( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( )) _ As SqlCommand
Dim command As SqlCommand = _ BuildQueryComma nd(storedProcNa me, parameters) Dim parameter As New SqlParameter()
With parameter .ParameterName = "ReturnValu e" .DbType = SqlDbType.Int .Size = 4 .Direction = ParameterDirect ion.ReturnValue .IsNullable = False .Precision = 0 .Scale = 0 .SourceColumn = String.Empty .SourceVersion = DataRowVersion. Default .Value = Nothing End With command.Paramet ers.Add(paramet er)
Return command
End Function
Private Function BuildQueryComma nd( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( )) _ As SqlCommand
Dim command As New SqlCommand(stor edProcName, myConnection) command.Command Type = CommandType.Sto redProcedure
Dim parameter As SqlParameter For Each parameter In parameters command.Paramet ers.Add(paramet er) Next
Return command
End Function *************** *************** *************** *************** ****
The problem seems to be that I found out I either need to close the connection or run NextResult() to get the return value. I would need to get access to the Command object to get the return value (I think). Since I would need to Read the DataReader before I close the connection - I have no access to Command (since I am only passing back the DataReader to the caller).
Is there anyway to get the return value from the DataReader itself?
Thanks,
Tom
in the datastream (tds) the return value comes after all other result sets.
to access the return value, you need to read thru all rows and result sets
first (or close the reader - which will do this automatically).
-- bruce (sqlwork.com)
"tshad" <ts**********@f tsolutions.com> wrote in message
news:OX******** ******@TK2MSFTN GP09.phx.gbl... I am trying to set up a class to handle my database accesses.
I can't seem to figure out how to get the return value from my dataReader from these routines (most of which I got elsewhere). They do work pretty well, except for the change I made to get the return value.
For example, I have the following: *************** *************** *************** *************** ********** Public Overloads Function RunProcedure( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( ), _ ByRef returnValue As Integer) _ As SqlDataReader
Dim returnReader As SqlDataReader
myConnection.Op en() Dim command As SqlCommand = _ BuildIntCommand (storedProcName , parameters)
returnReader = command.Execute Reader( CommandBehavior .CloseConnectio n) ' Connection will be closed automatically returnValue = CInt(command.Pa rameters("Retur nValue").Value)
Return returnReader
End Function
Private Function BuildIntCommand ( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( )) _ As SqlCommand
Dim command As SqlCommand = _ BuildQueryComma nd(storedProcNa me, parameters) Dim parameter As New SqlParameter()
With parameter .ParameterName = "ReturnValu e" .DbType = SqlDbType.Int .Size = 4 .Direction = ParameterDirect ion.ReturnValue .IsNullable = False .Precision = 0 .Scale = 0 .SourceColumn = String.Empty .SourceVersion = DataRowVersion. Default .Value = Nothing End With command.Paramet ers.Add(paramet er)
Return command
End Function
Private Function BuildQueryComma nd( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( )) _ As SqlCommand
Dim command As New SqlCommand(stor edProcName, myConnection) command.Command Type = CommandType.Sto redProcedure
Dim parameter As SqlParameter For Each parameter In parameters command.Paramet ers.Add(paramet er) Next
Return command
End Function *************** *************** *************** *************** ****
The problem seems to be that I found out I either need to close the connection or run NextResult() to get the return value. I would need to get access to the Command object to get the return value (I think). Since I would need to Read the DataReader before I close the connection - I have no access to Command (since I am only passing back the DataReader to the caller).
Is there anyway to get the return value from the DataReader itself?
Thanks,
Tom
"Ben" <be*@online.nos pam> wrote in message
news:b9******** *************** ****@FUSE.NET.. . Sql return values are a lot like method/function return values. They aren't available until the function has finished running. You need to close your reader to access the return value or output parameters iirc... If you really need to access the return value before closing the reader try returning the return value as a seperate result set if possible...
So in the case of where you have something like
function GetReader("stor ed Procedure Name", parameters) as
SqlDataReader
There would be no way to get the return value, since I don't have the
command object (where you get the return value) and I obviously can't close
the SqlDataReader to get it in my function - because then the DataReader
would have nothing in it.
I don't really want to write my Stored procedure to accommodate the
DataReader. I want the return value to be a return value.
Thanks,
Tom HTH, Ben
"tshad" <ts**********@f tsolutions.com> wrote in message news:OX******** ******@TK2MSFTN GP09.phx.gbl...I am trying to set up a class to handle my database accesses.
I can't seem to figure out how to get the return value from my dataReader from these routines (most of which I got elsewhere). They do work pretty well, except for the change I made to get the return value.
For example, I have the following: *************** *************** *************** *************** ********** Public Overloads Function RunProcedure( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( ), _ ByRef returnValue As Integer) _ As SqlDataReader
Dim returnReader As SqlDataReader
myConnection.Op en() Dim command As SqlCommand = _ BuildIntCommand (storedProcName , parameters)
returnReader = command.Execute Reader( CommandBehavior .CloseConnectio n) ' Connection will be closed automatically returnValue = CInt(command.Pa rameters("Retur nValue").Value)
Return returnReader
End Function
Private Function BuildIntCommand ( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( )) _ As SqlCommand
Dim command As SqlCommand = _ BuildQueryComma nd(storedProcNa me, parameters) Dim parameter As New SqlParameter()
With parameter .ParameterName = "ReturnValu e" .DbType = SqlDbType.Int .Size = 4 .Direction = ParameterDirect ion.ReturnValue .IsNullable = False .Precision = 0 .Scale = 0 .SourceColumn = String.Empty .SourceVersion = DataRowVersion. Default .Value = Nothing End With command.Paramet ers.Add(paramet er)
Return command
End Function
Private Function BuildQueryComma nd( _ ByVal storedProcName As String, _ ByVal parameters As IDataParameter( )) _ As SqlCommand
Dim command As New SqlCommand(stor edProcName, myConnection) command.Command Type = CommandType.Sto redProcedure
Dim parameter As SqlParameter For Each parameter In parameters command.Paramet ers.Add(paramet er) Next
Return command
End Function *************** *************** *************** *************** ****
The problem seems to be that I found out I either need to close the connection or run NextResult() to get the return value. I would need to get access to the Command object to get the return value (I think). Since I would need to Read the DataReader before I close the connection - I have no access to Command (since I am only passing back the DataReader to the caller).
Is there anyway to get the return value from the DataReader itself?
Thanks,
Tom
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: ALI-R |
last post by:
Hi All,
First of all I think this is gonna be one of those threads :-) since I have
bunch of questions which make this very controversial:-0)
Ok,Let's see:
I was reading an article that When you pass a Value-Type to method call
,Boxing and Unboxing would happen,Consider the following snippet:
int a=1355;
myMethod(a);
......
|
by: Craig |
last post by:
I have some methods that open a database connection, get some data and then
return a datareader. How do I manage closing the connection to the database
then?
public OracleDataReader ExecuteCommand(string cmdStr) {
Connect();
OracleCommand cmd = new OracleCommand(cmdStr, this._conn);
OracleDataReader reader = cmd.ExecuteReader();
return reader;
}
|
by: Grant |
last post by:
I am connecting to an access database using a datareader in C#. I get
results when I run a certain query from Access but when I run it from Code
it does not retrieve any results. I have put a stop point after the string
is created and it is correct. Its an inner join query so I was wandering
whether that is too complicated for a datareader to execute, or if Im
missing something else here?
Heres what Im doing in code:
...
|
by: Scott Natwick |
last post by:
Hi,
Is there a way to obtain the return code from a stored procedure?
Here is what I have so far. The procedure executes, but I'm not able to
find the return code from the procedure.
Thanks,
Scott
|
by: D. Shane Fowlkes |
last post by:
This may be a very basic question but it's something I've never done before.
I've looked at a couple of my favorite sites and books and can't find an
answer either. I can write a Function to return a single value. No big
deal. But I want to call a Function from another Sub and the function finds
and returns an entire db record. Using ASP.NET (VB!), how can this be done
and how can I differentiate between the fields/columns?
For...
| |
by: orencs |
last post by:
Hi,
I am using Datareader and stored procedure in C# ADO.NET.
When I am running the stored procedure in the SQL Query Analyzer and
recieve two rows (as I hace expected)
col1 col2
0 1
0 2
4 2
|
by: Sam anonymous |
last post by:
This is what I have so far but if someone could show me how to read
through the datareader once I can get the rest.
Thank you.
Private Sub lstCoursesOffered_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lstCoursesOffered.SelectedIndexChanged
Dim intcount As Integer = 0
mblnLoading = True
|
by: D. Shane Fowlkes |
last post by:
Up until now, I've always had my functions return integers, strings, or
booleans. Now, I've (hopefully) written a function to return a 2 column,
single row datareader. Assuming I did this correctly (the function), how
could I look at the results of the function in page_load and get the values?
A little guidance would be great. Thanks once again!!
(using ASP/VB .NET 2 and VWD)
|
by: Rudy |
last post by:
Hello All!
I have a value in a textbox(txbTableIDm.Text ) that I would like to use in a
paremiter in a SP I wrote, and then have the select statement work off that
parememter, retireive a diffrent value in another(txbCredits) texbox. Heres
the code:
Public Sub GetCredit()
Dim ConCred As SqlConnection
Dim strCred As String
Dim cmdCred As SqlCommand
|
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: 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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |