Detailed Question About Retrieving Field Determined By Identity Option

[peggasus88]

What I Would Like:
From a Customer Menu page, a user clicks on a "Place New Order" button and is taken to an Order Form page. There are 3 elements on the Order Form page:
1. A grid (grdProducts), displaying product information such as product name, description, price, etc.
2. A form, consisting of a dropdown box to select the product desired, a textbox to input the quanitity desired, and an "Add Item" button.
3. A grid (grdOrderDetails), displaying order details such as product name, price, quantity, subtotal, etc.

Example of Use:
When a user first enters the Order Form page, the grdOrderDetails is hidden (since there are no order details when he first enters). He makes his selection and clicks the "Add Item" button. He is redirected to the Order Form page, where the grdOrderDetails is now shown, detailing what he just entered. He can choose to add another item.

----------------------------
Relevant Tables in Database:
@Orders@ <- Table Name
-OrderID <- Primary Key
CustomerID
OrderDate

@OrderDetails@
-OrderID
-ProductName
UnitPrice
Quantity

My Problem:
For the OrderID field in the Orders table, I clicked on &quot;Identity&quot; in the table design window (where the coluumn name, datatype, etc. of the fields are specified). Choosing this option automatically increments the OrderID field in the database every time a new record is added. I need to have this OrderID value before the user adds an item, because the same OrderID is used in the OrderDetails table (There is one order record, but there can be many corresponding order details records.) I'm thinking about doing it this way:

PAGE 1: Customer Menu. User clicks on &quot;Place New Order&quot; button.
PAGE 2: Write CustomerID and OrderDate into Orders table
-> OrderID created.
PAGE 3: Retrieve OrderID that was created in PAGE 2 & keep in a session variable.
PAGE 4: Order Form.
PAGE 5: Process Order Form by writing OrderID (in a session variable), ProductName, UnitPrice, and Quantity into OrderDetails table. Redirect to Order Form/PAGE 4.

I have been thinking about this for a long time, and I am really stuck. I was hoping this might be a common operation that many of you have come across. Is there a better way to do this? I really appreciate any help you can give me.

Peggy

P.S. This is my first post. I am a complete newbie to InterDev. I just want to say that this IS the best technical website. I just wandered into it! The programmers and users have incredible technical knowledge (and patience). =)

 

[MerlinB]

If you perform the Add option using a stored Procedure, then it can return the value to your program.

The stored procedure could be something like

spAddItem (@some_parameters)
as
BEGIN
INSERT INTO xxx (cols,...)
VALUES (@param1, @param2)

RETURN @@Identity
END

Now you can execute this using classic ASP, or you can add a reference to this via the DataEnvironment:
. right-click the global.asa and add a connection
. against this connection, add a command
. select stored procedures, and pick your spAddItem
. name it 'spAddItem' as well!

it will ask about executing it - thats fine.

Make sure your asp page has a PageObjectDTC control.

In your page code, when you need to add a row type:

thisPage.createDE

orderID = DE.spAddItem txtOrderText.value, etc...

You should try to avaoid session variables where possible - store the value as a hidden form value (try using the pageObjectDTC, which makes this easy with its get/set values collection).

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[peggasus88]

Wow. Using the Data Environment simplifies coding quite a bit; in another page, I wrote many lines of ADO code to execute a stored procedure and now it's just 2 lines!
Thank you so much for your suggestions, MerlinB. They are really helping me out. I am working on learning the PageObjectDTC now. I think I understand what a Session and Application Lifetime is, but can you explain what a Page Lifetime is?

 

[MerlinB]

The page lifetime simply means that the value is held in a(dynamically created) hidden form field. Once you navigate to another page in your web, those values disappear - unless you forward them to the next page thru query string values or whatever.

Using the PageObject to dynamically create hidden fields is somewhat easier than manually adding hidden fields. If you allow Client-Side read/write, then the hidden field also has two small JavaScript functions created - a get and a set function.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[computergeek]

Hello,

I am having a similar problem. I tried the suggestion above to execute a procedure to do the insert, returning the identity column value. I am having 2 problems: 1.) The insert works fine but the Identity value does not seem to get returned?

