Problem updating lookups in web .net

Discussions related to order tracking development with the ProForm module.

Moderator: Phil Barton

john.morton
Posts: 89
Joined: Wed Nov 16, 2011 11:51 am

Re: Problem updating lookups in web .net

Post by john.morton » Fri Oct 26, 2012 10:42 am

Unfortunately, this appears to be something we don't directly support. We will support this behavior much more directly with the 3.0 release of the application.

That being said, it does appear that the only thing preventing this from working is the inability of the web application to find the necessary files on the local OS. The folders/files I'm referring to are located, in a default installation, here: "C:\Program Files (x86)\SoftPro\Select\Data." All of the sub files/folders under this directory are also required to be located in a similar path starting from the executing assembly. We use the following call to locate this directory:

Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase.Substring(@"file://\".Length))

When I tried, as a test, to see what this path would give me in my test application (lookupweb), it returned:
"C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\lookupweb\67af2aad\3e098127"

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: Problem updating lookups in web .net

Post by czentman » Tue Oct 30, 2012 1:45 pm

Can you test putting the files in your directory and see if that works? I find it so strange that the problem would be where the files are located since getting the data from the lookup works fine, it's only the setting the data in the lookup.

john.morton
Posts: 89
Joined: Wed Nov 16, 2011 11:51 am

Re: Problem updating lookups in web .net

Post by john.morton » Tue Oct 30, 2012 2:20 pm

I did try that before my last response on this thread, but it didn't work. At that point I assumed it was a combination of both not being able to locate those file AND not having my test web application setup in such a way that it would have permissions to access local files like that.

It makes sense to not get that error until you actually try and update. Lookups doesn't use "data item IDs" for its data. You can retrieve all the data from a lookup table without ever having to touch those files. As soon as you do any update though (any updates to an Order would also hit an error like this) it would bomb.

There's something another customer is trying that might also work for you. Again, at a basic level, writing web applications directly against our API is something we don't seem to directly support. So, what you could do is basically write a wrapper application to do the work against our API locally, and then have your web application talk to that wrapper application.

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: Problem updating lookups in web .net

Post by czentman » Mon Nov 12, 2012 4:00 pm

I created a console application. All it does is update one field in one lookup table. I still get an error but a different one "Failed to load DataItemDescription"

I copied the data folder from c:\program files (x86)\softpro\select to 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\' where I put this console application.

This is the code for my console app.

Imports SoftPro.Select.Client.SelectServer
Imports SoftPro.OrderTracking.Common
Imports SoftPro.OrderTracking.Client
Imports System.Reflection
Imports System.IO

Module Module1
Public table As ILookupTable
'connect using the select server object
Public sps As New SoftPro.Select.Client.SelectServer("http://spapp:8000") ', credentials)

Public spec As New LookupQuerySpec()

Public lookups As Lookups = sps.GetService(Of Lookups)()

Sub Main()
Dim strSQL As String
Dim item As ILookupValue
Dim row As ILookupRow

On Error Resume Next
Dim str As String = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase.Substring("UpdatesInSelect://\".Length))

sps.EnsureAuthenticated()
spec.Filter = "[LookupCode] like '%%'"
spec.Table = "AbstractorTesting"
spec.SchemaOnly = False

On Error Resume Next

Dim table As ILookupTable = lookups.QueryTable(spec)

For x = 0 To table.Rows.Count - 1
row = table.Rows(x)
row.Item("LookupCode") = row.NewValue(False, "TEST--" & x) 'this worked

Next

lookups.ApplyChanges(table)

End Sub

End Module


The following is the output. I don't get errors on the debug but it doesn't update.

'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Security.Cache.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Common.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.BusinessObjects.Base.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Administration.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Security.Classes.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'vdyp3sym'
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.BusinessRules.Base.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Common.Classes.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 'Lookup Table Fetch Thread' (0x1418) has exited with code 0 (0x0).
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'Anonymously Hosted DynamicMethods Assembly'
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.DebuggerVisualizers\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.DebuggerVisualizers.dll'
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.BusinessObjects.Core.dll'
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.Documents.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'UpdatesInSelect.vshost.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files (x86)\SoftPro\Select\UpdatesInSelect\bin\Debug\SoftPro.DocEngine.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
UpdatesInSelect.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
A first chance exception of type 'SoftPro.OrderTracking.Common.LookupException' occurred in SoftPro.OrderTracking.Client.dll
The thread 'vshost.RunParkingWindow' (0x11ac) has exited with code 0 (0x0).
The thread '<No Name>' (0x1638) has exited with code 0 (0x0).
The program '[6792] UpdatesInSelect.vshost.exe: Managed (v2.0.50727)' has exited with code 0 (0x0).

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: Problem updating lookups in web .net

Post by czentman » Mon Nov 12, 2012 4:01 pm

Also, after I copied the whole data folder into the bin folder in the .net directory that I'm working, i get this:

table.overlaycontext:Abstractor
LookupCode : Value cannot be null. Parameter name: source : ilkupcol.overlaypath:.LookupCode ilkupcol.kind:0 ilkupcol.size:255
test## : Value cannot be null. Parameter name: source : ilkupcol.overlaypath:.test## ilkupcol.kind:0 ilkupcol.size:255
PayeeName : Value cannot be null. Parameter name: source : ilkupcol.overlaypath:.PayeeName ilkupcol.kind:0 ilkupcol.size:255
FirstName## : Value cannot be null. Parameter name: source : ilkupcol.overlaypath:.FirstName## ilkupcol.kind:0 ilkupcol.size:255

for not the context error. But the update doesn't work.

john.morton
Posts: 89
Joined: Wed Nov 16, 2011 11:51 am

Re: Problem updating lookups in web .net

Post by john.morton » Mon Nov 12, 2012 5:00 pm

Just to make sure we're on the same page in regards to the files that need to be places in a directory relative to your executable:

The "Data" directory needs to be in the same directory that your executable will be running. As an example, I've written a small sample application to try and reproduce your issues, and when I run that application in debug mode from visual studio, it runs here:
C:\Users\jmorton\Documents\Visual Studio 2008\Projects\LookupTest\LookupTest\bin\x86\Debug
I have a copy of the entire Data directory from my Select installation located here:
C:\Users\jmorton\Documents\Visual Studio 2008\Projects\LookupTest\LookupTest\bin\x86\Debug\Data
C:\Users\jmorton\Documents\Visual Studio 2008\Projects\LookupTest\LookupTest\bin\x86\Debug\Data\ManagerObjectMaps
C:\Users\jmorton\Documents\Visual Studio 2008\Projects\LookupTest\LookupTest\bin\x86\Debug\Data\RuleInstanceDefs
Each of those directories should contain several files including: CoreBusinessRules.xml, DataItemIDs.xml, RuleInstanceDef.xsd, and many others.

Is this how you're currently set up?

As I mentioned above, I wrote a sample application trying to do the same thing you are doing. There are several differences though:
My application is written in C#, even though that shouldn't matter, I'm going to rewrite my little tool in VB as well.
I'm referencing the default built-in Abstractor lookup table, so I know what the requirements are regarding null fields and such.
Also, in the default table, which I'm referencing, I'm referring to the "Lookup Code" field instead of the "LookupCode" field. If this was your issue though, I would expect you to get an exception much sooner.

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Reflection;
using SoftPro.OrderTracking.Client;
using SoftPro.ClientModel.Proxies;
using SoftPro.Select.Client;
using SoftPro.Select.Common;
using SoftPro.OrderTracking.Common;
using SoftPro.ClientModel.Integration;


namespace LookupTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string startupPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase.Substring(@"file://\".Length));
            textBox1.Text += String.Format("\r\n{0}", startupPath);
            

            textBox1.Text += String.Format("\r\nLogging in...");
            SelectServer server;
            NetworkCredential credential = new NetworkCredential("Admin", "Passw0rd", SoftPro.ClientModel.Constants.ServerDomain);
            //server = new SelectServer(null, new Uri("http://localhost:8080/"), credential);
            server = new SelectServer(null, new Uri("http://SPSCMID09:8095/"), credential);
        

            string reason;
            if (!server.TryAuthenticate(out reason))
            {
                textBox1.Text += String.Format(String.Format("Log in failed: {0}", reason));
                return;
            }
            else
            {
                textBox1.Text += String.Format("\r\nSuccessfully logged in as: " + credential.UserName );
            }

            Lookups lookupService = server.GetService<Lookups>();

            LookupQuerySpec luSpec = new LookupQuerySpec()
            {
                Table = "Abstractor",
                SchemaOnly = false,
                MaxRows = 50,
                Filter = "[Name] like '%%'"
            };

            ILookupTable table = lookupService.QueryTable(luSpec);

            //AddRandomRow(lookupService, table);
            RenameLookupCodeEachRow(lookupService, table);
        }

        private void RenameLookupCodeEachRow(Lookups lookupService, ILookupTable table)
        {
            if (table.Rows.Count < 1)
            {
                textBox1.Text += String.Format(String.Format("{0}No rows found in that lookup table", Environment.NewLine));
                return;
            }

            textBox1.Text += String.Format("\r\nBefore changing lookup codes");
            foreach (ILookupRow row in table.Rows)
            {
                textBox1.Text += String.Format(String.Format("\r\nLookupCode: {0}", row["Lookup Code"].Value.ToString()));
            }

            try
            {
                foreach (ILookupRow row in table.Rows)
                {
                    row["Lookup Code"] = row.NewValue(false, Guid.NewGuid().ToString());
                }

                lookupService.ApplyChanges(table);

                textBox1.Text += "\r\nAfter updating the LookupCodes";
                foreach (ILookupRow row in table.Rows)
                {
                    textBox1.Text += String.Format(String.Format("\r\nLookupCode: {0}", row["Lookup Code"].Value == null ? "NULL" : row["Lookup Code"].Value.ToString()));
                }
            }
            catch (Exception ex)
            {
                textBox1.Text += String.Format(String.Format("Well, that failed: {0}", ex.Message));
            }
        }

        private void AddRandomRow(Lookups lookupService, ILookupTable table)
        {
            if (table.Rows.Count < 1)
            {
                textBox1.Text += String.Format(String.Format("{0}No rows found in that lookup table", Environment.NewLine));
                return;
            }

            textBox1.Text += String.Format("\r\nBefore adding row</BR>");
            foreach (ILookupRow row in table.Rows)
            {
                textBox1.Text += String.Format(String.Format("\r\nName: {0}</BR>", row["Name"].Value.ToString()));
            }

            try
            {
                var newRow = table.NewRow();
                var newName = newRow.NewValue(false, Guid.NewGuid().ToString());
                newRow["Name"] = newName;
                var newCode = newRow.NewValue(false, Guid.NewGuid().ToString());
                newRow["Lookup Code"] = newCode;


                table.Rows.Add(newRow);
                lookupService.ApplyChanges(table);


                textBox1.Text += String.Format("\r\nAfter adding row</BR>");
                foreach (ILookupRow row in table.Rows)
                {
                    textBox1.Text += String.Format(String.Format("\r\n{0}</BR>", row["Lookup Code"].Value == null ? "NULL" : row["Lookup Code"].Value.ToString()));
                }
            }
            catch (Exception ex)
            {
                textBox1.Text += String.Format(String.Format("Well, that failed: {0}", ex.Message));
            }
        }
    }
}
My little tool returns this when I run it, and it'll change the lookup codes every time I click go:


