G'day,
Came across an interesting one today.
I have one client who has problems inserting
a row of data into a table. Nobody else has
the problem, including another installation of
the BizMan on the same server.
The code displays a variable form then insert
data from the form into (amongst others) the
following tables, each with a one to many
relationship with the following table.
Enterprises
Org_Groups
Organisations
Departments
DeptContNumbers
The code gets the most recent ID, inserts the
row, gets the ID for the inserted row, compares
the two and if they are the same reports an error
and aborts the rest of the procedure.
It has done this to her on several occasions and
I have done the same data entry on my machine and
not found a problem.
When it does this it is the Organisation row that
fails to insert.
One can then go into the Enterprise form then to the
Og_Groups form, try to delete the Org_Group record
and receive an error message saying that the record
cannot be deleted as related records exist.
Trouble is, the only link to the PK in Org_Groups is
from Organisations and there are no Organisation
records visible in the table linked to Org_Groups.
After a reload, R:BASE permits the Org_Group to be
deleted. Anybody have a clue on why the record
would be reported as not inserted, invisible to
observation yet prevent the deletion of a parent
record?
I observed this manifestation myself so am not
relying on user observation.
Here is the code:
$COMMAND
EnterEnt
-- Creates new rows for enterprise, org group, org, depatments, address and
-- contact numbers from one data entry screen
-- Called by:
-- QuoteNew in FR.apx
-- JNOrgNew in Jobs.apx
-- OMMnMenu in OrgsMenu.apx
-- OMMenuM in OrgsMenu.apx
-- MMOrg0 in MenuMain.apx
*( Requires var
vUseTelFax
Returns var
vNewOrgID
)
-- Created by Tom Grimshaw
-- Modification history
-- 29-03-1998
-- 03-06-1998 Removed variable setting code to own block
-- Added three new variables
-- 04-06-1998 Added variable checking to abort if matching org found
-- 10-10-1998 Added carriage return and line break to screen message
-- 03-11-1998 Added new column OrgCode
-- 22-04-1999 Altered window size for Enterprise
-- 07-07-1999 Added mobile number option and IF statements for Dept #
-- 04-09-1999 Added ability to discard data after entering it
-- Added ability to enter new person after entering org
-- 22-01-2000 Added UseTelFax check to use existing var if set
-- 04-02-2000 Added section to set them to null and corrected error in var
-- 05-03-2000 Added ability to edit full org data after entering new org
-- 26-06-2000 tlg Added TFN and EntityType
-- 30-06-2000 tlg Added User ID and entry date to address record
-- 29-08-2000 tlg Added three new columns, removed add prsn to own block
-- 19-11-2000 tlg Added vNewPromoID and vNewSource
-- 13-12-2000 tlg Added update of billing and shipping address IDs
-- 12-08-2001 tlg Altered var names for org form
--
SET VAR vQuit TEXT = 'N'
SELECT ParameterValue INTO +
vNewOrgScreen IND vi1 +
FROM SiteValues +
WHERE SVID = 134
RUN VarNwOrg IN OrgsMenu.apx
IF vUseTelFax = 'Y' THEN
SET VAR vNewContNumb TEXT = .vNewJobTel
SET VAR vNewContNumb1 TEXT = .vNewJobFax
SET VAR vNewEntName TEXT = .vNewJobOrgName
SET VAR vNewShortOrgName TEXT = .vNewJobOrgName
ELSE
SET VAR vNewContNumb1 TEXT = NULL
SET VAR vNewContNumb TEXT = NULL
SET VAR vNewEntName TEXT = NULL
SET VAR vNewShortOrgName TEXT = NULL
ENDIF
CLEAR VAR MICRORIM_NOSTATUS
SET VAR vScrnMsgChoice TEXT = 'N'
EDIT USING &vNewOrgScreen +
AT 0,0,798,578 +
CAPTION 'New Organisation'
IF vScrnMsgChoice = 'N' THEN
SET VAR vScreenMsg TEXT = +
'Click [Yes] to confirm discard of data entry for new organisation.|+
Click [No] to drop the data into the organisation tables:'
RUN SM2YesNo IN GlobBlok.apx
IF vScrnMsgChoice = 'Y' THEN
GOTO EndSct11
ENDIF
ENDIF
SELECT (MAX(Ent_ID)) INTO +
vNewEnt_ID1 INDICATOR vi1 +
FROM Enterprises
INSERT INTO Enterprises (Ent_Name) +
VALUES (.vNewEntName)
SELECT Ent_ID INTO +
vNewEnt_ID INDICATOR vi1 +
FROM Enterprises +
WHERE COUNT=INSERT
IF vNewEnt_ID1 = .vNewEnt_ID THEN
SET VAR vScreenMsg TEXT = 'No new enterprise entered.'
RUN ScrMsg1S IN GlobBlok.apx
GOTO EndSct11
ENDIF
SELECT (MAX(Group_ID)) INTO +
vNewGroup_ID1 INDICATOR vi2 +
FROM Org_Groups
INSERT INTO Org_Groups (Ent_ID,GroupName) +
VALUES (.vNewEnt_ID,.vNewEntName)
SELECT Group_ID INTO +
vNewGroup_ID INDICATOR vi2 +
FROM Org_Groups +
WHERE COUNT=INSERT
IF vNewGroup_ID1 = .vNewGroup_ID THEN
SET VAR vScreenMsg TEXT = 'No new org group entered.'
RUN ScrMsg1S IN GlobBlok.apx
GOTO EndSct11
ENDIF
SELECT (MAX(Org_ID)) INTO +
vNewOrgID1 INDICATOR vi3 +
FROM Organisations
INSERT INTO Organisations (Group_ID,Org_Name, +
Org_NameS,OrgStatus,CustCat,NewBusDP, +
NextServ,Market,IntroBy,ServBy, +
CreditRating,CreditLimit,PriceLevel,TaxExemptNumb, +
OrgCode,InvDiscount,Source,PromotionID, +
EntityType,InvPayTermID) +
VALUES (.vNewGroup_ID,.vNewEntName, +
.vNewShortOrgName,.vNewOrgStatus,.vNewCustCat,.vNewNewBusDP, +
.vNewNextServ,.vNewMarket,.vNewIntroBy,.vNewServBy, +
.vNewCreditRating,.vNewCreditLimit,.vNewPriceLevel,.vNewTaxExempt, +
.vNewOrgCode,.vNewInvDisc,.vNewSource,.vNewPromoID, +
.vNewEntityType,.vNewInvTerms)
SELECT Org_ID INTO +
vNewOrgID INDICATOR vi3 +
FROM Organisations +
WHERE COUNT=INSERT
IF vNewOrgID1 = .vNewOrgID THEN
SET VAR vScreenMsg TEXT = 'No new org entered.'
RUN ScrMsg1S IN GlobBlok.apx
GOTO EndSct11
ENDIF
SELECT (MAX(Dept_ID)) INTO +
vNewDept_ID1 INDICATOR vi4 +
FROM Departments
INSERT INTO Departments (Org_ID,Dept_Name) +
VALUES (.vNewOrgID,.vNewEntName)
SELECT Dept_ID INTO +
vNewDept_ID INDICATOR vi4 +
FROM Departments +
WHERE COUNT=INSERT
IF vNewDept_ID1 = .vNewDept_ID THEN
SET VAR vScreenMsg TEXT = 'No new department entered.'
RUN ScrMsg1S IN GlobBlok.apx
GOTO EndSct11
ENDIF
IF vNewContNumb IS NOT NULL THEN
INSERT INTO DeptContNumbers (Dept_ID,ContType,ContNumb) +
VALUES (.vNewDept_ID,.vNewContType,.vNewContNumb)
ENDIF
IF vNewContNumb1 IS NOT NULL THEN
INSERT INTO DeptContNumbers (Dept_ID,ContType,ContNumb) +
VALUES (.vNewDept_ID,.vNewContType1,.vNewContNumb1)
ENDIF
IF vNewContNumb2 IS NOT NULL THEN
INSERT INTO DeptContNumbers (Dept_ID,ContType,ContNumb) +
VALUES (.vNewDept_ID,.vNewContType2,.vNewContNumb2)
ENDIF
IF vNewEMail IS NOT NULL THEN
INSERT INTO DeptContNumbers (Dept_ID,ContType,ContNumb) +
VALUES (.vNewDept_ID,'email',.vNewEMail)
ENDIF
INSERT INTO Addresses (EntryDate,Org_ID, +
AddressType,Mail_To,Address1,Address2, +
Suburb,StateCounty,PostCode_,Country, +
AddressNotes,PersNumb) +
VALUES (.#DATE,.vNewOrgID, +
.vNewAddressType,.vNewMail_To,.vNewAddress1,.vNewAddress2, +
.vNewSuburb,.vNewStateCounty,.vNewPostCode_,.vNewCountry, +
.vNewAddressNotes,.vUserID)
SELECT AddressID INTO +
vAddressID INDICATOR vi4 +
FROM Addresses +
WHERE COUNT=INSERT
UPDATE Organisations SET +
BillAddress = .vAddressID, +
ShipAddress = .vAddressID +
WHERE Org_ID = .vNewOrgID
SET VAR vOrg_ID_O INTEGER = .vNewOrgID
IF vNewTFN1 IS NOT NULL THEN
SET VAR vNewTFN TEXT = 'Y'
RUN TFNStorO IN Security.apx
ENDIF
IF vNewTFN2 IS NOT NULL THEN
SET VAR vNewTFN TEXT = 'Y'
RUN TFNStorO IN Security.apx
ENDIF
-- Display org form so user can add additional addresses etc
CLEAR VAR MICRORIM_NOSTATUS
SET VAR vOrgFrmWhere TEXT = ('WHERE Org_ID = ' + (CTXT(.vOrg_ID_O)))
SET VAR vOrgFrmOrderBy TEXT = 'ORDER BY Org_ID'
SET VAR vOrgFrmCaption TEXT = 'New Org Record'
RUN OrgForm IN OrgsMenu.apx
LABEL ReCheck
SELECT TextData INTO +
vScreenMsg IND vi1 +
FROM TextBlocks +
WHERE TextBlockID = 11
SET VAR vScreenMsg TEXT = (SRPL(.vScreenMsg,'|',(CHAR(13) + CHAR(10)),0))
SET VAR vScrnMsgChoice TEXT = 'Q'
SET VAR MICRORIM_NOSTATUS INTEGER
EDIT USING ScrnMsgNewOrg +
AT 35,52,762,545 +
CAPTION 'Message'
CLEAR VAR MICRORIM_NOSTATUS,vScreenMsg
IF vScrnMsgChoice = 'Q' THEN
GOTO EndSct11
ENDIF
IF vScrnMsgChoice = 'N' THEN
EDIT USING Enterprise +
WHERE Ent_ID = .vNewEnt_ID +
AT 126,30,674,570 +
CAPTION 'Enterprise Data'
GOTO ReCheck
ENDIF
IF vScrnMsgChoice = 'Y' THEN
SET VAR vOrg_ID_O INTEGER = .vNewOrgID
RUN AddPrsnO IN OrgsMenu.apx
ENDIF
LABEL EndSct11
CLEAR VAR vNewContType,vNewContNumb,vNewAddress1,vNewAddress2,vNewSuburb, +
vNewStateCounty,vNewPostCode_,vNewCountry,vNewAddressType,vNewMail_To, +
vNewEnt_ID,vNewEnt_ID1,vNewEntName,vNewDept_ID,vNewGroup_ID,vEnterDiff, +
vShortOrgName,vNewPriceLevel,vScreenMsgChoice,vNewTFN1,vNewTFN2
RETURN
At 07:04 03/06/02 -0500, you wrote:
>I recently inserted into a table with autonum and was able to put in my own
>values. I think that limitation has gone away since 6.5.
>
>ALWAYS put NOT NULL and UNIQUE constraints on an autonum column, or make it
>a PRIMARY KEY. That will eliminate the possibility of putting
>bogus/duplicate values in.
>
>-- Dennis McGrath
>mailto:[EMAIL PROTECTED]
>
>-- Productivity Tools for R:Base Programmers
>http://www.enteract.com/~mcgrath/dennis
>
>-- Full time consultant with:
>SQL Resources Group
>Steve Hartmann
>Oak Park, IL
>mailto:[EMAIL PROTECTED]
>
>
>-----Original Message-----
>From: Scott Salisbury [mailto:[EMAIL PROTECTED]]
>Sent: Sunday, June 02, 2002 8:22 PM
>To: rStreet List Server
>Subject: [rStreet] AUTONUM BUG???
>
>
>R:BASE seems to handle AUTONUM rows
>in a very inconsistent way.
>
>1. You can issue an UPDATE against an AUTONUM
> column and it will change the value. It will even change
> the value to a value that is already present in the table
> in another row.
>
>2. You *cannot* issue an INSERT that contains an
> AUTONUM column or you will get an "Incorrect
> Number of Values" error message.
>
>Does anyone else see this as being inconsistent?
>In MySQL, you can both INSERT and UPDATE
>an AUTONUM column. This seems to be more
>consistent.
>
>Either you have control over the AUTONUM column
>or you do not. It seems R:BASE rides the fence on
>this particular issue, however.
>
>Scott
>====
>Scott J. Salisbury
>Matrix Data Systems / The R:Street Journal
>5114 Point Fosdick Drive NW, Suite E-272
>Gig Harbor, WA 98335-1733
>
>mailto:[EMAIL PROTECTED]
>http://www.rStreet.Com
>Direct Line: 206-423-7679
>Toll Free: 888-386-5040
>Seattle: 206-448-7975
>Tacoma: 253-444-5166
>Fax: 305-425-0491
>
>
>
>
>The rStreet List is dedicated to database application development,
>R:BASE, PHP, Visual Basic etc...
>
>All products mentioned on this list are the trademarks of their resepective
>companies.
>
>Post: [EMAIL PROTECTED]
>Unsubscribe: [EMAIL PROTECTED]
>http://groups.yahoo.com/group/rStreet/
>
>Scott Salisbury - [EMAIL PROTECTED]
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>
>
>------------------------ Yahoo! Groups Sponsor ---------------------~-->
>Tied to your PC? Cut Loose and
>Stay connected with Yahoo! Mobile
>http://us.click.yahoo.com/QBCcSD/o1CEAA/sXBHAA/PhFolB/TM
>---------------------------------------------------------------------~->
>
>The rStreet List is dedicated to database application development,
>R:BASE, PHP, Visual Basic etc...
>
>All products mentioned on this list are the trademarks of their
>resepective companies.
>
>Post: [EMAIL PROTECTED]
>Unsubscribe: [EMAIL PROTECTED]
>http://groups.yahoo.com/group/rStreet/
>
>Scott Salisbury - [EMAIL PROTECTED]
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Warmest regards,
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
"... the control of impulse -- is the first principle of civilization."--
Will Durant,
Pulitzer Prize winning philosopher, writer and historian
the most needed product in the world can be found at
www.thewaytohappiness.org
This email and any files transmitted with it are confidential to the
intended recipient and may be privileged. If you have received this email
inadvertently or you are not the intended recipient, you may not
disseminate, distribute, copy or in any way rely on it. Further, you should
notify the sender immediately and delete the email from your computer.
Whilst we have taken precautions to alert us to the presence of computer
viruses, we cannot guarantee that this email and any files transmitted with
it are free from such viruses.
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
