| Author |
 Topic Search  Topic Options
|
Joeee 
Newbie
Joined: 05 August 2003
Status: Offline
Points: 33
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Merging 2 databases... tblAuthor
Posted: 07 August 2003 at 12:52pm |
Hi everyone,
I'm trying to figure out the best way to merge Web Wiz Forums into my existing auction application. The skins work great... however, I don't want to make my users register twice. What's the best (and easiest) way to merge my 2 "user" tables? I'm currently considering the easy way out... adding insert/update triggers to each table. Is there a better way? Anyone have experience with this? Are there any add-ons that could help with this?
Thanks
Edited by Joeee
|
 |
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 August 2003 at 3:45pm |
|
What I did was:
remove the identity from the Author_ID field
delete the duplicate fields in tlbAuthor
change the name of the table to tlbAuthorA
created a view joining the deleted fields from my member table with tblAuthorA (the newly joined fields should have the same name as the tblAuthor fields that were deleted)
I changed the default values in tblAuthorA so that new members are automatically added to my default group and so that all field that did not accept null had a default value.
insert the new Author_ID for each new Member added to you member's table.
There were other changes, but this should get you on your way
|
|
|
|
Joeee
Newbie
Joined: 05 August 2003
Status: Offline
Points: 33
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 August 2003 at 3:53pm |
|
Nice! That sounds like a much more elegant solution than the trigger idea. Thanks! Did you have to do a lot of work on the ASP side of things, or was this a 100% database solution?
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 August 2003 at 3:59pm |
|
Most of the changes are pure database changes.
There are a few ASP changes, but mostly on the register.asp as it is used for updates to the tblAuthor table. You can not update a view, so you will have to alter it a bit.
|
|
|
|
Joeee
Newbie
Joined: 05 August 2003
Status: Offline
Points: 33
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 August 2003 at 7:08pm |
Thanks! The view worked perfectly, but I'm having trouble understanding the register.asp. I've never seen ASP database calls like .Update and .Fields ??? I did a keyword search and can't even find the insert statement or reference to tblAuthor!? It doesn't look like it's calling a stored procedure to do the insert, so I'm kind of lost. Help? 
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 August 2003 at 7:15pm |
|
The .Update is updating the recordset defined by the SQL statement. It updates the fields from the .Fields. If you change the SQL statement to call the tblAuthorA table and remove the updates to the fields that no longer exist in the table then that should work.
|
|
|
|
Joeee
Newbie
Joined: 05 August 2003
Status: Offline
Points: 33
|
Post Options
Thanks(0)
Quote Reply
Posted: 08 August 2003 at 11:29am |
OK. This is driving me crazy. You're right on with the multiple updates problem, but now I've changed every single reference to the tblAuthor table (in register.asp) to tblAuthorB and I'm still getting this error!
SELECT tblAuthorB.Username FROM tblAuthorB WHERE tblAuthorB.Username = '4444';
Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot insert or update columns from multiple tables.
/discuss/forum/register.asp, line 821
Edited by Joeee
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 08 August 2003 at 11:46am |
|
That error is caused because it is trying to update the view. I'm assuming that you will not be using this page from registration, so just comment out the .Update and write and execute your own update.
|
|
|
|
