Data Connections

Re: Can I pass parameters to a Data Connection?

dlgross — Mon, 11 Aug 2008 14:21:43 GMT

nukkumatti, I am attempting to follow your suggestion, but when I do, the command string I get back is "exec dbo.uspJobAndContract" which is the correct name of the stored proc I am trying to execute. However, it looks like I should be getting back the select statement from my proc. Any idea what I might be doing wrong? Below is my sproc and jscript

ALTER PROCEDURE [dbo].[uspJobAndContract]

-- Add the parameters for the stored procedure here

--@JobNumber char(6) = 444139

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT TOP (100) PERCENT PARFLS.dbo.OverheadPool.Description1 AS Division,

PARFLS.dbo.WorkLocation.Description_1 + ' (' + JM.Office_Number + ')' AS [Office/Sector], JM.Job_Number,

PARFLS.dbo.ContractMaster.Customer_Number, PARFLS.dbo.ContractMaster.Customer_Name AS Customer, JM.Job_Description,

JM.ProjMgrLastName, JM.ProjMgrFirstName, PARFLS.dbo.ContractMaster.Contract_Number, PM.EMail_Name, JM.Office_Number

FROM PARFLS.dbo.JobMaster AS JM INNER JOIN

PARFLS.dbo.WorkLocation ON JM.Office_Number = PARFLS.dbo.WorkLocation.Work_Location INNER JOIN

PARFLS.dbo.OverheadPool ON PARFLS.dbo.WorkLocation.Overhead_Pool = PARFLS.dbo.OverheadPool.Pool_Master LEFT OUTER JOIN

PARFLS.dbo.ContractMaster ON JM.Contract_Master_Number = PARFLS.dbo.ContractMaster.Contract_Number LEFT OUTER JOIN

PARFLS.dbo.HrEmployee AS PM ON JM.PM_Employee_Number = PM.Employee_Number

--WHERE (JM.Job_Number=@JobNumber)

WHERE (JM.Job_Number='123456')

END

function msoxd_my_JobNumber::OnAfterChange(eventObj) {

// A field change has occurred and the DOM is writable. Write code here to respond to the changes.

objADOAdapter = XDocument.DataObjects("Job Data").QueryAdapter;

var command = objADOAdapter.Command;

//you receive back a string "select * from tblEmployess where name = 'Input'"

var query = command.replace("'123456'", "'" + XDocument.DOM.selectSingleNode("/my:myFields/my:JobNumber").text + "'");

// replace your "Input parameter with the value you want"

objADOAdapter.Command = query;

objADOAdapter.Query();

objADOAdapter.Command = command;

}

Re: Can I pass parameters to a Data Connection?

nukkumatti — Tue, 04 Dec 2007 08:20:19 GMT

Hey Mr. Monkey. First of all I would say that best practice for these scenarios is to write a webservice which requires input parameters and let them call the usp (stored procedure). However I agree that it can may be become bit bothersome to allways write/use a webservice if you jsut want to call an usp on a database to which you are connected anyway. Unfortunately (as far as I know) it is not possible to pass parameters dynamically to a stored procedure, BUT as always there is a little trick how you can accomplish what you wanna do. Keep in mind, this is not best practice ! First thing to do is to change your usp a bit. Instead of having input parameters do something like the following (simple example which takes only one input): select * from tblEmployee where name = 'Input' . You see the input i use is actually not a parameter but a usual string value. Now in Infopath set up a new dataconnection to receive data from your database and use "exec uspName" as the sql command. So at the momenten if you would just use a rule to call you dataconnection your sql command wouldn't make to much sense as Input is most probably not a name of your Employee table. Therefore now there comes the second part of the workaround. In your scenario i would say put the code we gonna need in the onAfterChange event of one of your ddls. See below jscript example of what you need to do: objADOAdapter = XDocument.DataObjects("NameOfYourDataConnection").QueryAdapter; var command = objADOAdapter.Command; //you receive back a string "select * from tblEmployess where name = 'Input'" var query = command.replace("'Input'", "'" + XDocument.DOM.selectSingleNode("xPath to your drop down list").text + "'"); // replace your "Input parameter with the value you want" objADOAdapter.Command = query; objADOAdapter.Query(); objADOAdapter.Command = command; the last line of the code is also very important but I think you will figure out why if you just leave it away. Hope this helps you and I guess it also answers your other post which was actually the same than this one. Feel free to ask if you have any furhter questions. Sorry for the formating.

Can I pass parameters to a Data Connection?

MonkeyCMonkeyDo — Wed, 28 Nov 2007 17:55:16 GMT

Hello,

I don't know if this is possible so I thought I'd ask cause it would make my life a lot easier. Is there a way to pass the value of a drop-down list box as a parameter to a stored proc in the data connection? I have a stored proc that takes 4 parameters, I have two drop-down list boxes that will be selected and I need both of those values to be passed to my stored proc. I have a third drop-down list box that will accept the results of that stored proc.

This doesn't seem feasible, but if it is I figured I'd find out here.

My second solution was to capture the values of the first two drop-down list boxes in my code behind...use ADO.NET to pass those values to my stored proc. The problem I run into is how do I map the results of the stored proc in order to populate my third drop-down list box?

This is a task of a professional...are you out there?

Thanks!