I am trying to transfer a ParentID value to the DMP_Lookup table in my sample ASP database application. The "Town" lookup values are stored in a self-referencing hierarchy table. They are provided to the application using a query "qryTown".
The application gives you the option to "Add" new values while editing a data table form. When I add a new town name (ie Lookup), it does NOT transfer the ParentID that is present in the query. In this case the ParentID = 1, but could be any integer.
The form generator application I am using is AspMaker 10.1. The project is in the attached file (no its not because EE is munted). The program is 17mb and is free to demo. The AspMaker project file is "LookupHierarchyTest.agp".
This project is setup with the folder and database path on the root of the c drive.
The support staff sent me the following code to include on the "Row_Inserting" server event. It works if you place in the Parent ID value in, but I want that to be parsed without the need for user intervention. The user ONLY needs to add the Lookup name.
Dim ar, ParentName, ParentID
ar = Split(rsnew("Name"), " ") ' Split the name to get table name
ParentName = ar(0)
If ParentName <> rsnew("Name") Then
ParentID = ew_ExecuteScalar("SELECT ID FROM <Table> WHERE Name = '" & ew_AdjustSql(ParentName) & "'")
ParentID = 0
rsnew("ParentID") = ParentID