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 
2000. 

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
		'V's
		Dim ObjRsCustomer, ObjRsAddress, IntCustomerID, 
IntAddressID
		'Object
		Set ObjRsCustomer = Server.CreateObject("ADODB.Recordset")
		objRsCustomer.Open "Customers", DcHealth, adOpenDynamic, 
adLockPessimistic
		
		'Update the recordset
		ObjRsCustomer.AddNew
		ObjRsCustomer.Fields("CompanyName") = Request.Form
("CompanyName")
		ObjRsCustomer.Fields("ForeName") = Request.Form
("ForeName")
		ObjRsCustomer.Fields("Surname") = request.FOrm("surname")
		ObjRsCustomer.Fields("EmailAddress") = Request.form
("EmailAddress")
		ObjRsCustomer.Fields("Telephone") = Request.form
("telephone")
		ObjRsCustomer.Fields("fax") = Request.form("fax")
		ObjRsCustomer.Fields("UserCreated") = Session("UsrName")
		
		ObjRsCustomer.Update
		'Define the new customer ID to a V
		IntCustomerID = ObjRsCustomer.Fields("CustomerID")
		
		'Close Customer Recordset
		ObjRsCustomer.Close
		Set ObjRsCustomer = Nothing
		
		'Stage 3 :: Address Table
		Set ObjRsAddress = Server.CreateObject("ADODB.Recordset")
		objRsAddress.open "Addresses", DcHealth, adOpenDynamic, 
adLockPessimistic
		
		'Update the Recordset
		ObjRsAddress.AddNew
		ObjRsAddress.Fields("CustomerID") = IntCustomerID
		ObjRsAddress.Fields("ForeName") = Request.form("ForeName")
		ObjRsAddress.Fields("Surname") = Request.form("Surname")
		ObjRsAddress.Fields("Company") = request.form
("CompanyName")
		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
("telephone")
		ObjRsAddress.Fields("Fax") = Request.form("fax")
		ObjRsAddress.Fields("DirectTelephone") = Request.form
("DirectTelephone")
		ObjRsAddress.Fields("DirectFax") = Request.form
("DirectFax")
		ObjRsAddress.Fields("Mobile") = request.form("Mobile")
		ObjRsAddress.Fields("EmailAddress") = Request.form
("EmailAddress")
		ObjRsAddress.Update
		
		'ObtainAddressID
		IntAddressID = ObjRsAddress.Fields("AddressID")
		
		'Close and tidy up
		ObjRsAddress.CLose
		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
			ObRsHqID.Close
			Set ObRsHqID = Nothing
			DcHealth.close
			Set DCHealth = Nothing
			Response.Redirect "Menu.asp"
		Else
			'Update the datastore
			ObRsHQID.Fields("HQAddressID") = IntAddressID
			ObRsHQID.Update
			'Close + Tidy up
			ObRsHQID.close
			Set ObRsHQID = Nothing
		End If
		
		DcHealth.Close
		Set DCHealth = Nothing
		
		'Redirect to the confirmation page
		Session("Message") = "The New Customer Has Been Added To 
The DataStore"
		Response.Redirect "Menu.asp"
		 %>