I need to insert data into multiple tables - how?

Report · Aug 09, 2007

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

Report · Aug 09, 2007

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

Report · Aug 09, 2007

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?

Report · Aug 09, 2007

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?
>

Report · Aug 09, 2007

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?
>>
>
>

Report · Aug 09, 2007

I do have that option but I don't know how to write them. I don' t normally do this kind of thing but due to recent org changes i'm learning on the fly. I was hoping this would be on the easy side. Guess not (at least not for me). Here's where I'm at now with it, it's returning an error at this point.

Report · Aug 09, 2007

Post your SQL statements and I will put this into a Stored Procedure for
you. This will give you far more flexibility and will run more efficiently.

I may not be able to do it tonight though as I am about to go out.

"Mark.P." <mark.pusateri@ey.com> wrote in message
news:f9fp2m$j5c$1@forums.macromedia.com...
>I do have that option but I don't know how to write them. I don' t
>normally do
> this kind of thing but due to recent org changes i'm learning on the fly.
> I
> was hoping this would be on the easy side. Guess not (at least not for
> me).
> Here's where I'm at now with it, it's returning an error at this point.
>
>
>
>
> <%
> If (CStr(Request("MM_insert")) = "addProject") Then
> If (Not MM_abortEdit) Then
>
> 'intNewKeyVal = rsProjects.Execute(MM_editCmd, intRecordsAffected,
> adCmdText)(0)
> ' execute the insert
> Dim MM_editCmd
>
> Set MM_editCmd = Server.CreateObject ("ADODB.Command")
> MM_editCmd.ActiveConnection = MM_commStr_STRING
> MM_editCmd.CommandText = "INSERT INTO dbo.projects (pName, pLead,
> status,
> startDate, endDate, audience, ojective, deliverables, issues) VALUES (?,
> ?, ?,
> ?, ?, ?, ?, ?, ?) SELECT SCOPE_IDENTITY()"
> MM_editCmd.Prepared = true
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202,
> 1,
> 100, Request.Form("pName")) ' adVarWChar
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202,
> 1,
> 100, Request.Form("pLead")) ' adVarWChar
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202,
> 1,
> 50, Request.Form("status")) ' adVarWChar
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 135,
> 1,
> -1, MM_IIF(Request.Form("startDate"), Request.Form("startDate"), null)) '
> adDBTimeStamp
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 135,
> 1,
> -1, MM_IIF(Request.Form("endDate"), Request.Form("endDate"), null)) '
> adDBTimeStamp
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202,
> 1,
> 250, Request.Form("audience")) ' adVarWChar
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 202,
> 1,
> 500, Request.Form("objective")) ' adVarWChar
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 202,
> 1,
> 500, Request.Form("deliverables")) ' adVarWChar
> MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9", 202,
> 1,
> 500, Request.Form("issues")) ' adVarWChar
> MM_editCmd.Execute
> intNewKeyVal = rsProjects.Execute(MM_editCmd, intRecordsAffected,
> adCmdText)(0)
> MM_editCmd.ActiveConnection.Close
>
> ' append the query string to the redirect URL
> Dim MM_editRedirectUrl
> MM_editRedirectUrl = "insertPart2.asp?fKey=intNewKeyVal"
> If (Request.QueryString <> "") Then
> If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
> MM_editRedirectUrl = MM_editRedirectUrl & "?" &
> Request.QueryString
> Else
> MM_editRedirectUrl = MM_editRedirectUrl & "&" &
> Request.QueryString
> End If
> End If
> Response.Redirect(MM_editRedirectUrl)
> End If
> End If
> %>
>

Report · Aug 09, 2007

Thank you so much! The column that auto-increments is named "projectID". Let me know if I left something out.

Thanks again