How do I get my stored procedure call in MVC to return data?

Question

0.00/5 (No votes)

See more:

VS2017

I'm running SQL Server Express 2016, SSMS 2017, and MVC6 in Visual Studio Community 2017 on Windows 10.

I was unable to get ReportViewer to work after two weeks of trying, so I created a stored procedure that successfully populates a temporary table exactly as it should be displayed in a report.

As a test, I then created an MVC Index view that displays temporary table perfectly as a report. But of course, that data is static.

What I really need is to drive the Index view from the SELECT statement FROM the temporary table at the end of the stored procedure. I have the following code in my NeededDocsReportController Index ActionResult procedure. It displays the column headers just fine, but no data.

The line that is remarked out above the stored procedure call is the test line that displays 17 rows of data from the temp table. Even if I delete records from that table, the stored procedure successfully re-populates the temp table. The difference is that in the stored procedure call below in the MVC controller, it returns no records, while the same stored procedure in an SQL Query window in SSMS 2017 correctly returns the 17 records.

Is there anything obvious that I am doing wrong, or that I should do, to get the records the come out in MVC? Thank you.

What I have tried:

namespace MVCwithDIYReport.Controllers
{
public class NeededDocsReportController : Controller
{
private ReportDBContext db = new ReportDBContext();

// GET: NeededDocsReport
public ActionResult Index()
{
//return View(db.NeededDocsReports.ToList());
return View(db.sp_docsstatus(true).OfType<neededdocsreport>().ToList());
}

Posted 29-Oct-17 13:21pm

Member 12824529

Updated 26-Nov-17 1:46am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Atlapure Ambrish · Accepted Answer · 2017-10-30T20:55:00

Solution 1

I would run SQL profiler and check what query/call is being fired when you call

db.sp_docsstatus(true).OfType<neededdocsreport>().ToList()

The I would run the same query on DB manually and see still I am getting expected data/behavior.

Posted 30-Oct-17 20:55pm

Atlapure Ambrish

Member 12824529 · Accepted Answer · 2017-11-26T01:46:00

I got the stored procedure call working by using ExecuteSqlCommand as in the controller code below. I ran the stored procedure to retrieve, sort, and load the data into a temporary table, with group columns showing groups only once (because this is a report). Then I display the temporary table in the normal way with a list-type view. It's a great workaround for us mere mortals for whom training videos and walkthroughs on ReportViewer don't work.

// GET: NeededDocsReports
public ActionResult Index()
{
//Refresh the report data
db.Database.ExecuteSqlCommand("dbo.sp_docsstatus", false);
//Display the report
return View(db.NeededDocsReport.ToList());
}