TableAdapter in DataSet Designer not displaying fields from Stored Procedure

Using Visual Studio 2005, Visual Basic-
I am attempting to modify one of the TableAdapters in my DataSet.  This tableadapter is tied to four (4) stored procedures, one each for Select/Insert/Update/Delete.  In the window where you select the existing stored procedures, there are no fields listed for the 'Select' stored procedure - it is blank.  The other stored procs all show their fields.  This prevents me from updating the tableadapter to changes I made.  I added a field to 3 of the 4 sprocs.

I am loathe to delete and re-create the tableadapter because of possible corruption in the project.  That will cause many, many errors...

Any ideas?  
mayfran-itAsked:
Who is Participating?
 
RobertRFreemanCommented:
Well, if it works without the 10, then generate it that way, then change the proc back when done.
0
 
RobertRFreemanCommented:
The select procedure returns a multiple row result set, so it does not list out the fields, it just uses whatever is in the last select statement in the stored proc.    The fields are only defined on the table.  Modify the stored procedure in the database and then just add a column to the DataTable in your DataSet.  You may also want to set the column mappings property on the datatable.
0
 
mayfran-itAuthor Commented:
OK, this is weird.  I went back to a previous version of the stored procedure and restored it to that and now the fields show up.  I then made the changes I had in the original one and it still works!  So I don't know what I did to make it not work in the first place.  Arghhh!

Thanks for your help,
Jim
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mayfran-itAuthor Commented:
Ok, I have tracked down what is causing this...

It all boils down to the RETURN statement.  When I change it to RETURN(10) from just RETURN, it causes the problem in VS - (you can't see any of the fields in the DataSource Configuration Wizard).  If I change it back to RETURN, the fields show up... !  I don't get it.  Either way is valid, and I would like to have the return code, but I can do without it.  The funny thing is that I have this working in other stored procedures.  Anyways, here is part of the procedure code:

ALTER PROCEDURE GetJobHeader
      @JobNumber CHAR(8) = NULL,
      @Success INT = 0 OUTPUT,
      @RetMessage varchar(150) = '' OUTPUT
AS
SET NOCOUNT ON

IF ISNULL(@JobNumber, '') = ''
      BEGIN
      SET @Success = 0
      SET @RetMessage = 'Job Number required.'
      RAISERROR (@RetMessage, 16, 1)
      RETURN(10)       <--------------------this is the line that causes the problem
      END
ELSE
      SET @JobNumber = UPPER(@JobNumber)

SELECT
      RTRIM(I.JOB_NUMBER) AS JOB_NUMBER,
      RTRIM(I.CONTRACT_JOB) AS CONTRACT_JOB,
      RTRIM(I.CUSTOMER_NUMBER) AS CUSTOMER_NUMBER,
.......and more selects......

Thanks,
Jim

0
 
RobertRFreemanCommented:
By field, do you mean parameter?
What did you add?
0
 
mayfran-itAuthor Commented:
No, I'm talking about the fields in the select statement.  For the above procedure they would be: JOB_NUMBER, CONTRACT_JOB, CUSTOMER_NUMBER....  When I change the RETURN to RETURN(10), these fields do not show up in the TableAdapter Configuration Wizard.

Jim
0
 
RobertRFreemanCommented:
I think this is because the wizard is evaluating the first part of the if statement and exiting before the select is generated.
Try changing @JobNumber CHAR(8) = NULL to @JobNumber CHAR(8) = [some job number]
before you generate the select statement.  Then change it back when you are done.
0
 
mayfran-itAuthor Commented:
No that didn't work.  It seems to depend on the RETURN statement.  This may be a bug in the TableAdapter Configuration Wizard.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.