p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

asp_databases thread: Update Command :: Obtaining SQL Identity Field from new record in recordser

Message #1 by "mike.carlton@b... on Wed, 12 Jun 2002 16:51:23
Help! I am adding a new record to the datastore, (data passed from a 
form) I then want to obtain the ID of this new record from the datastore. 

I am storing the ID in a variable to be used later. 

However, each time the code is executed the variable is empty. The code 
works fine when using Access as the datastore, but not with SQL server 

Thanks in advance, 

Mike Carlton


Code Below 

<!--#include file="connect.asp" -->
	'First of all we want to add a new record to the cusotmers table 
then we want to obtain the 
	'new customer id for use in the address table
		'Stage 1 :: Customer Table
		Dim ObjRsCustomer, ObjRsAddress, IntCustomerID, 
		Set ObjRsCustomer = Server.CreateObject("ADODB.Recordset")
		objRsCustomer.Open "Customers", DcHealth, adOpenDynamic, 
		'Update the recordset
		ObjRsCustomer.Fields("CompanyName") = Request.Form
		ObjRsCustomer.Fields("ForeName") = Request.Form
		ObjRsCustomer.Fields("Surname") = request.FOrm("surname")
		ObjRsCustomer.Fields("EmailAddress") = Request.form
		ObjRsCustomer.Fields("Telephone") = Request.form
		ObjRsCustomer.Fields("fax") = Request.form("fax")
		ObjRsCustomer.Fields("UserCreated") = Session("UsrName")
		'Define the new customer ID to a V
		IntCustomerID = ObjRsCustomer.Fields("CustomerID")
		'Close Customer Recordset
		Set ObjRsCustomer = Nothing
		'Stage 3 :: Address Table
		Set ObjRsAddress = Server.CreateObject("ADODB.Recordset")
		objRsAddress.open "Addresses", DcHealth, adOpenDynamic, 
		'Update the Recordset
		ObjRsAddress.Fields("CustomerID") = IntCustomerID
		ObjRsAddress.Fields("ForeName") = Request.form("ForeName")
		ObjRsAddress.Fields("Surname") = Request.form("Surname")
		ObjRsAddress.Fields("Company") = request.form
		ObjRsAddress.Fields("Building") = Request.Form("building")
		ObjRsAddress.Fields("Street") = Request.form("Street")
		ObjRsAddress.Fields("Locality") = Request.form("Locality")
		ObjRsAddress.Fields("town") = Request.form("town")
		ObjRsAddress.Fields("County") = Request.form("County")
		ObjRsAddress.Fields("PostCode") = Request.form("PostCode")
		ObjRsAddress.Fields("Country") = Request.form("country")
		ObjRsAddress.Fields("Telephone") = Request.form
		ObjRsAddress.Fields("Fax") = Request.form("fax")
		ObjRsAddress.Fields("DirectTelephone") = Request.form
		ObjRsAddress.Fields("DirectFax") = Request.form
		ObjRsAddress.Fields("Mobile") = request.form("Mobile")
		ObjRsAddress.Fields("EmailAddress") = Request.form
		IntAddressID = ObjRsAddress.Fields("AddressID")
		'Close and tidy up
		Set ObjRsAddress = Nothing
		'Update the customers info with the HQ Address
		Dim MySelectSQL, ObRsHqID
		MySelectSQL = "SELECT HqAddressID, CustomerID FROM 
Customers WHERE CustomerID = '" & IntCustomerID & "';"
		Set ObRsHqID = Server.CreateObject("ADODB.Recordset")
		ObRsHQID.Open MySelectSQL, DCHealth
		'Check for EOF
		If ObRsHQID.EOF Then
			'Define error then redirect back to main menu!
			Session("Message") = "Could not define HQ address 
Line 73. Contact IT on 01608 649 250 Int Customer ID =" & IntCustomerID 
& " IntAddressID=" & IntAddressID
			Set ObRsHqID = Nothing
			Set DCHealth = Nothing
			Response.Redirect "Menu.asp"
			'Update the datastore
			ObRsHQID.Fields("HQAddressID") = IntAddressID
			'Close + Tidy up
			Set ObRsHQID = Nothing
		End If
		Set DCHealth = Nothing
		'Redirect to the confirmation page
		Session("Message") = "The New Customer Has Been Added To 
The DataStore"
		Response.Redirect "Menu.asp"

  Return to Index