InfoPath Execute Stored Procedure Error

[sam1996]

Hi All,

I am frustrated with trying to execute a stored procedure from InfoPath. I
am trying to filter out a table into a repeated table form and the stored
procedure does not execute. Any help or direction on this issue will be
appreciated. I have looked at most all of the infopath forums about executing
a sp and still cannot make it work. I do not know what I am doing wrong...

Details are below:
bankname is the parameter I am passing into the sp...

Below is the JScript for the filter button on the infopath form..
function CTRL2_8::OnClick(eventObj)
{
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var bankname =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:bankname").text;

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
XDocument.QueryAdapter.Command = 'execute CBOReport ' + "'" + bankname + "'";
XDocument.UI.Alert(XDocument.QueryAdapter.Command);
//Query the Data Source.
XDocument.Query();
}

Here is the stored Procedure:

Create PROCEDURE [dbo].[CBOReport]@bankname varchar(25)
AS
BEGIN
SELECT ACIF, BankName, [Name], SUM(ISNULL(Balance, 0) + ISNULL(Unfunded, 0))
AS Balance, SUM(Counter) AS Counter
FROM tblELCAlertProductionData
where bankname = @bankname
GROUP BY ACIF, BankName, [Name]
HAVING (SUM(Counter) >= 3)
ORDER BY Counter, Balance
END

Below is the InfoPath Error:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
The XML data returned from the data source does not match the XML Schema for
this form. This discrepancy might be caused by the data source returning
multiple rows when InfoPath expects only one.

 

[S.Y.M. Wong-A-Ton]

Is there a schema listed anywhere in the error message? Probably the name
that you used for your table or a field in the stored procedure does not
concord with the name(s) of the corresponding XML node(s) in the data source
of InfoPath. The two must be the same.

 

[S.Y.M. Wong-A-Ton]

Forgot to mention that casing (upper and lower case) in table and field names
DOES matter.

 

[sam1996]

Hi SYM,

Thanks for your response...I have checked the column names both in the
infopath form and the stored procedure and they match. The table has 40
columns and I am only trying to pull in 5 columns into a repeating table
using the sp to filter only specific columns and data. I am using a new field
(bankname) in the form as a parameter that is passed into the sp. Any other
suggestions?

SAM

Forgot to mention that casing (upper and lower case) in table and field names
DOES matter.
---
S.Y.M. Wong-A-Ton

Hi All,

I am frustrated with trying to execute a stored procedure from InfoPath. I
am trying to filter out a table into a repeated table form and the stored
procedure does not execute. Any help or direction on this issue will be
appreciated. I have looked at most all of the infopath forums about executing
a sp and still cannot make it work. I do not know what I am doing wrong...

Details are below:
bankname is the parameter I am passing into the sp...

Below is the JScript for the filter button on the infopath form..
function CTRL2_8::OnClick(eventObj)
{
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var bankname =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:bankname").text;

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
XDocument.QueryAdapter.Command = 'execute CBOReport ' + "'" + bankname + "'";
XDocument.UI.Alert(XDocument.QueryAdapter.Command);
//Query the Data Source.
XDocument.Query();
}

Here is the stored Procedure:

Create PROCEDURE [dbo].[CBOReport]@bankname varchar(25)
AS
BEGIN
SELECT ACIF, BankName, [Name], SUM(ISNULL(Balance, 0) + ISNULL(Unfunded, 0))
AS Balance, SUM(Counter) AS Counter
FROM tblELCAlertProductionData
where bankname = @bankname
GROUP BY ACIF, BankName, [Name]
HAVING (SUM(Counter) >= 3)
ORDER BY Counter, Balance
END

Below is the InfoPath Error:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
The XML data returned from the data source does not match the XML Schema for
this form. This discrepancy might be caused by the data source returning
multiple rows when InfoPath expects only one.

 

[S.Y.M. Wong-A-Ton]

InfoPath usually lists what's wrong with the schema in its error messages.
Could you please post this information if it has been shown in the error
message? This way I might get a better idea of what's going on.

