Need help on Master Detail editing

[Dave Gardner]

Hi,
I'm on a tight deadline project and am attempting to perform the following task.
With ASPDb as the interface to a SQLServer 7 database, I set up a Master-Detail relationship between two tables, with ASPdb editing allowed on both tables. I use a custom button labeled "DrillDown" to capture the Master table's index and use that to alter the dbSQL statement on the Detail level.

This all works well and good up to this point. I want to emphasize this point... that the basic functionality of the master-detail relationship is working fine. I can edit both tables and navigate between them.

Now, the problem is that I need to consider the set of detail records associated with a record in the master as an "object". Why we need to do this is related to having auditing on all edits done at the detail level bump up a revision code at the master level. However, it would not make sense to alter 5 records at the detail level suddenly bump the revision code up 5 at the master level. What we need is for a "session" of edits at the detail level bump up the revision code by 1 at the master level.

I thought the easiest way to do this would be to have dbSilentCmdText create a temporary table, and reference the temporary table in dbSQL. This way, all edits are done against the temporary table at the detail level.
Example : oDB.dbSilentCmdText = "Select * into #TempTable from Detail where DetailIndex = MasterIndex"
oDB.dbSQL = "Select * from #TempTable"

Then, when the editing session is done, I could check to see if any changes occurred in the temporary table and if so, only then copy the temporary records back to the original location and bump the revision code at the
master level. I really hope this all makes sense...

Okay, the real problem is when I press the Edit button at the detail level, I get an ASP error saying that the #TempTable was not found! But it was sure displaying fine! I also have CloseConnection=FALSE in dbOptions. No help.
Does ASPdb editing open up another connection with the database that would preclude using the #TempTable?
Do you have any other suggestions for accomplishing what I'm trying to do?

Hope you can help me out...
Dave Gardner
Consultant
Medtronic Inc.
Dave.Gardner@medtronic.com

[John]

Hi Dave,

A couple of things...

First, make SURE you're using unique dbUnit numbers (and unique objects if
you wish) for each "call" to ASPdb.

Next, are you by chance in a dual dbMode? If so, that could be your problem.
You see, ASPdb actually displays "twice" in those modes. It may not have
created the table by the point you've asked for the data.

Finally, we have an example that shows basically what you're looking for on
our site at: www.aspdb.com/test/selectinto.asp It makes a table from
another table, displays that temp table and then deletes it. It should
make good reading!

Thanks,
John



------------
Dave Gardner at 1/21/00 6:23:26 PM

Hi,
I'm on a tight deadline project and am attempting to perform the following task.
With ASPDb as the interface to a SQLServer 7 database, I set up a Master-Detail relationship between two tables, with ASPdb editing allowed on both tables. I use a custom button labeled "DrillDown" to capture the Master table's index and use that to alter the dbSQL statement on the Detail level.

This all works well and good up to this point. I want to emphasize this point... that the basic functionality of the master-detail relationship is working fine. I can edit both tables and navigate between them.

Now, the problem is that I need to consider the set of detail records associated with a record in the master as an "object". Why we need to do this is related to having auditing on all edits done at the detail level bump up a revision code at the master level. However, it would not make sense to alter 5 records at the detail level suddenly bump the revision code up 5 at the master level. What we need is for a "session" of edits at the detail level bump up the revision code by 1 at the master level.

I thought the easiest way to do this would be to have dbSilentCmdText create a temporary table, and reference the temporary table in dbSQL. This way, all edits are done against the temporary table at the detail level.
Example : oDB.dbSilentCmdText = "Select * into #TempTable from Detail where DetailIndex = MasterIndex"
oDB.dbSQL = "Select * from #TempTable"

Then, when the editing session is done, I could check to see if any changes occurred in the temporary table and if so, only then copy the temporary records back to the original location and bump the revision code at the
master level. I really hope this all makes sense...

Okay, the real problem is when I press the Edit button at the detail level, I get an ASP error saying that the #TempTable was not found! But it was sure displaying fine! I also have CloseConnection=FALSE in dbOptions. No help.
Does ASPdb editing open up another connection with the database that would preclude using the #TempTable?
Do you have any other suggestions for accomplishing what I'm trying to do?

Hope you can help me out...
Dave Gardner
Consultant
Medtronic Inc.
Dave.Gardner@medtronic.com