Empty Field List

[jswota]

Hi,

I have created several table in a dataset using existing stored procedures. In the TableAdapter Configuration Wizard, when you specify the stored procedure for the Select command, a list of returned fields is displayed to the right in the parameters grid. This is not the case when using dynamic stored procedures that use the EXEC method to return data. After specifying this stored procedure, no fields are displayed in the Wizard.

Can someone please tell me how i can convert a stored procedure that utilizes the EXEC method into one that returns a table that will populate the list in the Wizard?

Thanks.

 

[jswota]

Just to elaborate.

The actual problem i am facing is this:

I have stored procedures that return certain fields. On top of this, i am defining an auto-incrementing ID column within the DataTable itself. I only want this ID to link my grid rows to the resultset so i can "Print Selected Items".

 Me.ds_Report.MaterialProcessing.DefaultView.RowFilter = String.Format("[ID] IN ({0})", <Function that returns selected ID's>))

Each time the TableAdapter.Fill method is called the ID's are reset for the returned data.

This approach works fine only for the stored procedures that use simple Select statements. It doesn't seem to work for dynamic stored procedures that use the EXEC method. When i preview the data for simple procedures, the ID is displayed in the resultset. This is not the case for the dynamic procedures.

Hope this sheds some light on the matter.

Thanks in advance for the help.

 

[cjard]

Using the IN operator is often quite a lame way to do something.
If you want to have multiple documents print under one job id, make another table, join it in, and specify the job id, without using EXEC and dynamic built sql:

SELECT job_id.NextVal INTO <jobId> FROM dual --this is oracle syntax for how we get a sequence counter. Convert for your DB

--print docs 1 thru 4
INSERT INTO printJobs(docId, jobId) VALUES(1, <jobId>)
INSERT INTO printJobs(docId, jobId) VALUES(2, <jobId>)
INSERT INTO printJobs(docId, jobId) VALUES(3, <jobId>)
INSERT INTO printJobs(docId, jobId) VALUES(4, <jobId>)

SELECT * FROM docs d INNER JOIN printJobs j ON d.docid = j.docid WHERE jobId = <jobId>


Now we have:
A record of all the jobs sent for print etc
Dynamism ; just insert more printjob rows under the same jobid
An SQL that doesnt need dynamic concat and can return a strongly typed cursor for datasets to work properly

 

[cjard]

ps dont use max(jobid)+1 for your sequence, cos until you insert your first doc/job mapping another user can get the same jobid

use a proper sequence counter, or whatever sqlserver uses for that

 

[jswota]

Thanks. Already figured it out though. I just had to create a table variable in the sp that the EXEC command adds the data to. Then select from this table and you will have an sp that provides a list of fields to an ado.net datatable. Another option is to simply add the columns to the datatable in the dataset designer. Certain functions in SQL may seem "lame" but sometimes they are exactly what is needed.

Thanks again.