Trouble with DrDev Tutorials

[drewangell]

I've been having a heck of a time trying to figure this out. Using the DrDev
tutorials ([url]http://www.drdev.net/article01.htm[/url]) I've gotten my script to insert
into 2 different tables at once like this:

<%
'CREATE VARIABLES TO HOLD INPUT DATA FOR BILLING INFORMATION
if(Request("ContactFirstName") <> "") then insertBilling__varContactFirstName
= Request("ContactFirstName")
if(Request("ContactLastName") <> "") then insertBilling__varContactLastName =
Request("ContactLastName")
if(Request("CompanyName") <> "") then insertBilling__varCompanyName =
Request("CompanyName")
if(Request("BillingAddress") <> "") then insertBilling__varBillingAddress =
Request("BillingAddress")
if(Request("BillingAddress2") <> "") then insertBilling__varBillingAddress2 =
Request("BillingAddress2")
if(Request("City") <> "") then insertBilling__varCity = Request("City")
if(Request("StateOrProvince") <> "") then insertBilling__varStateOrProvince =
Request("StateOrProvince")
if(Request("PostalCode") <> "") then insertBilling__varPostalCode =
Request("PostalCode")
if(Request("CountryOrRegion") <> "") then insertBilling__varCountryOrRegion =
Request("CountryOrRegion")
if(Request("PhoneNumber") <> "") then insertBilling__varPhoneNumber =
Request("PhoneNumber")
if(Request("FaxNumber") <> "") then insertBilling__varFaxNumber =
Request("FaxNumber")
if(Request("EmailAddress") <> "") then insertBilling__varEmailAddress =
Request("EmailAddress")
if(Request("Notes") <> "") then insertBilling__varNotes = Request("Notes")
'END OF BILLING INFORMATION VARIABLES
%>

<%
' BEGIN CREATING SHIPPING INFORMAITON VARIABLES
if(Request("ShipFirstName") <> "") then shippingInformation__varShipFirstName
= Request("ShipFirstName")
if(Request("ShipLastName") <> "") then shippingInformation__varShipLastName =
Request("ShipLastName")
if(Request("ShipCompany") <> "") then shippingInformation__varShipCompany =
Request("ShipCompany")
if(Request("ShipAddress") <> "") then shippingInformation__varShipAddress =
Request("ShipAddress")
if(Request("ShipAddress2") <> "") then shippingInformation__varShipAddress2 =
Request("ShipAddress2")
if(Request("ShipCity") <> "") then shippingInformation__varShipCity =
Request("ShipCity")
if(Request("ShipStateOrProvince") <> "") then
shippingInformation__varShipStateOrProvince = Request("ShipStateOrProvince")
if(Request("ShipPostalCode") <> "") then
shippingInformation__varShipPostalCode = Request("ShipPostalCode")
if(Request("ShipCountry") <> "") then shippingInformation__varShipCountry =
Request("ShipCountry")
if(Request("ShipPhoneNumber") <> "") then
shippingInformation__varShipPhoneNumber = Request("ShipPhoneNumber")
if(Request("ShipFaxNumber") <> "") then shippingInformation__varShipFaxNumber
= Request("ShipFaxNumber")
if(Request("ShipEmailAddress") <> "") then
shippingInformation__varShipEmailAddress = Request("ShipEmailAddress")
if(Request("OrderNotes") <> "") then shippingInformation__varOrderNotes =
Request("OrderNotes")
' END CREATION OF SHIPPING INFORMAITON VARIABLES

%>
<% If Request("Submit") <> "" Then ' Only run the commands code when the
submit button is clicked %>

<%
' COMMAND CODE FOR INSERTING BILLING INFORMATION INTO DATABASE (CUSTOMERS
TABLE)
set insertBilling = Server.CreateObject("ADODB.Command")
insertBilling.ActiveConnection = MM_connKennysTile_STRING
insertBilling.CommandText = "INSERT INTO Customers (ContactFirstName,
ContactLastName, CompanyName, BillingAddress, BillingAddress2, City,
StateOrProvince, PostalCode, CountryOrRegion, PhoneNumber, FaxNumber,
EmailAddress, Notes) VALUES ('" + Replace(insertBilling__varContactFirstName,
"'", "''") + "', '" + Replace(insertBilling__varContactLastName, "'", "''") +
"', '" + Replace(insertBilling__varCompanyName, "'", "''") + "', '" +
Replace(insertBilling__varBillingAddress, "'", "''") + "', '" +
Replace(insertBilling__varBillingAddress2, "'", "''") + "', '" +
Replace(insertBilling__varCity, "'", "''") + "', '" +
Replace(insertBilling__varStateOrProvince, "'", "''") + "', '" +
Replace(insertBilling__varPostalCode, "'", "''") + "', '" +
Replace(insertBilling__varCountryOrRegion, "'", "''") + "', '" +
Replace(insertBilling__varPhoneNumber, "'", "''") + "', '" +
Replace(insertBilling__varFaxNumber, "'", "''") + "', '" +
Replace(insertBilling__varEmailAddress, "'", "''") + "', '" +
Replace(insertBilling__varNotes, "'", "''") + "') "
insertBilling.CommandType = 1
insertBilling.CommandTimeout = 0
insertBilling.Prepared = true
insertBilling.Execute()
' END COMMAND CODE FOR INSERTING BILLING INFORMAITON INTO DATABASE
%>

