Unanswered

Error in Canvas App: "Invalid Argument Type" When Calling Stored Procedure

(1) Share

Report

Posted on by CU21102136-0

Context:

The stored procedure works fine in Power Automate, but I encounter an issue in Power Apps when trying to store the result in a variable or collection. The error occurs when accessing .Table1 from the result set.

Power Fx Formula Where the Error Occurs:

I’ve tried using ParseJSON() to convert the result set to a typed object, but I still get the "Invalid argument type" error when trying to access fields from .Table1. Even when storing the result in a variable instead of a collection, I face the same issue.

Attempts to Resolve:

I've tried using Text(), Value(), and even ForAll() to convert and extract values from the result set, but I continue to encounter the error.
I followed the documentation on handling untyped results, but still cannot properly parse the JSON data and store it in a variable.

Here’s an overview of what I’m doing:

Retrieving JSON Data: I am calling a stored procedure and storing the JSON output in a variable:

Set(varsiteproviders, callstoredprocedure(site_id));
Extracting the Table from JSON Data:

3. Attempting to Parse and Store in a Variable:

Set(varTypedTable, ForAll(varsiteproviderstable, { Field1: ThisRecord.Field1, Field2: ThisRecord.Field2 }));

However, I get the error message "Formula contains data where indent is expected" when trying to set the varTypedTable variable.

I have verified that varsiteproviders and varsiteprovidertable contain valid JSON data, and Power Automate successfully runs the same stored procedure. However, I’m trying to eliminate Power Automate due to performance concerns.

Expected Outcome:

I would like to store the result of the stored procedure in a Power Apps variable or collection and access the table’s fields without encountering the untyped object error.

Question:

How can I properly handle the stored procedure result in Power Apps to avoid this error? Should I convert the result into another format or use different Power Fx functions to handle the untyped object?

Any guidance or alternative approaches would be appreciated! Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Ravindra Jadhav 342 Moderator on at

Like (2)

Report
Run the stored procedure:

Set(varsiteproviders, callstoredprocedure(site_id));

Access the table from the result:
Use First(varsiteproviders).Value to retrieve the untyped table.

Display data in a gallery:
In a gallery, set the label's Text property to ThisItem.ColumnName to access fields from the untyped object.

This approach should help avoid the "Invalid argument type" error and allow you to work with the stored procedure result in Power Apps.

Please Closed the Question, Mark it Solved

If my answer helped resolve your issue, please consider marking it as solved to assist others facing the same problem. Additionally, giving it a like would be greatly appreciated and motivates us to keep helping

Thank You

Ravindra Jadhav

Please Subscribe to https://www.youtube.com/@jadhav_ravi_oo7

Connect On https://www.linkedin.com/in/ravindra-jadhav-powerplatform/

Was this reply helpful? Yes No
Suggested answer

Meekou 200 Moderator on at

Like (2)

Report
For accessing data from Stored procedure by PowerApps, there is no need to use ParseJson to convert the result, in general it will return table directly.

Something like

You need to access by ResultSets.Table1.

For your original code, do not need to use ParseJson, for your appempts, make your variable match like below:

Set(varsiteproviders, meekoudb.dboGetUsers()); Set(varsiteprovidertable, varsiteproviders.ResultSets.Table1); Set(varTypedTable, ForAll(varsiteprovidertable, {UserName : ThisRecord.UserName, UserAge: ThisRecord.UserAge}));

Best Regards,

Meekou

Private Support: support@meekou365.onmicrosoft.com

Was this reply helpful? Yes No
Suggested answer

timl 36,383 Super User 2025 Season 2 on at

Like (2)

Report

From what you you describe, it sounds as though your Stored Procedure returns JSON - possibly because you've used "For JSON" in your call to Select.

https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16&tabs=json-path

Can you confirm whether that's the case? If so, that would explain the issue the behaviour that you're seeing.

If that's the case, if it's possible for you to modify your stored procedure to not return JSON, that would be the easiest way to resolve this problem.

Was this reply helpful? Yes No
CU21102136-0 4 on at

Like (1)

Report
@meekou I tried your method but still getting same error

If I am trying to call SP directly into collection I am getting following error:

Set(varsiteproviders, meekoudb.dboGetUsers()); Set(varsiteprovidertable, varsiteproviders.ResultSets.Table1); Set(varTypedTable, ForAll(varsiteprovidertable, {UserName : ThisRecord.UserName, UserAge: ThisRecord.UserAge}));

Was this reply helpful? Yes No
Meekou 200 Moderator on at

Like (1)

Report
@CU21102136-0, could you share your defination of Stored Procedure, what data did you return?

I test with return table data which can access Table1, and here is my test Stored Procedure

CREATE PROCEDURE GetUsers AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here SELECT * From tblUser END GO

Share a demo Stored Procedure which can reproduce your issue.

Best Regards,

Meekou

Private Support

Was this reply helpful? Yes No