Don_S
Posts: 50
|
| Posted: 05/06/2005, 12:27 PM |
|
I am trying to add a record to a SQL Server query and no matter how I try to configure it I keep getting the following error? What am I doing wrong?
[Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'qry_Misc_Info' is not updatable because the modification affects multiple base tables. (Microsoft OLE DB Provider for ODBC Drivers)
I need to add information to 2 tables:
"Entry_ID" and 'Info"
Entry_ID has the following fields:
Entry_ID, PK, int, indexed, Autonumber
User_ID
Date
Time
Entry_Type
Info has the following fields:
Entry_ID, FK, int, indexed
Entry_Name
Description
So you can see how the tables are related...
How do I make this work? I am just starting to use CodeCharge, migrating from Access.
Thanks,
Don
|
 |
 |
peterr
Posts: 5971
|
| Posted: 05/06/2005, 12:46 PM |
|
You can only update one table at a time, thus you would need to use the "Custom Insert" and "Custom Update" properties to specify which table to update.
Especially in your case you need to insert one record first, then find out what it's key was, then use that key to create a record in your 2nd table. So it cannot be done all at once. You can use the "After Insert" and "After Update" events to do this. Various examples are in CCS Example Pack, especially the many-to-many examples. Mult-step User Registration example also shows how to retrieve the last inserted key.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 05/06/2005, 3:19 PM |
|
Actually I just realized that since you're using SQL Server you may be able to use SQL Triggers to update your other table(s). This way you would use the database functionality without a need for updating multiple tables programmatically. Should be more efficient as well.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