<%

' COMMAND CODE FOR INSERTING SHIPPING INFORMATION INTO DATABASE (ORDERS TABLE)
set shippingInformation = Server.CreateObject("ADODB.Command")
shippingInformation.ActiveConnection = MM_connKennysTile_STRING
shippingInformation.CommandText = "INSERT INTO Orders (ShipFirstName,
ShipLastName, ShipCompany, ShipAddress, ShipAddress2, ShipCity,
ShipStateOrProvince, ShipPostalCode, ShipCountry, ShipPhoneNumber,
ShipFaxNumber, ShipEmailAddress, OrderNotes) VALUES ('" +
Replace(shippingInformation__varShipFirstName, "'", "''") + "', '" +
Replace(shippingInformation__varShipLastName, "'", "''") + "', '" +
Replace(shippingInformation__varShipCompany, "'", "''") + "', '" +
Replace(shippingInformation__varShipAddress, "'", "''") + "', '" +
Replace(shippingInformation__varShipAddress2, "'", "''") + "', '" +
Replace(shippingInformation__varShipCity, "'", "''") + "', '" +
Replace(shippingInformation__varShipStateOrProvinc e, "'", "''") + "', '" +
Replace(shippingInformation__varShipPostalCode, "'", "''") + "', '" +
Replace(shippingInformation__varShipCountry, "'", "''") + "', '" +
Replace(shippingInformation__varShipPhoneNumber, "'", "''") + "', '" +
Replace(shippingInformation__varShipFaxNumber, "'", "''") + "', '" +
Replace(shippingInformation__varShipEmailAddress, "'", "''") + "', '" +
Replace(shippingInformation__varOrderNotes, "'", "''") + "') "
shippingInformation.CommandType = 1
shippingInformation.CommandTimeout = 0
shippingInformation.Prepared = true
shippingInformation.Execute()
' END COMMAND CODE FOR SHIPPING INFORMATION

%>
<% End If 'end the whole submit button loop %>



Now, my only problem is that when the new customer record gets inserted it
automatically creates a CustomerID number for the new record in the Customers
table. I need this newly created CustomerID to be placed into the CustomerID
field of my Orders table to ensure the relationship exists. DrDev gives a
"retrieve identity of new record" code example:

<%
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\WebSiteDirectory\DatabaseDirectory\YourD B.mdb; Persist Security
Info=False"
commInsert.Execute("INSERT INTO tableName(TextField, NumericField)
VALUES('ABCDE', 12345);") ' Execute the insert command
Set rsNewID = commInsert.Execute("SELECT @@IDENTITY")
intNewID = rsNewID(0) ' Store the value of the new identity in variable
intNewID
rsNewID.Close
Set rsNewID = Nothing
commInsert.Close
Set commInsert = Nothing
%>


I cannot figure out, however, how to impliment this into my code to make it
work. I've tried creating the recordset to store the new CustomerID
immediately after running the INSERT command:


<%
' COMMAND CODE FOR INSERTING BILLING INFORMATION INTO DATABASE (CUSTOMERS
TABLE)
set insertBilling = Server.CreateObject("ADODB.Command")
insertBilling.ActiveConnection = MM_connKennysTile_STRING
insertBilling.CommandText = "INSERT INTO Customers (ContactFirstName,
ContactLastName, CompanyName, BillingAddress, BillingAddress2, City,
StateOrProvince, PostalCode, CountryOrRegion, PhoneNumber, FaxNumber,
EmailAddress, Notes) VALUES ('" + Replace(insertBilling__varContactFirstName,
"'", "''") + "', '" + Replace(insertBilling__varContactLastName, "'", "''") +
"', '" + Replace(insertBilling__varCompanyName, "'", "''") + "', '" +
Replace(insertBilling__varBillingAddress, "'", "''") + "', '" +
Replace(insertBilling__varBillingAddress2, "'", "''") + "', '" +
Replace(insertBilling__varCity, "'", "''") + "', '" +
Replace(insertBilling__varStateOrProvince, "'", "''") + "', '" +
Replace(insertBilling__varPostalCode, "'", "''") + "', '" +
Replace(insertBilling__varCountryOrRegion, "'", "''") + "', '" +
Replace(insertBilling__varPhoneNumber, "'", "''") + "', '" +
Replace(insertBilling__varFaxNumber, "'", "''") + "', '" +
Replace(insertBilling__varEmailAddress, "'", "''") + "', '" +
Replace(insertBilling__varNotes, "'", "''") + "') "
insertBilling.CommandType = 1
insertBilling.CommandTimeout = 0
insertBilling.Prepared = true
insertBilling.Execute()
' END COMMAND CODE FOR INSERTING BILLING INFORMAITON INTO DATABASE
' CREATE RECORDSET AND VARIABLE TO HOLD CUSTOMER ID DATA
Set rsNewID = insertBilling.Execute("SELECT @@IDENTITY") ' Create a recordset
and SELECT the new Identity
intNewID = rsNewID(0) ' Store the value of the new identity in variable
intNewID
rsNewID.Close
Set rsNewID = Nothing
insertBilling.Close
Set insertBilling = Nothing
%>


