?
Solved

How do I use a .NET tableadapter to retreive a scalar value from a Stored Procedure

Posted on 2009-03-30
9
Medium Priority
?
1,149 Views
Last Modified: 2012-06-21
I have been having issues retrieving scalar values from scalar tableadapter queries.

The tableadapter was set up to use an existing, TESTED, storedprocedure.

I can PREVIEW the expected data...

But in retrieving the value in code I keep getting cast type errors.

I have check to make sure the return type of the qure


MyDoubleValue = Convert.ToDouble(this.MyTableAdapter.GetInterestReceived("1O2", "9050",  "9205", PeriodStart, PeriodEnd));

Open in new window

0
Comment
Question by:hertzgordman
  • 5
  • 4
9 Comments
 

Author Comment

by:hertzgordman
ID: 24024250
BTW I have "coded" the function using the SP and the executescalar() technique.

It works but seems like more effort than necessary...
0
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24025195
Need more info, what is inside GetInterestReceived method?
0
 

Author Comment

by:hertzgordman
ID: 24025237
Its a select statement retrieving the sum of a filtered set of transactions.

Would be comparable to adding up the sales for the customer for a date range.

Returns ONE field which is the total field.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 18

Expert Comment

by:philipjonathan
ID: 24025291
Mind to show the code?
0
 

Author Comment

by:hertzgordman
ID: 24025425


Here is the SP (one just like the one above):

CREATE PROCEDURE dbo.GP_TRX_SelectTotalsForLoanTrxTypeSourceDocDateRange(@Segment_1 char(4),
@Segment_2 char(4),
@Segment_4 char(4),
@TransactionTypeCode char(10),
@StartDate datetime,
@EndDate datetime,
@SourceDoc char(11))
AS SELECT     SUM(NetTrxAmount) AS NetTrxTotal
FROM         dbo.GP_TRX_VIEW
WHERE     (Segment_1 = @Segment_1) AND (Segment_2 = @Segment_2) AND (Segment_4 = @Segment_4) AND (TransactionTypeCode = @TransactionTypeCode)
                       AND (TRXDATE BETWEEN @StartDate AND @EndDate) AND (SOURCDOC = @SourceDoc)

GO
0
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24025500
Mm, I mean the code inside GetInterestReceived method. I suspect the Convert.ToDouble should go in there
0
 

Author Comment

by:hertzgordman
ID: 24025526


MyReturn = Convert.ToDouble(this.GP_TRX_SelectTotalsForLoanTrxTypeSourceDocDateRange(
"1O2", "9050",  "9205", "INTREC",PeriodStart, PeriodEnd,"CMTRX"));
 
0
 
LVL 18

Accepted Solution

by:
philipjonathan earned 2000 total points
ID: 24025578
It calls the SP directly? I'm afraid you can't do that, you'd need to use SqlConnection and SqlCommand objects to do that. Try this:

string connectionString = "your connection string here ...";
SqlConnection connection = new SqlConnection(connectionString);
try
{
  connection.Open();
 
  string cmdText = "GP_TRX_SelectTotalsForLoanTrxTypeSourceDocDateRange";
  SqlCommand cmd = new SqlCommand(cmdText, connection);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("@Segment_1", SqlDbType.Char, 4).Value = "1O2";
  cmd.Parameters.Add("@Segment_2", SqlDbType.Char, 4).Value = "9050";
  cmd.Parameters.Add("@Segment_4", SqlDbType.Char, 4).Value = "9205";
  cmd.Parameters.Add("@TransactionTypeCode", SqlDbType.Char, 10).Value = "INTREC";
  cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = PeriodStart;
  cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = PeriodEnd;
  cmd.Parameters.Add("@SourceDoc", SqlDbType.Char, 11).Value = "CMTRX";
 
  object total = cmd.ExecuteScalar();
  return Convert.ToDouble(total);
}
finally
{
  connection.Close();
}

Open in new window

0
 

Author Closing Comment

by:hertzgordman
ID: 31564585
That's what I eventually did and it seemed to work.

Need verification on this.

Thanks!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question