C:\Users\jmorton\Documents\Visual Studio 2008\Projects\LookupTest\LookupTest\bin\x86\Debug
Logging in...
Successfully logged in as: Admin
Before changing lookup codes
LookupCode: 72d817b8-ab70-4dbd-9a8d-36703e43bfa7
LookupCode: f4da3fd8-f1b0-45db-b734-8c66a4eb9d63
After updating the LookupCodes
LookupCode: 217c2030-007b-44df-b5e0-f27eea902d5b
LookupCode: 18f4cf6c-fc70-4ece-aaba-0bd3abbca593

john.morton
Posts: 89
Joined: Wed Nov 16, 2011 11:51 am

Re: Problem updating lookups in web .net

Post by john.morton » Mon Nov 12, 2012 5:34 pm

As an experiment, I rewrote my test app in VB (using your sample code as a start), and it worked no problems:

Code: Select all

Imports SoftPro.Select.Client.SelectServer
Imports SoftPro.OrderTracking.Common
Imports SoftPro.OrderTracking.Client
Imports System.Reflection
Imports System.IO
Imports System.Net

Public Class Form1

    Public table As ILookupTable
    'connect using the select server object
    Public credentials As New NetworkCredential("Admin", "Passw0rd", SoftPro.ClientModel.Constants.ServerDomain)
    Public sps As New SoftPro.Select.Client.SelectServer("http://SPSCMID09:8095/", credentials)


    Public spec As New LookupQuerySpec()

    Public lookups As Lookups = sps.GetService(Of Lookups)()

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strSQL As String
        Dim item As ILookupValue
        Dim row As ILookupRow

        On Error Resume Next
        Dim str As String = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase.Substring("UpdatesInSelect://\".Length))
        TextBox1.Text += vbCrLf & String.Format("Logging in...")

        sps.EnsureAuthenticated()

        TextBox1.Text += vbCrLf & String.Format("Hopefully Logged in...")

        spec.Filter = "[Lookup Code] like '%%'"
        spec.Table = "Abstractor"
        spec.SchemaOnly = False

        On Error Resume Next

        Dim table As ILookupTable = lookups.QueryTable(spec)

        TextBox1.Text += vbCrLf & String.Format("Before changing lookup codes")

        For x = 0 To table.Rows.Count - 1
            row = table.Rows(x)
            row.Item("Lookup Code") = row.NewValue(False, "TEST--" & x) 'this worked
        Next

        TextBox1.Text += vbCrLf & "After updating the LookupCodes"

        For x = 0 To table.Rows.Count - 1
            row = table.Rows(x)
            TextBox1.Text += vbCrLf & String.Format(String.Format("LookupCode: {0}", row("Lookup Code").Value.ToString()))
        Next

        Lookups.ApplyChanges(table)
    End Sub
End Class
I did have to change just a few things:
I added back in your NetworkCredential, I assume your connection is failing without this?
I changed your references to [LookupCode] to [Lookup Code], because the default table has a space.

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: Problem updating lookups in web .net

Post by czentman » Wed Nov 14, 2012 11:41 am

I did exactly what you did, copied the code. I used the credentials as well. I kept the LookupCode because I changed that in the table to be without space. Everything looks good until applyChanges call I get this error.


UpdatesInSelectFormApp.vshost.exe Error: 0 : Failed to load DataItemDescription cache: Value cannot be null.
Parameter name: key
A first chance exception of type 'SoftPro.OrderTracking.Common.LookupException' occurred in SoftPro.OrderTracking.Client.dll
The thread '<No Name>' (0x15e0) has exited with code 0 (0x0).

Please advise.

Post Reply