dhenning - Wednesday, July 16, 2008 11:18 AM:
I have been trying to overcome a mental (!) block for 4 days now trying to get an SQL process called from a method to return my selected data. I had modeled my process on a method included in Innovator but somehow I have gone wrong - terribly wrong . Now I am down to a simple test method just trying to get the process to work.....here is the method I am using (AND I have verified that the id I have 'hard coded' into this is the correct id for the wbs I want to start with. Also, the SQL I have put in Visual Studio (connected to the same database) and it runs and returns exactly what I was looking for - using the id 'hard coded' in the method as input. Where am I going wacko?
Here is my simple test method:
Dim myinv As Innovator = Me.newInnovator
Dim myinn As Innovator = Me.newInnovator
Dim vStr As String
Dim vName As String
Dim vWBS As String
Dim vAct As String
Dim q As Item = Me.newItem()
Dim r As Item = Me.newItem()
Dim s As Item = Me.newItem()
Dim t As Item = Me.newItem()
Dim oCall As Innovator = Me.newInnovator
vStr = "A29101C24DAD4630A57C39C363C85F4F" ' Top WBS id for proj 1324
Dim aml As String = String.Format("" & _
"<AML><Item type='SQL' action='SQL PROCESS'>" & _
"<PROCESS>CALL</PROCESS><name>get_subwbs_oac</name><ARG1>{0}</ARG1>" & _
"</Item></AML>", vStr)
s = myinv.applyAML(aml)
Return (myinn.newResult(s.node.OuterXML))
I get an error that points to the Return line and says there is no instance of the object (presumably the s.node.OuterXML reference) .
and the SQL get_subwbs_oac text is:
/* get list of wbs elements below top wbs in project */
create procedure get_subwbs_oac(@id char(32))
as
begin
SELECT innovator.WBS_ELEMENT.NAME AS WBSName, innovator.WBS_ELEMENT.ID AS WBSid, innovator.ACTIVITY2.NAME AS ActName,
innovator.ACTIVITY2.PROJ_NUM AS ProjNbr, innovator.ACTIVITY2.STATE AS ActState, innovator.SUB_WBS.SOURCE_ID
FROM innovator.SUB_WBS INNER JOIN
innovator.WBS_ELEMENT ON innovator.SUB_WBS.RELATED_ID = innovator.WBS_ELEMENT.ID INNER JOIN
innovator.WBS_ACTIVITY2 ON innovator.WBS_ELEMENT.ID = innovator.WBS_ACTIVITY2.SOURCE_ID INNER JOIN
innovator.ACTIVITY2 ON innovator.WBS_ACTIVITY2.RELATED_ID = innovator.ACTIVITY2.ID
WHERE (innovator.SUB_WBS.SOURCE_ID = @id)
end
SamsAn - Tuesday, July 22, 2008 4:51 PM:
Instead of
s = myinv.applyAML(aml)
Return (myinn.newResult(s.node.OuterXML))
Try these code lines:
s = myinv.applyAML(aml)
Return (myinn.newResult(s.dom.OuterXML))
or
s = myinv.applyAML(aml)
Return s
vishal_trivedi - Monday, November 29, 2010 6:55 AM:
Hi Dhenning
If you want to get all the activities of the project or any of the data related to Project, WBS Element or Activity2 you can modify below query.
I have tried and observed your query. I might be wrong. But try below query.
If you are looking for any other thing then please tell me I can solve your problem related to above point.
Select pr.[name],wb.*,act.[name]
from innovator.PROJECT pr
left join innovator.WBS_ACTIVITY2 wb
on pr.WBS_ID = wb.[SOURCE_ID]
left join innovator.Activity2 act
on act.ID = wb.[RELATED_ID]
where pr.WBS_ID = ""
You can get WBS_ID it's a property of PROJECT schema.
-- Vishal
fli - Wednesday, November 10, 2010 6:02 AM:
Hi,
I am having the same troubles.
I would like to do a "Client Event" Getting some properties from Activity2.
Testing on them, and by the result setting some propreties in Activity2.
This is a workaround, my initial problem is to eddit the project template.
Did you get it working ?
BR/ Christoffer
dhenning - Wednesday, November 10, 2010 11:46 AM:
Christoffer:
Back when I was working on this, the help SamsAn gave got me through the SQL returning the correct data, but I could not get the right process in place to update data back at the server and so this process was then abandoned. So, in short, no, I did not get it working. I took a different tack at the time and did something as a background server process to change info in the Activity2 assignments.
Sorry....hope you have better luck in your quest.
-Dennis
vishal_trivedi - Monday, November 29, 2010 6:37 AM:
HI Dennis,
I was also getting problem when i applied Return innovator.newResult(res) then it worked.
It should be taken care when we are using C# and we want to perform some operations related to DB.
-- Vishal
