March 6, 2012 at 6:18 am
Hi All,
I'm trying to use one procedure for two different Datasets, by passing a parameters (one of the parameters is an id and the second one is used to switch between the two Datasets).
But when I try to run the stored procedure in the Dataset properties eg. EXEC procedureName @ID, 1, I get the following error "The EXEC SQL construct or statement is not supported.". Would anyone let me know how to resolve this issue please?
Thank you!
March 6, 2012 at 7:44 am
Rather than use a text based query with EXEC spname, just set the dataset to use a stored procedure directly. There is an option in the dataset dialog box (not the query builder) that allows you to use a stored procedure.
March 6, 2012 at 8:11 am
Daniel Bowlin (3/6/2012)
Rather than use a text based query with EXEC spname, just set the dataset to use a stored procedure directly. There is an option in the dataset dialog box (not the query builder) that allows you to use a stored procedure.
Thanks for your reply!
The reson why I chose to use the text based query is because I need the two options to be switched, so I've put a default Parameter for each Dataset, for example; for Dataset1 it is: exec spname @id 1(which is a defult value, if the user chooses Dataset1) and it would be the same for Dataset2 eg. exec spname @id 2 and it will populate all the columns for Dataset2. If thats any clear?
Thank you!
March 10, 2012 at 1:48 pm
In the text builder itself write the sp name only without using exec statement and put question mark "?" for the parameters.
stored procedure ?,?,?,?... (no of parameters = no of inputs for the sp)
then go to the parameters option in the dataset properties itself and set the parameters against the "?" in the same order.
Hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply