Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Nickel Elite Contributor
Posts: 107
Registered: ‎03-09-2011
Accepted Solution

Can not insert data on remote database custom table

I have added a custom table to hold account Territory assignments.

 

This is a one to one relationship with the Account Table

 

In Code I look for an account territory assignment record for the current account

If there is no account territory assignment record I Insert one with default values based on a set of defaults.

 

I have similiar code on the Add New Account as well.

 

This works great for all users logging into and using the network installation.

 

However on remote clients, users can not insert into the new custom table.

 

Script Error message box is displayed

An error occured executing active form script (Account:TerritoryAssignment).

Error in include script (SYSTEMSmiley FrustratedLX DATABASE SUPPORT):

The statement has been terminated.

Cannot insert duplicate key row in object 'sysdba.ACCOUNTTERRITORYASSIGNMENT' with unique index 'ACCOUNTTERRITORYASSIGNMENT_PRIMARY'. The duplicate key value is (accountid) at line 255, char 10.

 

As stated this works fine on the normal network install.  Just a remote user(I'm not sure if it is specific to this user or all remote users.

 

FYI, there are no records for the offending accountid in the child table.

 

I have gone so far as to drop and recreate the custom table ensuring that the "Enable Field Level security" and Syc this Table flags are set to true and the  Sync option is set to "Either Direction"

 

I have a copy of the remote database installed locally.  I can duplicate the beahvior.  Stepping thorough the code is a pain as I can't easily set a break point.

 

 

Any suggestions on how to proceed?

 

I'm stumped.

 

 

 

 

Highlighted
Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Can not insert data on remote database custom table

Can you post the bits of code that actually gets (finds) & sets the new ID. Basically, the index 'ACCOUNTTERRITORYASSIGNMENT_PRIMARY' is set to disallow duplicate primary IDs. This is correct. But, if you are somehow getting an ID that already exists then the statement will fail as expected. Doesn't explain why normal users work - but, until we can see that part - shooting in the dark.
Highlighted
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Can not insert data on remote database custom table

Why bother to do a 1:1 - just add the fields to the OOTB Account table and all your problems go away.

 

This is how most of us have been doing it since the release of Scorpion (v7.0) - avoid 1:1 big time!

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Highlighted
Nickel Elite Contributor
Posts: 107
Registered: ‎03-09-2011

Re: Can not insert data on remote database custom table

I'll try to post only the relevent code.

 

ta is a form level variable for my TerritoryAssignment Class

 

From onchange in ACCOUNT: TerritoryAssignment form

Sub AXFormChange(Sender)
    bIsSaved = True
    ta.AccountID = Application.BasicFunctions.CurrentAccountID
    blnIsOverride = chkIsOverride.Checked
'IF NO ACCOUNT MANAGER IS IS ASSIGNED, THEN SET THE DEFAULTS If txtAcctMgrID.Text = "" Then 'get the default value from the account address
'THIS CALL IS WHERE THE TROUBLE BEGINS ta.AssignDefaultValues Form.Refresh End If Dim objRS, strSQL Set objRS = objSLXDB.GetNewRecordSet 'set the salesassociate If txtSalesAssociateID.Text = " " Or txtSalesAssociateID.Text = "" Then gStrSalesAssocText = "" ppeSalesAssoc.Text = gStrSalesAssocText Else strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtSalesAssociateID.Text) & "'" 'TL Downing objRS.Open strSQL, objSLXDB.Connection If Not (objRS.BOF And objRS.EOF) Then ppeSalesAssoc.Text = objRS.Fields("USERNAME").Value Else ppeSalesAssoc.Text = "" End If objRS.Close gStrSalesAssocText = ppeSalesAssoc.Text 'The Lookup ID for the Sales Associate End If strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtAcctMgrID.Text) & "'" 'TL Downing objRS.Open strSQL, objSLXDB.Connection If Not (objRS.BOF And objRS.EOF) Then ppeAcctMgr.Text = objRS.Fields("USERNAME").Value Else 'get the de ppeAcctMgr.Text = "" End If strAcctMgrID = txtAcctMgrID.Text strOwnerID = lueOwner.LookupID strReason = memOverrideReason.Text strSalesAssocID = txtSalesAssociateID.Text SetDefaultOverride () 'set the warning label visibility property ShowHideWarning End Sub

ta.AssignDefaultValues

 

    Public Sub AssignDefaultValues
        If blnDefaultValuesSet = False Then
            SetDefaultValues 'this is working
        End If
        ValidateAccountManagerID 'this is working
        If strAccountID = "" Then 'there is nothing to assign yet
            Exit Sub
        End If
        Dim objRS, SQL
'check to see if an account level territory assignment record exists
        SQL = "SELECT * FROM sysdba.ACCOUNTTERRITORYASSIGNMENT WHERE ACCOUNTID = '" & strAccountID &"'"
        Set objRS = objSLXDB.GetNewRecordset
        objRS.Open SQL, objSLXDB.Connection
