2. ASPRunnerPro General questions
  3. View details from another table
This topic is locked

View details from another table

 		3/5/2009 05:10:08
ASPRunnerPro General questions
H
 HilcrRWise author

I have two tables which I have joined together at the Edit SQL query stage, which displays fine on the list page, but I also need to beable to display data from both tables on the add and edit pages while still being able to save the changes back to the table. Only one of these tables actually needs to be updateable, the other is only there for additional info. The table structure is as follows:
Table1 (Updateable)

IDCode

BehaviourRating

AttitudeRating

WorkRating

Comment
Table2 (Info Only)

IDCode

Surname

FirstName

Gender
How can I display all the fields from both tables on the Add and Edit pages while still being able to save changes to Table1 fields?
Please be aware I am new to this program and ASP in general so could you please provide as much detail as possible.
Thanks
J
 		Jane 3/5/2009

Richard,
you can add joined fields to the edit page as well on the Choose fields tab and set up these fields as readonly on the "Edit as" settings dialog on the Visual Editor tab.
H
 		HilcrRWise author 3/5/2009

Thanks for that the edit page is working fine now.
The next step is to sort the add page so it does what I want (or modify the edit page to do the same if possible).
Table2 contains a full list of people, however Table1 does not always have corresponding data for all the people in that list, so the list page will look something like:
IDCode Surname FirstName Gender IDCode BehaviourRating AttitudeRating WorkRating Comment

0001 AAAAA AAAAAA M 0001 Good Good Good comment1

0002 BBBBB BBBBBB F 0002 Poor Good Satisfactory comment 2

0003 CCCC CCCCC M

0004 DDDD DDDDD F
I am able to edit record 1 and 2 fine, but if I go to edit for record 3 or 4 I can enter the data for the fields in Table1 but when I click save it just goes back to the list page without saving anything, but if I use the add page the data from Table 2 is not displayed.
Ideally what I want to beable to do is click on the edit button on the line I want to change and if there is no matching data in Table1 I want a new record to be added to the table using the data entered on the edit page.
Assuming this makes sense do you have any recomendations on how to acomplish this.
Thanks
Sergey Kornilov admin 3/5/2009

Ideally what I want to beable to do is click on the edit button on the line I want to change and if there is no matching data in Table1 I want a new record to be added to the table using the data entered on the edit page.


This can be done using events. Use BeforeProcessAdd event to check if associated record exists (sample action Check if record exists). Then use Data Access Layer to add a record if required.
More info on DAL: http://www.xlinesoft.com/asprunnerpro/docs...scess_layer.htm
H
 		HilcrRWise author 3/6/2009

I am having problems with the Check If Record Exists Code. I have the following code to test if the argument is working but currently no message is displayed.

'********** Check if specific record exists ************

set rsExists = dal.Table1.Query("IDCode=" & values("Table2.IDCode"),"")

if not rsExists.eof then

Response.Write "Record Exists"

else

Response.Write "Record Does Not Exist"

end if

rsExists.Close : set rsExists = Nothing


I need it to look in the IDCode field of Table1 and see if it contains the value taken from the IDCode of Table2 for the record where edit was selected on the list page.
Can you please point out where I am going wrong.
Thanks
H
 		HilcrRWise author 3/9/2009

Bump