SQL Database Integration

Re: Parameter to a multi-table secondary data source

Fletchsm — Fri, 26 Mar 2010 18:05:40 GMT

Well not sure if this was the best way to do it, but it worked. I am sure if anyone had any C# training at all they would have known this right off the bat :) since you have to put a + after each new line and each line has to be inclosed in " for the statement

"shape {select * from InspectionReports where IRid = " + IRlookup + " } as InspectionReports " +

"append ({select * from IRHealthSafetyActions as IRHealthSafetyActions} relate IRid TO IRid) as IRHealthSafetyActions, " +

" ({select * from IREnergyMeasureActions as IREnergyMeasureActions} relate IRid TO IRid) as IREnergyMeasureActions," +

" ({select * from IRDiagnosticTestsActions as IRDiagnosticTestsActions}relate IRid TO IRid) as IRDiagnosticTestsActions," +

" ({select * from IRComments as IRComments} relate IRid TO IRid) as IRComments" ;

Re: Parameter to a multi-table secondary data source

Patrick Halstead — Fri, 26 Mar 2010 03:11:52 GMT

Hi FletchSm,

Thanks for posting!

First off, your question is more of a SQL coding question, not really InfoPath. Second, writing code in your form is not a best practice because it increases IT cost to maintain across multiple XSNs. Now, I know you're a developer and those two points mean absolutely nothing to you. You just want a solution and I can appreciate that, but we've got to pay for this free forum somehow, so we hope people take a look at our ultra-cheap qWSSP Web Service which basically does what you want to do without writing code. Not only is it dirt cheap but it also reduces the cost of code maintenance - since it's codeless, so you win double (well, your company wins double - maybe you don't win because you can't write code like you want ... :)

If that doesn't work for you, please wait for someone else to offer you free assistance. Unfortunately, we can't fund free coding help for people due to the above. Hope you understand.

Best,

Patrick

Parameter to a multi-table secondary data source

Fletchsm — Thu, 25 Mar 2010 14:47:52 GMT

I am trying to see how to format the SQL statement for a button that will query based on another field in my form. I know how to write sql queries normally but the c# is throwing me off. If anyone has any links to examples or tutorials that would be helpful. Thanks.

Here is what Infopath says the sql is on my secondary datasouce I am using when I modify/edit the datasource.

shape
{select "IRid","MonVisitnumber","MonitorID","UnitStatusID","GranteeAgencyCD","DateOfVisit","OccupantName","JobNo","HeatingCon","InsulationCon","AddFundSour","Passed" from "smorris"."InspectionReports" as "InspectionReports"} as "InspectionReports"
append
  ({select "IRid","LineItemID","ActiontypeID","ActionCOM","ActionHS" from "smorris"."IRHealthSafetyActions" as "IRHealthSafetyActions"}
   relate "IRid" TO "IRid") as "IRHealthSafetyActions",
  ({select "IRid","LineItemID","ActiontypeID","ActionCOM","ActionHS" from "smorris"."IREnergyMeasureActions" as "IREnergyMeasureActions"}
   relate "IRid" TO "IRid") as "IREnergyMeasureActions",
  ({select "IRid","LineItemID","ActiontypeID","ActionCOM","ActionHS" from "smorris"."IRDiagnosticTestsActions" as "IRDiagnosticTestsActions"}
   relate "IRid" TO "IRid") as "IRDiagnosticTestsActions",
  ({select "IRCommentID","IRid","CommentTypeID","CommentText" from "smorris"."IRComments" as "IRComments"}
   relate "IRid" TO "IRid") as "IRComments"

and I am trying have that data filtered by

where [IRid] = " + IRlookup;

This is working if I only use "select * from [InspectionReports] where [IRid] = " + IRlookup;

But I missing half of the data I need.