I'm trying to populate a Power Apps Data Table with the output from a Stored Procedure after passing just one parameter from Power Apps to SQL Server (ideally with more parameters, but for the sake of getting it to work, just one for now).
The simple setup I have is as follows:
- One SCREEN named snStoredProcTest
- One TEXT INPUT named txtProgName
- One BUTTON named btnRunStoredProc
- One DATA TABLE named dtStoredProcTest
- My Stored Procedure has been connected like an external data source to my Power App and is named ConnName.ppspSearchprog ("Safe to use for galleries and tables?" is checked yes)
- My Stored Procedure has been tested in SQL Server using EXEC and the relevant parameter and returns 101 records with no issue
In the BUTTON's 'OnSelect' Action Property, I have the following:
ClearCollect(
colSearchResults,
ConnName.ppspSearchProgs(
{
progName:(txtProgName.Text)
}
)
)
Please Note: the parameter of the Stored Procedure, "progName:", did populate in the intellisense which leads to me to believe Power Apps can see and is reading the Stored Procedure.
In my DATA TABLE's 'Items' Data Property, I have the following Collection listed:
colSearchResults
Please Note: this is the variable from the ClearCollect function in my BUTTON's 'OnSelect' Action Property.
When I run the app and click the BUTTON with a short text value that has been tested in SQL Server, the Stored Procedure runs with no errors, but no records display, and the Collection only shows (Table: 1 rows) which is nothing more than a question mark under OutputParameters and ResultSets (nothing under ReturnCode).
I'm unable to add the field names to the DATA TABLE unless I bind the table to a data connection which still displays no records.
If you have done this successfully, please advise what I'm missing. I've been at this for 48 hours and cannot find a solution in Microsoft Doc's, AI, or online forums or videos. It seems like it should be pretty straight forward but I'm at a loss.
