Smart object retrieval


Badge +8

I created two smartobject methods( namely getcountries and getbusinessunits) using dynamic sql sp service as list methods and configured them as cascading in infopath(when country is changed business units must be fetched based on the country)


When the getbusinessunits methods gets executed by passing the country value in its input properties im getting this following error


The query cannot be run for the following DataObject: InfopathServices_GetBusinessUnits
InfoPath cannot run the specified query.
The SOAP response indicates that an error occurred on the server:


System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Invalid column name 'Country'.
   at SourceCode.Workflow.Common.HostedServers.SmartObjects.ExecuteListMethod(SmartListMethod listmethod)
   at SourceCode.Workflow.Common.InfoPathSOMethodExecuter.ExecuteSecondaryDataSource()
   at SourceCode.Workflow.Common.InfoPathSOMethodExecuter.ExecuteMethod()
   at SourceCode.Workflow.Common.InfoPathHelper.GetInfoPathMethodResultSet(XmlDocument soInputs)
   at SourceCode.Workflow.RuntimeServices.Functions.InfoPathFunctions.ExecuteSmartObjectMethod(XmlDocument executeSmartObjectMethodInput)
   at SourceCode.Workflow.RuntimeServices.InfoPathService.ExecuteSmartObjectMethod(XmlDocument ExecuteSmartObjectMethodInput)
   --- End of inner exception stack trace ---


Note: Both the methods will return more than one value and both of them are working correctly when i tested with smartobject tester tool


26 replies

Badge +1
I am also getting the same error.. please anyone help...
Badge +8
Has anyone encountered a similar issue..I'm stuck with this one for the past 10 days :-(
Badge +6

I had similar problems using the DSQL SP Service. When using parameters for the stored proc in question, I have noticed and tested to rather not use input properties within the Smart Object. This worked for me. In other words, specify the parameters within the SO Method parameters (optional) page within the wizard, which will result in only having output properties within the SO.


Follow these steps...


1. Create the stored proc and compile it,


2. Refresh the SQL SP Service in the Broker Management Tool to update the cached list of stored procs.


3. Create your SO, remove all smart box methods.


4. Add a new method, pointing to the SQL SP Service and desired stored proc's Execute method. This will pick up any parameters


   and result set columns based on the stored proc.


5. On the Method Parameters (optional) page of the wizard, enter your parameter(s), without the prefix @ sign.


6. Map the input and output properties, noticed that all input parameters are marked with a number in brackets as post fix.


    Change their mappings by clicking on Assign, then change to use the SO Method Parameter and select the ones entered. Dismiss any dialog


    to delete input properties not used/mapped.


7. The SO should as a result only have output properties.


8. Ensure that if your input parameters do not contain the same field names as result set field, this will also cause problems. Name iether


    of them differently, like inputCountry as an input parameter, and Country within the stored proc final result set.


 


Hope this helps :-)

Badge +8

Your method resolved the error, but the so method is not returning any values..


prreviously i was able to see the results in SOtester tool. now there is no results when i tested in that.. please see attched image


12693i429152E5462141EC.jpg
Badge +6

Hi, did you get your drop down working? You have a rule on the first drop down right, to populate the second one everytime a value is selected in the first?


I will test it out soon, and tell you what I found.

Badge +8
Nope, i havent got this to work.. I have no luck working with smartobjects i guess..
Badge +6

maxpirate


 


any luck with this yet? i have done this before which worked fine...post your steps below so that I can help figure out what might be the problem...


 


remember:


1. Open rule: execute SO 1 method to populate first dropdown


2. Ensure dropdown 1 is binded to SO 1 method properly in infopath form


3. Add a rule on dropdown 1, for whenever the value change: execute SO 2 method to populate dropdown 2, taking the value of dropdown 1 as parameter


4. Ensure dropdown 2 is binded to SO 2 method properly in infopath


 


Let me know...