'if no assignment, then If objRS.RecordCount = 0 Then 'insert the accountterritotyassignment record InsertDefaultAccountTerritoryAssignment 'This is the code that is failing ElseIf objRS.RecordCount = 1 Then 'update the accountterritotyassignment record UpdateDefaultAccountTerritoryAssignment 'ElseIf objRS.RecordCount > 1 Then 'we have an error there should be 1 and only 1 account territoryassignment record per account, this is a 1 to 1 relationship 'update the accountterritotyassignment record End If Set objRS = Nothing End Sub

 InsertDefaultAssignment, this is the code that throws the error

    Public Sub InsertDefaultAccountTerritoryAssignment
        Dim SQL

        SQL = "INSERT INTO sysdba.ACCOUNTTERRITORYASSIGNMENT (ACCOUNTID, ACCTMGRID, OVERRIDEREASON, " & _
        "SECCODEID, ISOVERRIDE, SALESASSOCIATEID" & _
        ") VALUES ('" & strAccountID & "', '" & strDefaultAccountManagerID & "'," & _
        "'','" & strDefaultOwnerId & "','F','" & strSalesAccociateID &"')"

        objSLXDB.ExecuteSQL SQL

    End Sub

 

Highlighted
Nickel Elite Contributor
Posts: 107
Registered: ‎03-09-2011

Re: Can not insert data on remote database custom table

<rant>

 

Because the powers that be would like to perform the following

 

Have the ability to override the account manager and owner (And be able to tell when different thatn the defaults"

 

we also have 2 sales associates 1 at the account level and 1 at the territory level.

 

so now I have 3 account manager fields, three owner fields and 2 sales associates fileds sometimes with differetn data.  This makes appending the account table rather tedious.  I did give it some thought.

 

Sometimes we have to do what we are told.....

 

whether we like it or not.  

 

Personally, I'd do away with all of the silly territory assignment customizations if I could. Major effort with little return on investment (if any).  But peole that get paid a lot more than I do are making the call here, so I just try to implement as painlessly as possible

 

<rant/>

all better.

Highlighted
Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Can not insert data on remote database custom table

Ah. You can't use the OnChange for the form - it *will* fire multiple times (always has) - so your insert will repeatedly fire. Also, try to avoid direct SQL inserts.

 

Use this instead:

 

===================
Inserting a record 
===================

    Dim objSLXDB, objRS, strSQL
    Set objSLXDB = New SLX_DB

       Set objRS = objSLXDB.GetNewRecordSet
       strSQL = "SELECT * FROM TABLE WHERE 1=0" ' Setup RS

       objRS.Open strSQL, objSLXDB.Connection
       objRS.AddNew

          With objRS
                  .Fields("CONTACTID").Value = ConID
                  .Fields("Q1").Value = "F"
                  .Update
                  If ErrorCheck (Application.Translator.Localize("Error updating...")) > 0 Then exit sub
		  .Close
          End With

       Set objRS = Nothing

 It's easier to read and edit later.

 

Do you know what index the primary is failing with - as any of the these values could be duplicated.

 

    Public Sub InsertDefaultAccountTerritoryAssignment
        Dim SQL

        SQL = "INSERT INTO sysdba.ACCOUNTTERRITORYASSIGNMENT (ACCOUNTID, ACCTMGRID, OVERRIDEREASON, " & _
        "SECCODEID, ISOVERRIDE, SALESASSOCIATEID" & _
        ") VALUES ('" & strAccountID & "', '" & strDefaultAccountManagerID & "'," & _
        "'','" & strDefaultOwnerId & "','F','" & strSalesAccociateID &"')"

        objSLXDB.ExecuteSQL SQL

    End Sub

 But, this needs re-wrting anyway - to use above method.

 

Finally, if you want to continue AxFormChange (you shouldn't) then wrap it all with

 

If Not frmName.IsReading Then

End If

 This will ensure that the multiple fires will be avoided and your code executes only after everything else is loaded/bound.

 

 

 

Highlighted
Nickel Elite Contributor
Posts: 107
Registered: ‎03-09-2011

Re: Can not insert data on remote database custom table

Thanks, 

 

I'll refactor as you describe.

 

I understand your caution re: not using OnChange.

 

But when the user changes to the next record, I need to populate and check things (Like does the Account Territory Assignment match what is actually on the account?).

 

What should I use?

 

 

 

Highlighted
Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Can not insert data on remote database custom table

Some other hints. Use the SLX Database include - then you can re-write a lot of this code:

 

 strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtSalesAssociateID.Text) & "'"  'TL Downing

 Becomes:

 

uName = GetField("IsNull(USERNAME,'')","USERINFO","USERID='" &  txtSalesAssociateID & "'")

 No need to get/set RS - easier to work with.

 

Also:

 

    If txtSalesAssociateID.Text = "            " Or txtSalesAssociateID.Text = "" Then

Simpler as:

If trim(txtSalesAssociateID) = ""

It'll be null anyway if you use the other code.

 

 

    If txtSalesAssociateID.Text = "            " Or txtSalesAssociateID.Text = "" Then
        gStrSalesAssocText = ""
        ppeSalesAssoc.Text = gStrSalesAssocText
    Else
        strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtSalesAssociateID.Text) & "'"  'TL Downing

Highlighted
Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Can not insert data on remote database custom table

OnChange is fine for things like that (to setup the form, data etc). Just try to avoid using updates within it. Use BeforeRecordChange or axFormValidate to update it after the event, not during.
Highlighted
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Can not insert data on remote database custom table

You wrote "... This makes appending the account table rather tedious  .."

 

Appending the account table is simply using the SalesLogix DB Manager and adding the fields. The tedious part is having to write special code to handle the 1:1 business.

 

NOTE: If you use the web app.. or SData for the mobility.. 1:1 complicates things.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html