Zugg Software :: View topic

Beginner Joined: 19 Jul 2007 Posts: 18

I have a database that is tied back to my mapper. I have the following alias which is used to add rooms to the mapper database.
I am trying to figure out how to write another alias that will allow me to edit or delete an entry into that database. For example if there was a typo when the room was added to the database. There are also cases where I have deleted older zones that no longer exist, but the mapper reused those vnums and I cannot add the newer zones to the database because the vnums are already in use by the old zone in the database.

Alias to add a room to the database: (this alias works well).
roomtof=%trim( %concat( "%1 ", "%2 ", "%3 ", "%4 ", "%5 ", "%6 ", "%7 ", "%8", "%9"))
#VAR DB2 %comcreate( "ADODB.Recordset")
#CALL @DB2.Open( INSERT INTO Rooms ~(ObjId~, Name~) VALUES ~(%roomvnum~,~'%replace( @roomtof, "'", "''")~'~), @MDBConnStr2)
#SAY %ansi( green)Room %ansi( bright, cyan)@roomtof %ansi( green)added %ansi( bright, cyan)%roomvnum

This is what I tried to use as a seperate alias in hopes of editing or replacing a database entry.
roomtof=%trim( %concat( "%1 ", "%2 ", "%3 ", "%4 ", "%5 ", "%6 ", "%7 ", "%8", "%9"))
#VAR DB2 %comcreate( "ADODB.Recordset")
#CALL @DB2.Open( UPDATE Rooms SET Name~=~'%replace( @roomtof, "'", "''")~'~) WHERE ObjId~=%roomvnum~), @MDBConnStr2)
#SAY %ansi( green)Room %ansi( bright, cyan)@roomtof %ansi( green)modified at vnum %ansi( bright, cyan)%roomvnum

But i get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again

Have also tried:
#CALL @DB2.Open( UPDATE Rooms ~(ObjId~, Name~) VALUES ~(%roomvnum~,~'%replace( @roomtof, "'", "''")~'~), @MDBConnStr2)

But I get syntax errors.

I really don't have much knowledge/experience with database coding so I could really use some help here.
Basically, I would like to do one of 2 things, either completely delete an old db entry so I can reuse it, or have the ability to overwrite or replace with new data.

Any help would be much appreciated.