Badge +8
I have done exactly the same. but i still cannot fathom out what causes the error. Everything seems to be working in smartobject tester :-(
Badge +6

Hi,


 I am getting same error , when I integrate Smart object with Infopath form. ( The smart object uses custom (static) service type).


Please let me know, If you know the workaround.


 


Thanks


Jayaprakash n M

Badge +8
I have not found a answer to the error. sorry.
Badge +6

MaxPirate


You mention it is working fine in the SO Tester Tool?


Try and open the service types using the Broker Management Tool and refresh the service types there as well. Also close the InfoPath form and run an iisreset, try and test again and see what happens.


I tried this scenario before which worked, I'll look at it again and let you know what I did.

Badge +6

Ok, so here is my scenario and what I did... Will try and show as much as possible...


 


On my InfoPath form I have a Department and Personnel drop downs. Based on the particular selection of department, all employees for that department must


be shown in the personnel drop down. But the personeel details is yet contained within another master table, updated by an external process.


 


I used the K2 DSQL Service type that can be downloaded from the K2 Underground Black Market to use as lookup mechanism via SO's to retrieve


data from a custom workflow database and tables in SQL.


 


SQL Table SnapShots:


1. Department


    DeptID              bigint            PK    auto increment


    Name               nvarchar(50)


 


2. Department_Personnel


   PersonnelNo      nvarchar(50)      PK


   DeptID               bigint


 


3. Personnel_Master


   PersonnelNo       nvarchar(50)     PK


   NTAccount         nvarchar(50)


   FirstName          nvarchar(50)


   LastName          nvarchar(50)


  . . .


 


K2 SO's Snapshot:


1. SoDepartment


       Properties (DeptID, Name)


       Methods (Read, GetList)


 


2. SoDepartmentPersonnel


      Properties (PersonnelNo, NtAccount, FirstName, LastName, etc....)


      Methods (Read, GetList(which is a SO join on 3 tables, all setup in the SO wizard = Department/Department_Personnel/Personnel_Master)


 


K2 Broker Management Tool


1. Open service types


2. Expand the newly created type for the DSQL Service type, refresh manually


 


K2 SO Tester Tool


1. Open SO Explorer


2. Test Department and Personnel SO methods 


 


InfoPath Form


1. Integrate all required SO's with form


2. Design form, add one dropdown = Department, second dropdown = Personnel


3. Bind GetList method of each SO to corresponding dropdown controls, specifying the value and description properties correctly


4. Form Options, add a rule to execute a connection, calling the Department SO GetList method


5. Add a Rule on control/dropdown Department, for when its value changes...


   a. To take the value selected (DeptID) and pass it as input parameter for the Personnel GetList method


   b Execute a connection,calling the Personnel SO GetList method


 


This is really all I did, and it worked fine for me.


Let me know what happens with yours, if you still have problems, then post your steps over here in as much detail, so that we can solve it.


 


Good Luck! 


  

Badge +8

Sagrys, mine is a simple approach, there are business units and departments associated by business unit.


I created two getlist methods in smartobject using dynamic sql service. i have my data from a custom sql database.


The getDepartments method will take one param as input(which is the business unit id) to pull respective departments.


I deployed the smart object.


Integrated my infopath with these smart object methods. Then configured two dropdowns to use these respective methods.


Used rules to pull departments values when business unit is selected.


Previewed it. the business unit dropdown works(it has no input params) but the department does not work.i get the error msg

Badge +6

I wonder if that dropdown control became a bit bogus and went corrupt...


Maybe try deleting the control... add a new one and try again


If still no go... then remove the SO from the form integration totally, delete thr control once again, and re-map all and try again

Badge +8

Sagrys,


I have implemented that close to ten times. I think that i have a problem with DSQL dll. Im waiting for the time when my dev env is gonna be reinstalled once when they clean up POC's.


 

Badge +6

Hey Maxpirate


Hmmm, what version of the DSQL assembly are you using? I know there were previous versions that gave some issues, but the latest is working fine.


 

Badge +8
forgive me if what im gonna ask is very lame, do i have to generate strong name for the DSQL dll and install it in the gac after i have downloaded that from blackmarket
Badge +6

Ask away my friend, there is no question that is lame :-) 


Last time I downloaded binaries for v.1.03 if I am not mistaken, was a while ago....which was the fixed version of the DSQL Service, be sure to get that one. If it is not available no more, I saved it to one of my ext HDD at home, can email it to you if need be. So let me know


 It will do no harm whatsoever to generate a strong name and put it into the GAC, so no worries over there. If you open their project it should have a SNK already generated in it and you could only recompile their project, and copy the dll to the K2 blackpearl ServiceBroker folder and start registering it within the Broker Management Tool, that should do it.


 

Badge +8

Yes, i have downloaded that version today. will give it a try this week and update.


Even if i dont put that in the gac it wouldn be a problem right?

Badge +6

Hello,


 


Let me explain the scenario, I have developed Custom Search service (Static Service (Custom)) that returns a collection of customer records using the List method.


 


Properties of Service:


 


Customer Name


Customer Number


Customer Address


Customer Contact


Cost Center


 


Method:  GetCustomerList ( This is list method)


Input Properties: {Customer Name, Customer Number}


Output Properties: {Customer Name, Customer Number, Customer Address


Customer Contact, Cost Center}


And I have created a Customer Search Smart object that uses GetCustomerList method of server with above properties.


 


I tested the smart object with Smart object tool, it is working properly. But It gives below error , when I integrate the smart object with InfoPath Form.


 


The query cannot be run for the following DataObject: CustomerSO_GetCustomersList


InfoPath cannot run the specified query.


The SOAP response indicates that an error occurred on the server:


 


Server was unable to process request. ---> Message: Cannot find column [CustomerName].; ServiceName: NoInternCust; ServiceGuid: e84773f0-a345-4707-9581-2a352a83bb6f; InnerExceptionMessage: ;


 


Please any suggestions


 


Thanks


Jayaprakash N M

Badge +6

Hello,


 


Let me explain the scenario, I have developed Custom Search service (Static Service (Custom)) that returns a collection of customer records using the List method.


 


Properties of Service:


 


Customer Name


Customer Number


Customer Address


Customer Contact


Cost Center


 


Method:  GetCustomerList ( This is list method)


Input Properties: {Customer Name, Customer Number}


Output Properties: {Customer Name, Customer Number, Customer Address


Customer Contact, Cost Center}


And I have created a Customer Search Smart object that uses GetCustomerList method of server with above properties.


 


I tested the smart object with Smart object tool, it is working properly. But It gives below error , when I integrate the smart object with InfoPath Form.


 


The query cannot be run for the following DataObject: CustomerSO_GetCustomersList


InfoPath cannot run the specified query.


The SOAP response indicates that an error occurred on the server:


 


Server was unable to process request. ---> Message: Cannot find column [CustomerName].; ServiceName: CustomerSearchServicet; ServiceGuid: e84773f0-a345-4707-9581-2a352a83bb6f; InnerExceptionMessage: ;


 


Please any suggestions


 


Thanks


JayaPrakash N M

Badge +6

Hi jpmarrapu


Well, looking at your example, it seems that there might be a spelling mistake, thus the error you are experiencing.


Your Input and Ouput properties refer to the customer name as "Customer Name", yet the error in InfoPath does not see the property name spelled like that, but rather like "CustomerName", thus not finding the column.


Just check where you ref this property everywhere in your code, I am sure this is just a spelling mistake. Correct it and re-map it to InfoPath and try again.

Badge +6

Hi Sagrys,


 


Thanks for reply


 


I have checked InfoPath form data connections and smart object properties. They are no differences.


 


I have successfully integrated Read method of smart object with InfoPath form,


But still I am getting above error, when I try to integrate the List method with InfoPath form.


 


Please any suggestions?


Thanks


Jayaprakash N M

Badge +6

Hi


Please try to remove the entire SO and all its methods from the InfoPath form.


Then re-export this particular SO only. Test it in the SO Tester Tool and see if you get any errors there. If no errors experienced, re-add the SO for integration to the process and InfoPath form and check it again.


 


 

Badge +6

Hi ,


 


 


You had fixed above issue with help of K2 consultant. The fix was Input properties should be part of return results in List method.


 


But Smart objects (List) are giving error after we have upgraded our K2 server from 4.8210.2.450 to 4.8210.2.545.


 


But they are working with Smart object tester, but not in InfoPath form (we had seen similar behavior).


 


This error is


 


The query cannot be run for the following DataObject: AP_CustomerListSO_GetNonInternalCustomersList


InfoPath cannot run the specified query.


The SOAP response indicates that an error occurred on the server:


 


Server was unable to process request. ---> Message: Syntax error: Missing operand after ''AB'' operator.; ServiceName: AP_CustomerListSvc; ServiceGuid: e84773f0-a345-4707-9581-2a352a83bb6f; InnerExceptionMessage: ;


 


Note: ‘AB’ is input property value that is key in on InfoPath from.


 


 


I checked Smart object logs under service broker, Smart object list method returns result in to log file, when I invoke from InfoPath Form. But still I am getting above error on InfoPath form.


 


Please any help?


 


Thanks


JayaPrakash NM

Reply