I'm getting an error though:

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or
ordinal.
/KennysTile/woodOrderForm/index2.asp, line 57

Line 57 is: intNewID = rsNewID(0)

[RobGT]

You are trying to execute the command twice:
--------------------
insertBilling.Execute()
' END COMMAND CODE FOR INSERTING BILLING INFORMAITON INTO DATABASE
' CREATE RECORDSET AND VARIABLE TO HOLD CUSTOMER ID DATA
Set rsNewID = insertBilling.Execute("SELECT @@IDENTITY") ' Create a
recordset
--------------------

Try this instead:

insertBilling.CommandText = SET NOCOUNT ON; " & insertBilling.CommandText &
"; SELECT SCOPE_IDENTITY() AS ID"
Set rsNewID = MM_insertBilling.Execute
IF (NOT rsNewID .EOF) THEN
varLastID = rsNewID.Fields.Item("ID").Value
END IF

Then you have the last ID from that insert statement returned to you in the
varLastID variable.
You can then use that variable value in your second insert statement to
populate your foreign key, as you need to do.

I use SCOPE_IDENTITY() instead of @@IDENTITY just in case you have a busy
database - this means that you will only get back the ID from the insert
that YOU made (if someone else makes an insert at the same time you COULD
get the wrong value returned to you by using @@IDENTITY.

The above code is in ASP VBScript, as per your original message.
SCOPE_IDENTITY() is a SQL Server database variable - I'm guessing that you
are using SQL Server?

I hope this helps.
Cheers,
Rob
[url]http://robgt.com/[/url]

[RobGT]

Sorry - typo on this line:

Set rsNewID = MM_insertBilling.Execute

It should read like this:

Set rsNewID = insertBilling.Execute()

Cheers,
Rob
[url]http://robgt.com/[/url]