473,543 Members | 2,500 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored Procedures, DAL & Returning Values

Hello,

We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier Architecture.

The Stored Procedures are used through TableAdaptors, which in turn are
used by Class Files.

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

An Example Stored Procedure:
--------------------------------------------
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId=@@I DENTITY
END
GO
--------------------------------------------

An Example Portion of The Class (VB.NET 2.0):

--------------------------------------------
'*** Create New Supplier
Public Function CreateSupplier( _
ByVal Company As String, _
ByVal SupplierId As Integer) As Boolean

'*** Declare Variables
Dim Result As Boolean

'*** Insert Supplier
Result = Adapter.Insert( Company, SupplierId)

'*** Publish Result
Return Result

End Function
--------------------------------------------

As you can see from the class, this deals wwith actually enterting the
values into the stored procedure, which will then add it into the table
itself. How would I be able to represent the returning value in this
portion of the class?

I would like to be able to do this without having to do another call to
the database. I come from ASP 3.0 (Classic) background and am used to
using Stored Procedures with Command objects that will allow the
returning of values. I have as yet, been unable to simulate such
operations using ASP.NET 2.0 and am looking for help with this
situation.

Anyone have any ideas, solutions?
Sincerely,

SP

Nov 1 '06 #1
4 4342
Hello,
>
We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier Architecture.

The Stored Procedures are used through TableAdaptors, which in turn are
used by Class Files.

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

An Example Stored Procedure:
--------------------------------------------
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId=@@I DENTITY
END
GO
--------------------------------------------

An Example Portion of The Class (VB.NET 2.0):

--------------------------------------------
'*** Create New Supplier
Public Function CreateSupplier( _
ByVal Company As String, _
ByVal SupplierId As Integer) As Boolean

'*** Declare Variables
Dim Result As Boolean

'*** Insert Supplier
Result = Adapter.Insert( Company, SupplierId)

'*** Publish Result
Return Result

End Function
--------------------------------------------

As you can see from the class, this deals wwith actually enterting the
values into the stored procedure, which will then add it into the table
itself. How would I be able to represent the returning value in this
portion of the class?

I would like to be able to do this without having to do another call to
the database. I come from ASP 3.0 (Classic) background and am used to
using Stored Procedures with Command objects that will allow the
returning of values. I have as yet, been unable to simulate such
operations using ASP.NET 2.0 and am looking for help with this
situation.

Anyone have any ideas, solutions?

Sincerely,

SP
What is that Adapter you use?

To call a stored procedure and use the returned parameters, I use
something like:
- create a SqlCommand object
- set the CommandText property to the name of the stored procedure to
call and the CommandType to CommandType.Sto redProcedure)
- add parameters. Two in your case: @Company is an input param,
@SuppliedId is output (or in/out).
- set the Connection property
- open the connection
- Execute the command (ExecuteNonQuer y method on the command in your
case)
- close the connection (you might want to put the execute in a
"try"-block and the connection-close in the "finally")
- read the value from the @SupplierId parameter.

this works in 1.1 and I don't expect that to be changed (much) in 2.0

Hans Kesting
Nov 1 '06 #2
I created an example that should help. Note everything is coded in the code
behing, but logic should be separated from the presentation.

-- begin vb.net suxx code --

Partial Class _Default
Inherits System.Web.UI.P age

Private Const ConnectionStrin g As String =
"server=serverN ame;uid=UserNam e;password=Pass word;database=D atebaseName"

Public Class Supplier

Private _id As Integer
Private _company As String

Public Sub New()
End Sub

Public Property Id() As Integer
Get
Return _id
End Get
Set(ByVal value As Integer)
If (value < 0) Then
Throw New ArgumentOutOfRa ngeException()
End If
_id = value
End Set
End Property

Public Property Company() As String
Get
Return _company
End Get
Set(ByVal value As String)
_company = value
End Set
End Property

End Class

Private Sub InsertSupplier( ByVal supplier As Supplier)

Dim connection As New SqlConnection(C onnectionString )
Dim command As New SqlCommand("Ins ertSupplier", connection)

command.Paramet ers.Add("@Compa ny", SqlDbType.NVarC har).Value =
supplier.Compan y
command.Paramet ers.Add("@Suppl ierId", SqlDbType.Int). Direction =
ParameterDirect ion.Output
command.Command Type = CommandType.Sto redProcedure

Try

connection.Open ()
command.Execute NonQuery()

Catch ex As Exception
Throw ex
Finally
connection.Clos e()
End Try

supplier.Id = CType(command.P arameters("@Sup plierId").Value , Integer)

End Sub

Protected Sub InsertBUtton_Cl ick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles InsertBUtton.Cl ick

Dim supplier As New Supplier

supplier.Compan y = txtCompany.Text

InsertSupplier( supplier)
Response.Write( "inserted. id = " & supplier.Id.ToS tring())

End Sub
End Class
-- end code --

-- begin T-SQL code --

CREATE PROCEDURE [dbo].[InsertSupplier]
@Company nvarchar(1000),
@SupplierId int OUTPUT
AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId = SCOPE_IDENTITY( )
END
GO

-- end code

--
Milosz Skalecki
MCAD
"scparker" wrote:
Hello,

We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier Architecture.

The Stored Procedures are used through TableAdaptors, which in turn are
used by Class Files.

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

An Example Stored Procedure:
--------------------------------------------
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId=@@I DENTITY
END
GO
--------------------------------------------

An Example Portion of The Class (VB.NET 2.0):

--------------------------------------------
'*** Create New Supplier
Public Function CreateSupplier( _
ByVal Company As String, _
ByVal SupplierId As Integer) As Boolean

'*** Declare Variables
Dim Result As Boolean

'*** Insert Supplier
Result = Adapter.Insert( Company, SupplierId)

