With classic ADO you could attach a ReturnValue parameter to a command containing inline parameters. I didn't add any OTHER parameter objects to do so, The entire command was in the CommandText. The example is to insert a single new row to table Notes which has an Identity field named Notes_ID. There is no problem inserting the row, but I want the new Note_ID value returned with the command.
Ex:
In SQL Server (2000 or 2005)
_______________ _______________ __
CREATE PROCEDURE [dbo].[Notes_Insert]
@Note text
AS
DECLARE @Note_ID int, @RETURN int, @ERROR int
SELECT @RETURN = 0, @ERROR = 0
INSERT INTO Notes
SELECT @Note
SELECT @Note_ID = @@IDENTITY, @ERROR = @@ERROR
IF @ERROR <> 0 BEGIN SET @RETURN = @ERROR END
ELSE BEGIN SET @RETURN = @Note_ID END
RETURN @RETURN
_______________ _______________ __
In VB6 using ADO and OLEDB for SQL Server the code was like this.
_______________ _______________ __
Dim lngNewIdentity as long
Dim Cmd as New ADODB.Command
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "Notes_Insert(' Reply to Bill on Friday')"
' No parameter object yet, and none can be successfully used at this
' point except for one of type ReturnValue like this.
Dim pRet as New ADODB.Parameter
pRET.Type = adInteger
pRET.Direction = adParamReturnVa lue
Cmd.Parameters. Append pRET
Cmd.ActiveConne ction = cnSem
Cmd.Execute
lngNewIdentity = pRet.Value
_______________ _______________ __
I have thousands of line of code written like this that work great. Now I am
using VB 2005 and the SqlClient.SqlCo mmand
_______________ _______________ __
Dim Cmd as New SqlCommand, lngNewIdentity as long
Cmd.Connection = cnSem
Cmd.CommandType = CommandType.Tex t
Cmd.CommandText = "Notes_Inse rt 'Reply to Bill on Friday' "
Dim pRET As New SqlParameter("R etValue", SqlDbType.Int)
pRET.Direction = ParameterDirect ion.ReturnValue
Cmd.Parameters. Add(pRET)
Cmd.ExecuteNonQ uery()
lngNewIdentity = pRet.Value
_______________ _______________ __
The parameter value always comes back 0. Note too that the command type was Text not StoredProcedure . That may be where the problem lies, but it won’t run as CommandType.Sto redProcedure. With hundreds of stored procedures to code to, some having 20 or 30 parameters, the in-line (as I call them) parameters save a ton of code.
So, is it no longer possible to do what I want, or have I just not explored enough options. I haven’t used the OleDB command object to date, and that might be the only reason I haven’t conquered this. So far I’ve been making a second trip with a scalar reader to get the @@IDENTITY created by the new record insert.
Any insight would be really appreciated.
Tom Garth
0 2229 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Silver Arrow |
last post by:
Iframe ReturnValue returns undefined if domains are not the same
I am using a modal window and an iFrame to try and pull a return value
back.
IMPORTANT : I am doing this across domains.
Main app: http://webSite1.domain.com/mainApplication
Other app call in ModalWindow:
http://webSite2.domain.com/mainApplication
I have the value returned from the modal window to the iFrame window
|
by: Henrik Dahl |
last post by:
Hello!
After I've finished using an instance of the SqlCommand class, should I then
invoke Dispose() on the instance. I suppose so, as there is a Dispose
method, but what does it actually release?
I would basically prefer to skip invoking Dispose() as this will free me
from determining when the usage actually has finished.
|
by: Dmitri Khanine |
last post by:
Hi All
Trying to retrieve the value of the Sqlcommand' parameter as integer.
Here is how I declare it:
SqlParameter paramId = command.Parameters.Add("@id", SqlDbType.Int);
paramId.Direction = ParameterDirection.InputOutput;
paramId.Value = id;
|
by: Tom |
last post by:
Hello guys
Please have a look on following paragraph from ".NET Data
Access Architecture Guide".
'''''''''''
Although you can repeatedly use the same SqlCommand object
to execute the same command multiple times, do not reuse
the same SqlCommand object to execute different commands.
1) You do not need to explicitly open or close the
database connection. The SqlDataAdapter Fill method opens
the database connection and then closes the...
|
by: Ja NE |
last post by:
(I'm not a programer, I have learned enough php to build my cms, but
that doesn't make me coding guru, so please excuse me if I'm asking
something trivail...)
well, I have one upload form where I want my users to choose category
for their picture. they are alowed to upload more then one picture at
time. and there is a problem. I have to use
<select name="name">
for my php script, so I have added id to the select tag
<select...
| |
by: Mr Not So Know It All |
last post by:
im new to SQL Server and ASP.Net. Here's my problem. I have this SQL
Server stored procedure with an input parameter and output parameter
CREATE PROCEDURE .
@in_rc varchar(8)
@out_eList varchar(7) output
AS
select @out_eList = ecuid from _organization where rccode = @in_rc
GO
|
by: Jason Huang |
last post by:
Hi,
In C#.Net, can we have more than one SqlCommand in a SqlConnection?
And in one SqlCommand, can we have more than one SqlDataReader in a
SqlCommand?
In what situation should I use SqlConneciton.Dispose() instead of
SqlConnection.Close()?
Thanks for help.
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table.
When calling the stored procedure from VB.NET, in the CommandText, can I
just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)...
|
by: karthikpl83 |
last post by:
Hi,
Can anyone tell me how to capture event.returnValue.
I have two actions written in my script which will be called during onbeforeunload event. Whenever onbeforeunload event is called, event.returnValue will be executed. This event.returnValue will hold a message with OK and CANCEL buttons. I want to submit a page based on the value returned from OK and CANCEL buttons.
Your help will be much appreciated.
Thanks,
|
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: 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: 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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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.
| |