Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Problem with mapping a stored procedure to a TableAdapter class

(Using .NET 2.0)
I have a stored procedure as follows:
ALTER PROCEDURE dbo.GetUnallocatedHours
      (
      @employeeName varchar(200)
      )
AS
      declare @numHours real
      SET NOCOUNT ON
      SET @numHours = (SELECT SUM(HoursTotal) FROM ClockCard WHERE Employee = @employeeName)
      RETURN @numHours

Then, using the DataSet designer I mapped thie stored procedure to the ClockCard DataTable, giving it the name GetUnallocatedHours, and in the "shape of the data returned" part of the wizard, I chose to return "A single value" (the second option). In the designer, if I right-click this new query and choose 'Preview Data' it works as expected. But when I try to call it from the code below, I get the famous error "object reference not set to an instance of an object":

MessageBox.Show(clockCardTableAdapter.GetUnallocatedHours(employeeName).ToString());
this doesn't work either: MessageBox.Show(Convert.ToInt32(clockCardTableAdapter.GetUnallocatedHours(employeeName)).ToString());

When I hover over the 'GetUnallocatedHours' in the code above, IntelliSense displays: int? ClockCardTableAdapter.GetUnallocatedHours(string employeeName) which seems right although I don't understand the question mark.

The function works correctly because it works in the designer. But how do I correct my code to call it correctly from there?
0
smickell
Asked:
smickell
  • 3
1 Solution
 
NunoGodinhoCommented:
Hi,

 Before you user this:
     MessageBox.Show(clockCardTableAdapter.GetUnallocatedHours(employeeName).ToString());
  Are you initializing the clockCardTableAdapter = new ClockCardTableAdapter();?

  It could be it.
0
 
smickellAuthor Commented:
I am indeed. A few lines before it in the same method I use:
DataSet1TableAdapters.ClockCardTableAdapter clockCardTableAdapter = new DataSet1TableAdapters.ClockCardTableAdapter();

I tried removing this and dragging a TableAdapter class onto the form in the designer but still no go.
employeeName is also set correctly as I have tested it.

*Note - I only get the above error message if I use clockCardTableAdapter.GetUnallocatedHours(employeeName).GetValue().
If I try clockCardTableAdapter.GetUnallocatedHours(employeeName).Value the exception says 'Nullable object must have a value.'
.HasValue returns false so in other words the method is returning null, not an object reference error.
Convert.ToSingle and Convert.ToInt32() both return 0 whereas if testing the procedure in the DataSet designer, it should be the correct value of 9.
0
 
smickellAuthor Commented:
Never mind, I spent an hour or three fiddling around with the settings. It was down to a combination of the code in the stored procedure and the way it was  mapped to the tableadapter. The parameter settings were mainly at fault.
0
 
smickellAuthor Commented:
The code for the stored procedure was:
ALTER PROCEDURE dbo.GetUnallocatedHours
     (
     @employeeName varchar(200)
     )
AS
   declare @numHours real
   SET @numHours = (SELECT SUM(HoursTotal) FROM ClockCard WHERE Employee = @employeeName)
   RETURN @numHours

For the stored procedure to work correctly when mapped to a TableAdapter I had to have the return statement as a SELECT, I couldn't return a parameter. This is weird because as I said, it works when previewing the mapped method from the designer. Anyway, the code that worked is simply:

ALTER PROCEDURE dbo.GetUnallocatedHours
     (
     @employeeName varchar(200)
     )
AS
SELECT SUM(HoursTotal) FROM ClockCard WHERE Employee = @employeeName

Easy as that.
0
 
GhostModCommented:
Closed, 500 points refunded.

GhostMod
Community Support Moderator
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now