'*** Publish Result
Return Result

End Function
--------------------------------------------

As you can see from the class, this deals wwith actually enterting the
values into the stored procedure, which will then add it into the table
itself. How would I be able to represent the returning value in this
portion of the class?

I would like to be able to do this without having to do another call to
the database. I come from ASP 3.0 (Classic) background and am used to
using Stored Procedures with Command objects that will allow the
returning of values. I have as yet, been unable to simulate such
operations using ASP.NET 2.0 and am looking for help with this
situation.

Anyone have any ideas, solutions?
Sincerely,

SP

Nov 1 '06 #3
Hi,

scparker wrote:
I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?
FWIW - as an alternative to the regular approaches mentioned in this
thread, you could simply return your new ID within the resultset if your SP
has to return that new ID only, as in:

--- 8< ---
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SELECT @@IDENTITY
END
GO
--- 8< ---

Retrieving your new ID could then be handled using i.e.
intNewID=ctype( cmdSomeCommand. ExecuteScalar,i nteger)

Cheers,
Olaf
--
My .02: www.Resources.IntuiDev.com
Nov 1 '06 #4
Hello,

Thank you for your responses. It appears you all do follow the same
line of thinking.

To answer Hans Kesting;
The Adapter is an object created using Visual Studio 2005. It creates
the Data Access Layer (DAL). What it effectively does is bind the four
operations of Select, Insert, Update & Delete into One Object. This
results in the following code:

Adapter.Insert( Company,Supplie rId)
or
Adapter.Select( SupplierId)
etc
So, you create a stored procedure that is the definitive Select
statement you are going to use to communicate with that particular
table. Using VS2005 you use the graphical interface to select a
particular stored procedure to assume the Select command. The same goes
for Insert, Update and Delete. The name of these stored procedures
could be anything
(say: CREATE PROCEDURE WeWantToSelectA Supplier(@Suppl ierID as INT) AS
....)
and when you select that to be the defined Select Bind, instead of
calling Adpater.WeWantT oSelectASupplie r - you call Adapter.Select. I do
not know if 1.1 had this functionality as I have come from Classic ASP
to ASP.NET 2.0 - so I hope this makes sense to you.

To answer Milosz Skalecki;
This really continues with my answer to Hans Kesting. Because of this
use of the TableAdaptor, it appears we can negate the need for the use
of Command and Connection objects. We have learnt that what we are
looking to do is to return a scalar value from a dataset. The following
links may help to elaborate the issue we are having:
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
The above details very similar to the example you have provided and
then goes on to discuss the return value being used in the stored
procedure. It is this value we wish to extract using the TableAdaptor
objects.

http://blogs.gotdotnet.com/youngjoo/...25/678320.aspx
The link above continues the discussion by showing how to get the
return value by using the scalar extractor to bring the actual value
through. It is however, hard to follow and this is where we have
reached a stumbling block. Are you able to elaborate on what is going
on here, in the context of what we have been talking about?

To answer Olaf Rabbachin;
Thank you for the contribution with regard to the Stored Procedure
change. It makes little difference whether you use @@IDENTITY or
SCOPE_IDENTITY( ) to retrieve the value required. However, I will be
using the SCOPE_IDENTITY( ) in future, so thank you for your assistance
with this.

If any of you are able to help with the understanding of using a Scalar
value with TableAdaptors, you will be of great assistance.

Sincerely,

SP

Nov 2 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1968
by: Golawala, Moiz M (GE Infrastructure) | last post by:
Hi All, I am having problem returning values from a Stored Procedure that creates a dynamic table (table variable) inserts values during a procedure and then I select from that dynamic table to furnish values to python. This does not work MY PYTHON CODE IS: import adodbapi connStrSQLServer = r"Provider=SQLOLEDB.1; User ID=sa;...
2
3508
by: M Wells | last post by:
Hi All, I'm wondering if anyone can tell me if it's possible to search for stored procedures by their contents? I tend to leave very descriptive notes in stored procedures and they're beginning to build up in number, so I'm wondering if it's possible to search in Query Analyzer for stored procedures that contain certain words / phrases...
9
5475
by: Ralph Cramden | last post by:
I'm writing a VB6 app which calls several stored procedures on my SQL Server DB. The first stored procedure must complete its inserts before the second stored procedure can query the modified table for its results. My problem is that the second stored procedure occasionally returns a different result set, acting as if the first stored...
2
1722
by: Anthony Judd | last post by:
Hi all, Just a quick question. I am using Sql Server 2000 and calling stored procedures from asp. At this stage i am forgoing using the ADO command object and am simply create dynamic like sql statement for store procedure execution. My question is, I find the code below quite messy, particularly when it comes to checking for option...
6
26519
by: Terentius Neo | last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select statement? I mean something like this: Select c.number, call procedure( c.number ) as list from table c With best regards
45
3362
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
1
4147
by: mandible | last post by:
Hello I was wondering if I"m doing something wrong with using db2advis I have 2 stored procedures are are massive, I created an input file to call both of these stored procedures and I dont' seem to get any index help, but if I put the main body of these stored procs in a view and do a select * from that view I get a tonne of index help. ...
9
4128
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...
7
5834
by: eholz1 | last post by:
Hello PHP group, Could someone help me out? I have PHP 5.2, Apache 2.0, and MySQL 5.0 running on Linux (Redhat Fedora Core 6). All that works fine. I would like to be able to "call" a stored procedure from a PHP program, and run a stored procedure. I have yet to figure out the proper way to do this. My stored procedures work fine...
0
7399
marktang
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...
0
7584
Oralloy
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. ...
0
7729
jinu1996
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...
1
7332
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...
0
7677
tracyyun
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...
0
5880
agi2029
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...
0
3386
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1809
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
0
626
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.