Interdev Code:
Set DE = Server.CreateObject(&quot;DERuntime.DERuntime&quot
DE.Init(Application(&quot;DE&quot)
' Get data column values
strname = txtcompanyname.value etc....
' Insert company record
IntCurrCompany = DE.cmdAddCompany (strname,strtype,stradd1,stradd2,strcity,strstate,strcountry,strpostalcode,strcontact,strphone,strextension,strfax,stremail)

Procedure Code:

CREATE PROC cp_add_company
@company_name varchar(50),
@company_type varchar(1),
@invoice_address_line1 varchar(50),
@invoice_address_line2 varchar(50),
@invoice_city varchar(30),
@invoice_state varchar(30),
@invoice_country varchar(30),
@invoice_postal_code varchar(30),
@contact_name varchar(50),
@phone varchar(30),
@extension varchar(10),
@fax varchar(30),
@email varchar(30)
AS

Begin
Insert into Company(company_name,company_type,etc...)
values (@company_name,@company_type etc... )
return @@Identity
End

2.) Once I do get the identity column value back I would like to find this record within my existing recordset which is a DTC created recordset (rsCompanies), get the index, then move to that record...

Help, please.... : o (

 

[peggasus88]

Hi computergeek,

I haven't tried using the DE to execute a stored procedure that returns a value (only to execute a stored procedure that doesn't return a value). But I can paste my stored procedure code and ADO code that successfully executes a stored procedure that returns a value:

* Stored Procedure *

Alter PROCEDURE spPostOrder
@CustomerID int,
@OrderDate datetime,
@OrderID int Output
AS
INSERT INTO Orders
(
CustomerID,
OrderDate
)
VALUES
(
@CustomerID,
@OrderDate
)
SET @OrderID = @@IDENTITY

RETURN @OrderID

---------------------------------
* ADO *

Dim intCustomerID
intCustomerID = Session(&quot;UserID&quot

Dim dateOrderDate
dateOrderDate = Date

Dim adoCmd
Set adoCmd = Server.CreateObject(&quot;ADODB.Command&quot

'== Set up Command object
With adoCmd
.CommandText = &quot;spPostOrder&quot;
.CommandType = adCmdStoredProc

'== Define INPUT parameters
.Parameters.Append .CreateParameter (&quot;@CustomerID&quot;, adInteger, adParamInput, 8, intCustomerID)
.Parameters.Append .CreateParameter (&quot;@OrderDate&quot;, adDBTimeStamp, adParamInput, 10, dateOrderDate)

'== Define OUTPUT parameters
.Parameters.Append .CreateParameter (&quot;@OrderID&quot;, adInteger, adParamOutput, 6)

'== Open the Connection
adoConn.Open strConnectionString

'== Associate the Command with the Open Connection
Set .ActiveConnection = adoConn
.Execute
End With

Session(&quot;OrderID&quot = adoCmd.Parameters(&quot;@OrderID&quot

--------------------------------------------------
I hope that helps. Someone online helped me.
When I get the chance, I'm going to try using the DE to execute a stored procedure that returns a value, so let me know how you got it to work! =)

Peggy

 

[MerlinB]

ComputerGeek!
I am not quite sure why the command did not return the row id value. I have just tried a near identical test and it worked just fine.

However, if you look at the properties for your command in the Data Environment, you can see the list of expected parameters. The first parameter is called RETURN_VALUE, which is listed before all of your stored procedure parameters. (make sure the

You can execute a command, then inspect the parameter collection against the command to retrieve any return values - which is how you handle stored procedure parameters marked OUTPUT.

The code
Set DE = Server.CreateObject(&quot;DERuntime.DERuntime&quot
DE.Init(Application(&quot;DE&quot)

appears to be identical to the
thisPage.createDE

except that in the first case VI can then determine what the DE variable means - and so it pops up the method and property lists.

Peggy
The Data Environment is most peculiar in that when you add a 'command' to it, it magically creates a method of the same name. Unfortunately, VI does not really know this, and so when you create a DE object (as above), VI will not list your commands in the pop-up lists - just the standard built in ones (or none at all if you use thisPage.createDE).

What the DE does is to query the database at design-time for the parameter details, and cache this information in the file _Private/DataEnvironment/DataEnvironment.asa. Because the DE is a compiled object (DLL), it should run slightly faster than manually creating parameter lists in ASP code - and its much easier too!

Further - this area is virtually undocumented, so you have to do some guesswork and use your classic VB knowledge.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)