I need to insert into 2 seperate tables but one of the tables is dependant on an entry into the other the code I am using is:

if Request("selected" <> "" then
rsContacts.AddNew
rsContacts("Title"=Request("title"
rsContacts("FirstName"=Request("FirstName"
rsContacts("Surname"=Request("Surname"
rsContacts("Add1"=Request("Address1"
rsContacts("Add2"=Request("Address2"
rsContacts("Town"=Request("Town"
rsContacts("County"=Request("County"
rsContacts("PCode"=Request("PCode"
rsContacts("Country"=Request("Country"
rsContacts("Tel"=Request("Tel"
rsContacts("Email"=Request("Email"
rsContacts.Update

intNewID = Command1.Parameters.Item("@RETURN_VALUE".Value

rsEnquiries.AddNew
rsEnquiries("ContactID"= intNewID
rsEnquiries("Source"=Request("Source"
rsEnquiries("SourceOther"=Request("SourceOther"
rsEnquiries("SendBrochure"=Request("brochure"
rsEnquiries("Query"=Request("query"
rsEnquiries("ReceivedBy"=Request("receivedby"
rsEnquiries.Update
Response.Redirect("contact_done.asp"
End If

at the head of the page is:

set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_connBrume_STRING
Command1.CommandText = "artifex_brume.spInsertRecord"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@Text", 200, 1,abc,Command1__Text)
Command1.Parameters.Append Command1.CreateParameter("@Number", 3, 1,123,Command1__Number)
Command1.Execute()

Which simply calls the stored proceedure on the SQL db which is:

CREATE PROCEDURE spInsertRecord

@Text varchar(20),
@Number int

AS

SET NOCOUNT ON

INSERT INTO tblName(TextField, NumberField) VALUES(@Text, @Number) SELECT @@IDENTITY

RETURN @@IDENTITY

SET NOCOUNT OFF

Go

I have a felling that the 2 pieces of code aren't compatible can anyone shed any light on this, or even suggest a better way of achiving the same result

Sorry Matt, Not sure i'm following this correctly...

first you insert their details into 1 table (title, first name etc) then you want to use their PK to create a related record in another table?
If that's the case you could simply get your SP to do both inserts for you... pass it all the values from the page and then do your insert into the 1st table, then set @intNewID = @@IDENTITY and use that when inserting the rest of the details into the 2nd table.

Sharing knowledge saves valuable time!

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]