2. ASPRunnerPro General questions
  3. Create Field ID on Header and Pass to Detail
This topic is locked

Create Field ID on Header and Pass to Detail

 		10/19/2006 7:06:33 PM
ASPRunnerPro General questions
T
 tgunther author

I am setting up an IT request page on our intranet using asp pages and our sql server, I have created a header table and a detail table. My goal is that someone will enter data into the header page, the header page will then create an id for their entry which will be added to the detail page and when they submit the entry it will send them to the detail add page to enter in the additional details of the request. I created a project linking the two tables together by a key column and the header table is setup on the sql server to auto create an id when an entry is added but the asp page won't add an entry, so the key column is never populated. Can you help?
J
 		Jane 10/20/2006

Hi,
you can select id of added record and add it to the detail table as default values.

To select id of added record use AfterAdd event for Master table:

Sub AfterAdd()

str = "select @@IDENTITY from MasterTableName"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
Session("foreignkey") = rsTemp(0)

End Sub



where MasterTableName is your actual table name.

Then use Session("foreignkey") as default value on the "Edit as" settings dialog for your id field in the Detail table.
T
 		tgunther author 10/20/2006

Thanks! That worked great! I just have one more question I would like to make it so that once a header record is made it takes them directly to the add detail page for that header record, so that they are forced to have at least one detail record per header record.
Sergey Kornilov admin 10/23/2006

Here is how you can do this.
This example uses Make as a Master table and Cars as a Details table. After new make is added user is redirected to the details page. Make sure you replace sample table and fields names with real names.

Sub AfterAdd()
Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open "select name from make where id=(select max(id) from make)", dbConnection
SESSION("cars_masterkey1") = rstemp(0)

rstemp.close : set rstemp=nothing
Session("cars_mastertable")="Make"
Response.Redirect "cars_add.asp"
End Sub
T
 		tgunther author 10/23/2006

Thank you for your help I greatly appreciate it. I have tried to implement the new code that you gave me unfortunately it still does not work. I recieve an error that there is an incorrect syntax near keyword key. Here is the code I have modified for my situation:

Sub AfterAdd()

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open "select key from dbo.header where id=(select max(id) from dbo.header)", dbConnection
Session("foreignkey") = rstemp(0)

rstemp.close : set rstemp=nothing
Session("masterkey")="key"
Response.Redirect "detail_add.asp"
End Sub



The name of my master table is header and the name of my other table is detail. The name of the column that the unique field is made in is called 'key'. Sorry that I am having trouble understanding what you are telling me, but thank you for the help.
Sergey Kornilov admin 10/23/2006

You are not following the code I posted.
Try the following. This code assumes that key field is both IDENTITY field in header table and also a foreign key that points to detail table.
PS. key is a reserved keyword in SQL Server and you need to wrap it using square brackets every time you use it in SQL.

Sub AfterAdd()
Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open "select [key] from dbo.header where [key]=(select max([key]) from dbo.header)", dbConnection
SESSION("detail_masterkey1") = rstemp(0)

rstemp.close : set rstemp=nothing
Session("detail_mastertable")="Header"
Response.Redirect "detail_add.asp"
End Sub