Joined Table Question

[Ron Barner]

Re: your Enterprise Example #6 relating to joined tables.

The example shows a Customer Table joined to an Orders Table from the NWIND database. In this example you show how to add a new order to the Orders table.

Is it possible to change this example so that the user can add a New Customer AND a new Order at the same time, instead of having one function to add a Customer, then another function (like your example) to add an Order?

[John]

Hi Ron,

At present, ASP-db issues ONE SQL Update statement when you update a record.
I can't think of an SQL Update statement that modifies TWO or more tables
simultaneously. Besides that, things could get really messy if only one of
the updates failed. Should you roll back that transaction or both? Could
it even be detected, or would the last updated table set the success flag?

There may be a way with a stored procedure to accomplish what you're after.
Especially if you couple that with the template features that will be
available in the ASP-db 2000 version. Feel free to contact us and we'd be
glad to give you an estimate on what it would cost to develop a solution to
your problem. It would involve custom ASP code, and possibly a feature
addition to the component, which would obviously be at an additional charge.

Thanks,
John



------------
Ron Barner at 12/9/99 2:56:29 PM

Re: your Enterprise Example #6 relating to joined tables.

The example shows a Customer Table joined to an Orders Table from the NWIND database. In this example you show how to add a new order to the Orders table.

Is it possible to change this example so that the user can add a New Customer AND a new Order at the same time, instead of having one function to add a Customer, then another function (like your example) to add an Order?

[Ron Barner]

------------
John at 12/11/99 8:44:47 PM

Hi Ron,

At present, ASP-db issues ONE SQL Update statement when you update a record.
I can't think of an SQL Update statement that modifies TWO or more tables
simultaneously. Besides that, things could get really messy if only one of
the updates failed. Should you roll back that transaction or both? Could
it even be detected, or would the last updated table set the success flag?

There may be a way with a stored procedure to accomplish what you're after.
Especially if you couple that with the template features that will be
available in the ASP-db 2000 version. Feel free to contact us and we'd be
glad to give you an estimate on what it would cost to develop a solution to
your problem. It would involve custom ASP code, and possibly a feature
addition to the component, which would obviously be at an additional charge.

Thanks,
John



------------
Ron Barner at 12/9/99 2:56:29 PM

Re: your Enterprise Example #6 relating to joined tables.

The example shows a Customer Table joined to an Orders Table from the NWIND database. In this example you show how to add a new order to the Orders table.

Is it possible to change this example so that the user can add a New Customer AND a new Order at the same time, instead of having one function to add a Customer, then another function (like your example) to add an Order?

[Ron Barner]

Thanks for the input. I know that using Forms in MS Access, I can update multiple tables. I was hoping there was a way in ASP-db to do it, but I have an easy workaround.

------------
John at 12/11/99 8:44:47 PM

Hi Ron,

At present, ASP-db issues ONE SQL Update statement when you update a record.
I can't think of an SQL Update statement that modifies TWO or more tables
simultaneously. Besides that, things could get really messy if only one of
the updates failed. Should you roll back that transaction or both? Could
it even be detected, or would the last updated table set the success flag?

There may be a way with a stored procedure to accomplish what you're after.
Especially if you couple that with the template features that will be
available in the ASP-db 2000 version. Feel free to contact us and we'd be
glad to give you an estimate on what it would cost to develop a solution to
your problem. It would involve custom ASP code, and possibly a feature
addition to the component, which would obviously be at an additional charge.

Thanks,
John



------------
Ron Barner at 12/9/99 2:56:29 PM

Re: your Enterprise Example #6 relating to joined tables.

The example shows a Customer Table joined to an Orders Table from the NWIND database. In this example you show how to add a new order to the Orders table.

Is it possible to change this example so that the user can add a New Customer AND a new Order at the same time, instead of having one function to add a Customer, then another function (like your example) to add an Order?

[Ron Barner]

Thanks for the input. I know that using Forms in MS Access, I can update multiple tables. I was hoping there was a way in ASP-db to do it, but I have an easy workaround.

------------
John at 12/11/99 8:44:47 PM

Hi Ron,

At present, ASP-db issues ONE SQL Update statement when you update a record.
I can't think of an SQL Update statement that modifies TWO or more tables
simultaneously. Besides that, things could get really messy if only one of
the updates failed. Should you roll back that transaction or both? Could
it even be detected, or would the last updated table set the success flag?

There may be a way with a stored procedure to accomplish what you're after.
Especially if you couple that with the template features that will be
available in the ASP-db 2000 version. Feel free to contact us and we'd be
glad to give you an estimate on what it would cost to develop a solution to
your problem. It would involve custom ASP code, and possibly a feature
addition to the component, which would obviously be at an additional charge.

Thanks,
John



------------
Ron Barner at 12/9/99 2:56:29 PM

Re: your Enterprise Example #6 relating to joined tables.

The example shows a Customer Table joined to an Orders Table from the NWIND database. In this example you show how to add a new order to the Orders table.

Is it possible to change this example so that the user can add a New Customer AND a new Order at the same time, instead of having one function to add a Customer, then another function (like your example) to add an Order?