I need to insert data into multiple tables - how?

Do you have the option of using Stored Procedures?


"Pat Shaw" <pat@nomail.com> wrote in message
news:f9flpl$fgg$1@forums.macromedia.com...
> There are a number of ways. For ease, I would declare an OUTPUT variable
> in your SQL statement and then when you have done the first INSERT you
> assign the value of SCOPE_IDENTITY() to the OUTPUT Variable. You then use
> the variable like any other in your SQL and your ASP.
>
>
> "Mark.P." <mark.pusateri@ey.com> wrote in message
> news:f9fjsn$d5l$1@forums.macromedia.com...
>> most of what I've found is for asp.net. so I'm having a hard time
>> finding how
>> to pass the id to the next page with ASP.
>>
>> All I can find tells me to put it into my insert, but nothing past that.
>>
>> MM_editCmd.CommandText = "INSERT INTO dbo.projects (pName, pLead, status,
>> startDate, endDate, audience, ojective, deliverables, issues) VALUES (?,
>> ?, ?,
>> ?, ?, ?, ?, ?, ?) SELECT SCOPE_IDENTITY()"
>>
>> So how can I get that id from SCOPE_IDENTITY() then pass it on?
>>
>
>

There are a number of ways. For ease, I would declare an OUTPUT variable in
your SQL statement and then when you have done the first INSERT you assign
the value of SCOPE_IDENTITY() to the OUTPUT Variable. You then use the
variable like any other in your SQL and your ASP.


"Mark.P." <mark.pusateri@ey.com> wrote in message
news:f9fjsn$d5l$1@forums.macromedia.com...
> most of what I've found is for asp.net. so I'm having a hard time finding
> how
> to pass the id to the next page with ASP.
>
> All I can find tells me to put it into my insert, but nothing past that.
>
> MM_editCmd.CommandText = "INSERT INTO dbo.projects (pName, pLead, status,
> startDate, endDate, audience, ojective, deliverables, issues) VALUES (?,
> ?, ?,
> ?, ?, ?, ?, ?, ?) SELECT SCOPE_IDENTITY()"
>
> So how can I get that id from SCOPE_IDENTITY() then pass it on?
>

most of what I've found is for asp.net. so I'm having a hard time finding how to pass the id to the next page with ASP.

All I can find tells me to put it into my insert, but nothing past that.

MM_editCmd.CommandText = "INSERT INTO dbo.projects (pName, pLead, status, startDate, endDate, audience, ojective, deliverables, issues) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) SELECT SCOPE_IDENTITY()"

So how can I get that id from SCOPE_IDENTITY() then pass it on?

You would do the Insert to your main table and then use SCOPE_IDENTITY() to
get the Primary Key and assign it to a variable within your Stored
Procedure. Then, you would use the variable to populate your Foriegn Keys in
the other tables with the other Inserts.

Google SCOPE_IDENTITY() and you will see how it works.

Pat.


"Mark.P." <mark.pusateri@ey.com> wrote in message
news:f9fbe4$3bo$1@forums.macromedia.com...
> Using ASP, VBScript & MS SQL -
>
> My main table has a PK with the name projectID, this field is updated
> automatically in SQL. I need that ID in order to populate the rest of the
> tables. How do I retrieve this ID before moving on to the next page(s)?
>
> I've seen some similar questions but none have really been answered,
> Hoping
> this time is the charm.
>
> Thanks
>
>