2. ASPRunnerPro General questions
  3. automatic Record insert
This topic is locked

automatic Record insert

 		4/7/2006 11:18:28 PM
ASPRunnerPro General questions
Lowkeck authorDevClub member

I have a master relationship between two SQL tables. Is there a way to create a field that automatically inserts data from a field in the master table when a new record is added to the child table?
For example my master table "customers" has a field called "custname". When I add a new record to child table I would like to have a field called "custname" automatically inserted from the master table?
Hope I am explaining this clearly.
Sergey Kornilov admin 4/10/2006

Hi,
you can do it using events.

Go to the Events Tab, select Before record added event and choose Insert a record into another table action.l
Lowkeck authorDevClub member 4/10/2006

I tried the following code without success.
Sub AddOnLoad()

'** Insert a record into another table ****

strSQLInsert = "insert into TICKETFORM (Customer) values CUSTOMER INFO(Company Name)"

dbConnection.Execute strSQLInsert
End Sub
Error message is as follows:
Error number -2147217900

Error description Line 1: Incorrect syntax near 'CUSTOMER INFO'.

URL /Ticket_Form_add.asp

SQL query select [Customer], [Key], [Call Recieved], [Time], [Reported by], [Problem], [Resolution], [Running Time], [Status], [Technician] from [dbo].[Ticket Form] where 1<0

Additional info Event: Call AddOnLoad()
I believe the problem is I need to "Insert a record from another table" instead of insert a record to another table. Is this possible?
Thanks for all your help I really do appreciate it.
Sergey Kornilov admin 4/10/2006

If you need to carry over field value from master table to one of child table fields I recommend the following.

  1. Use child table ListOnLoad Event to store master table field value in Session variable. Make sure to replace bolded names with real table and field names.
    set rstmp = Server.CreateObject("ADODB.Recordset")

    rstmp.Open "select fieldname from mastertable where masterkey = " Request("masterkey"), dbConnection
    Session("MasterFieldName") = rstmp(0)
    rstmp.Close : set rstmp=nothing


2. Set default value of child table field to Session("MasterFieldName").
This is it.