InfoPath Dev

Let's approach one issue at a time.

acarita:

When I preview the form and try to run it, i get the following error.

The query cannot be run for the following DataObject: PayPeriodsDataSource
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Line 1: Incorrect syntax near '/'.

The issue is this line:

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ' + "myFields/my:HireDate";          

You're basically sending the SQL server the command

execute "dbo"."VCS_SP_Remaining_Pays" myFields/my:HireDate

Which doesn't mean anything.  Previously you seemed to be correctly storing the HireDate in a variable.  Could you give that a try again and then let us know how it went?

I added this line

var HireDate = XDocument.DOM.selectSingleNode("my:myFields/my:HireDate")

and I changed this

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ' + "myFields/my:HireDate"; 

to

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ' + "HireDate"; 

It looks like we're making progress.  Now I get the error

The query cannot be run for the following DataObject: PayPeriodsDataSource
InfoPath cannot run the specified query.
[0x80040E07][Microsoft OLE DB Provider for SQL Server] Error converting data type nvarchar to datetime.

 At this point your SQL command is:

 execute "dbo"."VCS_SP_Remaining_Pays" HireDate

 Because you put HireDate in quotes as a string literal.

But aside from that, date literals in SQL are a bit tricky.  What happens if you use:

 dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" {d \'' + HireDate + "'}";

Since it is a bit hard to see, that last part is:

{d \SINGLEQUOTEDOUBLEQUOTE + HireDate + DOUBLEQUOTESINGLEQUOTE}DOUBLEQUOTE;

I edited the code to have

{d\'" + HireDate + "'}"

When I tried to preview the form, it failed to open.  The error about the nvarchar was corrected by editing the Stored Procedure.  I spoke with our SQL guy and he modified the query for me.  We also have the code working to the point where it is looking for a parameter that it says isnt supplied.

The query cannot be run for the following DataObject: PayPeriodsDataSource
InfoPath cannot run the specified query.
[0x80040E10][Microsoft OLE DB Provider for SQL Server] Procedure 'VCS_SP_Remaining_Pays' expects parameter '@HireDateV', which was not supplied.

Here is what the code I am using looks like.

var dc = XDocument.DataObjects["PayPeriodsDataSource"].QueryAdapter;        

var originalCommand = dc.Command;     // backup the connection's original command

var HireDateV = XDocument.DOM.selectSingleNode("/my:myFields/my:HireDate").text;

var Remainingpays = XDocument.DOM.selectSingleNode("/my:myFields/my:RemainingPays").text;

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ', + HireDateV + ', ' + Remainingpays + 'output';    

dc.Query();

dc.Command = originalCommand;    // restore the original command to the data source

 I'm pretty sure you're going to need to do the {d ...} thing in order for SQL to accept the date unless that field is a string-type field in the database.  Maybe there was a missing quotation mark somewhere preventing the form from opening.

 It looks like you're missing a space before output.  Try using ' output' instead of 'output'

Other than that, I'm not sure why you're getting that error.  If the above doesn't solve it, perhaps you could show me the first few lines of the stored procedure's definition so I can see what parameters it takes?

I added the space to in front of output, and added the {d \...} and then the form wouldn't open.  I looked over everything and found one missing quotation mark that I added but didn't help.  Maybe you can see something that I may have overlooked.

var dc = XDocument.DataObjects["PayPeriodsDataSource"].QueryAdapter;        

var originalCommand = dc.Command;     // backup the connection's original command

var HireDateV = XDocument.DOM.selectSingleNode("/my:myFields/my:HireDate").text;

var Remainingpays = XDocument.DOM.selectSingleNode("/my:myFields/my:RemainingPays").text;

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ', "{d \'" + HireDateV + "'}" ', ' + Remainingpays + ' output';

//dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ', + HireDateV + ', ' + Remainingpays + ' output';

//{d \'" + HireDate + "'}";

Here is the stored procedure.  I wasnt sure what part would be helpful, so I posted all of it. 

ALTER PROCEDURE [dbo].[VCS_SP_Remaining_Pays]
 @HireDateV varchar(20),
    @Remainingpays smallint OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

declare @HireDateD datetime

set @HireDateD = CAST(@HiredateV as datetime)
set @Remainingpays = (select sum(1)                          --as Remainingpays
FROM VCS_WorkCalendar
WHERE StartofPay = '1'
  AND CalDate  > (@HireDateD -14)            -- include current ppd
  AND Caldate <= (select max(Caldate)from VCS_WorkCalendar
                    where StartofPay = '1' and    --exclude last ppd if overlap to next year 
                      Datepart(yyyy, PayDate) = Datepart( yyyy, @HireDateD)))

END

I appreciate your help with this. 

All of your code is still in your button click event? You originally had this attached to a 'refresh' button, right?

yes.  It's just a button that I labeled "Refresh" and attached the code to the On_Click event.

 As I suspected, the form is not opening because of this line:

 dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ', "{d \'" + HireDateV + "'}" ', ' + Remainingpays + ' output';

 Here you have a comma where you should have a plus sign:

 Remaining_Pays" ', "{d \'"

And here you have two separate strings with nothing joining them:

 "'}" ', '

 If you put plus signs in those two places, the form will open.

However, there are still a few issues.

After looking at the stored procedure I can see that your colleague has modified the stored procedure to accept HireDate as an nvarchar and convert it to a date, so that means you do not need the {d } bit anymore.

Also, you are passing in the value of the Remainingpays field, but the server will expect an SQL variable there since it is an output parameter.

Please try the following and let me know if the query executes without an error:

 dc.Command = 'DECLARE @remainingPays smallint '
+ 'EXECUTE "dbo"."VCS_SP_Remaining_Pays" \'' + HireDateV + '\', @remainingPays OUTPUT '
+ 'SELECT @remainingPays as RemainingPays';

I tried the code snippet you gave me and it executed without error.  Thank you for that. 

Now the next step I need to figure out is how can take the value that is being returned, @Remainingpays, and set it equal to, /my:myFields/my:RemainingPays?  @Remainingpays is not a column in any table, otherwise I would create that datasource and set the field equal to it. 

The end result is to have that RemainingPays value get multiplied by number to return the # of paid time off. 

Up near the top of the JScript code, you should see a section where SelectionNamespaces is being defined.  You'll need to add this line right after it:

XDocument.DataObjects["PayPeriodsDataSource"].DOM.setProperty("SelectionNamespaces", 'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');

Once you've done that, you should be able to access the RemainingPays value after the query like this:

var ds = XDocument.DataObjects["PayPeriodsDataSource"].DOM;
var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@RemainingPays').text;

I put the 2 var codes after dc.query() and got the error below.

The following error occurred:

Object required
File:script.js
Line:43

Line 43 is

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@RemainingPays').text;

 Could you paste the section where you are currently seting the query text?

dc.Query();

var ds = XDocument.DataObjects["PayPeriodsDataSource"].DOM;

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@Remainingpays').text;

dc.Command = originalCommand;    // restore the original command to the data source

Where's the part where you're setting the query text?