Also, there are fields listed under the dataFields node in the "Data Source"
pane (if you've bound your form to a database). Is your stored procedure
returning all of these fields? If not, try returning all of them.
---
S.Y.M. Wong-A-Ton

Hi SYM,

Thanks for your response...I have checked the column names both in the
infopath form and the stored procedure and they match. The table has 40
columns and I am only trying to pull in 5 columns into a repeating table
using the sp to filter only specific columns and data. I am using a new field
(bankname) in the form as a parameter that is passed into the sp. Any other
suggestions?

SAM

Forgot to mention that casing (upper and lower case) in table and field names
DOES matter.
---
S.Y.M. Wong-A-Ton

Hi All,

I am frustrated with trying to execute a stored procedure from InfoPath. I
am trying to filter out a table into a repeated table form and the stored
procedure does not execute. Any help or direction on this issue will be
appreciated. I have looked at most all of the infopath forums about executing
a sp and still cannot make it work. I do not know what I am doing wrong...

Details are below:
bankname is the parameter I am passing into the sp...

Below is the JScript for the filter button on the infopath form..
function CTRL2_8::OnClick(eventObj)
{
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var bankname =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:bankname").text;

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
XDocument.QueryAdapter.Command = 'execute CBOReport ' + "'" + bankname + "'";
XDocument.UI.Alert(XDocument.QueryAdapter.Command);
//Query the Data Source.
XDocument.Query();
}

Here is the stored Procedure:

Create PROCEDURE [dbo].[CBOReport]@bankname varchar(25)
AS
BEGIN
SELECT ACIF, BankName, [Name], SUM(ISNULL(Balance, 0) + ISNULL(Unfunded, 0))
AS Balance, SUM(Counter) AS Counter
FROM tblELCAlertProductionData
where bankname = @bankname
GROUP BY ACIF, BankName, [Name]
HAVING (SUM(Counter) >= 3)
ORDER BY Counter, Balance
END

Below is the InfoPath Error:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
The XML data returned from the data source does not match the XML Schema for
this form. This discrepancy might be caused by the data source returning
multiple rows when InfoPath expects only one.

 

[sam1996]

Hi S.Y.M.

Thanks for your assistance. The was a uppercase letter in one of the
column names and I corrected it. The sp works now. But what its doing is that
now the repeating table that is in the 3 views I have, the main view which is
the master/detail form and the other two views are reports that use the sp to
pull in filtered data into the repeating tables, the data is showing up in
all three views since they are all bounded by the same table, if I do a query
on the Main view. That is not what I am looking for. So what I did was to use
a secondary data connection on the two report views but now the sp does not
return any data back to the report views and there are no error messages. I
do not undertstand why the secondary data source does not return back the
data set from the stored procedure since its the same table that I am using
in the Main Data Source connection, the column names are the same...Any ideas
why the secondary data source connection using the same table does not
work...Am I doing something wrong in the JScript for the filter button as
shown below...

The sp I am using...

Create PROCEDURE [dbo].[CBOReport]@bankname varchar(25)
AS
BEGIN
SELECT ACIF, BankName, [Name], SUM(ISNULL(Balance, 0) + ISNULL(Unfunded,0))
FROM tblELCAlertProductionData
where bankname = @bankname
GROUP BY ACIF, BankName, [Name]
HAVING (SUM(Counter) >= 3) ORDER BY Counter, Balance END


Below is the JScript for the filter button on the infopath form..

function CTRL2_8::OnClick(eventObj)
{
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var bankname =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:bankname").text;

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
XDocument.QueryAdapter.Command = 'execute CBOReport ' + "'" + bankname +
"'";
XDocument.UI.Alert(XDocument.QueryAdapter.Command);
//Query the Data Source.
XDocument.Query();
}

InfoPath usually lists what's wrong with the schema in its error messages.
Could you please post this information if it has been shown in the error
message? This way I might get a better idea of what's going on.

Also, there are fields listed under the dataFields node in the "Data Source"
pane (if you've bound your form to a database). Is your stored procedure
returning all of these fields? If not, try returning all of them.
---
S.Y.M. Wong-A-Ton

Hi SYM,

Thanks for your response...I have checked the column names both in the
infopath form and the stored procedure and they match. The table has 40
columns and I am only trying to pull in 5 columns into a repeating table
using the sp to filter only specific columns and data. I am using a new field
(bankname) in the form as a parameter that is passed into the sp. Any other
suggestions?

SAM

Forgot to mention that casing (upper and lower case) in table and field names
DOES matter.
---
S.Y.M. Wong-A-Ton


:

Hi All,

I am frustrated with trying to execute a stored procedure from InfoPath. I
am trying to filter out a table into a repeated table form and the stored
procedure does not execute. Any help or direction on this issue will be
appreciated. I have looked at most all of the infopath forums about executing
a sp and still cannot make it work. I do not know what I am doing wrong...

Details are below:
bankname is the parameter I am passing into the sp...

Below is the JScript for the filter button on the infopath form..
function CTRL2_8::OnClick(eventObj)
{
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var bankname =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:bankname").text;

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
XDocument.QueryAdapter.Command = 'execute CBOReport ' + "'" + bankname + "'";
XDocument.UI.Alert(XDocument.QueryAdapter.Command);
//Query the Data Source.
XDocument.Query();
}

Here is the stored Procedure:

Create PROCEDURE [dbo].[CBOReport]@bankname varchar(25)
AS
BEGIN
SELECT ACIF, BankName, [Name], SUM(ISNULL(Balance, 0) + ISNULL(Unfunded, 0))
AS Balance, SUM(Counter) AS Counter
FROM tblELCAlertProductionData
where bankname = @bankname
GROUP BY ACIF, BankName, [Name]
HAVING (SUM(Counter) >= 3)
ORDER BY Counter, Balance
END

Below is the InfoPath Error:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
The XML data returned from the data source does not match the XML Schema for
this form. This discrepancy might be caused by the data source returning
multiple rows when InfoPath expects only one.