Add records 2 a 2nd table on the add page

Add records 2 a 2nd table on the add page

Postby david freeman » Fri May 13, 2016 9:20 pm

I have a project and i am sure others might where I need the record from a add age to be inserted into a 2nd table as a history, this would have to be on a Add page and Edit page.

It would be good to have a way to in the designer stipulate a History table and when adding or editing to be able to put the same date into a 2nd table like a 1 to many history as you can do in MS access.
david freeman
 
Posts: 8
Joined: Sat Sep 13, 2014 1:25 am

Re: Add records 2 a 2nd table on the add page

Postby david freeman » Fri Jun 24, 2016 12:01 pm

I have managed to get the following script to work to add to a 2nd table by adding to the Row inserting and Uptating events
Would still be nice to be able to do this from the generation of the web pages
Change the fields to yours as required and the table name

' Response.Write "Row Updated"

Dim con, sql_insert, data_source

Session("LTitle") = Request.Form("x_Title")
Session("Fname") = Request.Form("x_FirstName")
Session("LName") = Request.Form("x_LastName")
Session("LDOB") = LostMain.DOB.CurrentValue
Session("LAffiliation") = Request.Form("x_Affiliation")
Session("LUNIndexNo") = Request.Form("x_UNIndexNo")
Session("LPersonNoCcure") = Request.Form("x_PersonNoCcure")
Session("LMifareCardNo") = Request.Form("x_MifareCardNo")
Session("LValidFrom") = LostMain.ValidFrom.CurrentValue
Session("LValidUntil") = LostMain.ValidUntil.CurrentValue
Session("LStolen") = LostMain.Stolen.CurrentValue
Session("LWhenLost") = Request.Form("x_WhenLost")
Session("LWhereLost") = Request.Form("x_WhereLost")
Session("LOfficerTakingReport") = Request.Form("x_OfficerTakingReport")
Session("LOffenceTotal") = Request.Form("x_OffenceTotal")
Session("LCardType") = Request.Form("x_CardType")
Session("LDate") = LostMain.Stolen.CurrentValue
Session("LAdded") = LostMain.Added.CurrentValue


data_source = "dsn=2016WEBPAGE;uid=;pwd="


sql_insert = "insert into LostHistory (Title, Date, CardType, OffenceTotal, OfficerTakingReport, WhereLost, WhenLost, Stolen, ValidUntil, ValidFrom, MifareCardNo, PersonNoCcure, UNIndexNo, Affiliation, DOB, FirstName, LastName)"
sql_insert = sql_insert & "values ('" & session("LTitle") & "','" & Date() & "','" & session("LCardType") & "','" & session("LOffenceTotal") & "','" & session("LOfficerTakingReport") & "','" & session("LWhereLost") & "','" & session("LWhenLost") & "','" & session("LStolen") & "','" & session("LValidUntil") & "','" & session("LValidFrom") & "','" & session("LMifareCardNo") & "','" & session("LPersonNoCcure") & "','" & session("LUNIndexNo") & "','" & session("LAffiliation") & "','" & session("LDOB") & "','" & session("Fname") & "','" & session("LName") & "')"

' Creating the Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source

' Executing the sql insertion code
con.Execute sql_insert

' Done. Now Close the connection
con.Close
Set con = Nothing
End Sub
david freeman
 
Posts: 8
Joined: Sat Sep 13, 2014 1:25 am


Return to Feature Requests (ASPMaker)