Alif Noushad Alif Noushad - 2 months ago 18
ASP.NET (C#) Question

Calling a Query that Returns in MVC 5

I am a beginner in MVC 5. I want to display a list of selected data from database .I've created a controller action that used raw query and an IEnumerable class or model. But the Controller returns an error:


An exception of type 'System.Data.SqlClient.SqlException' occurred in
EntityFramework.SqlServer.dll but was not handled in user code

Additional information: Invalid object name 'tblProduct'.


My Model is :

namespace ShoppingCartProject1.Models
{
public class tblProduct:IEnumerable<tblProduct>
{
[Key]
public int PId { get; set; }
public string Pname { get; set; }

List<tblProduct> mylist = new List<tblProduct>();
public IEnumerator<tblProduct> GetEnumerator()
{
return mylist.GetEnumerator();
}

IEnumerator IEnumerable.GetEnumerator()
{
return this.GetEnumerator();
}
public tblProduct this[int index]
{
get { return mylist[index]; }
set { mylist.Insert(index, value); }
}
}
}


Controller is:

[HttpPost]
public ActionResult SearchResult(string searched)
{
if (searched== null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}

// Commenting out original code to show how to use a raw SQL query.
//Department department = await db.Departments.FindAsync(id);

// Create and execute raw SQL query.
string query = "select * from tblProduct where FREETEXT (Meta, 'branded')";
IEnumerable<tblProduct> data = db.Database.SqlQuery<tblProduct>(query);

if (data == null)
{
return HttpNotFound();
}
return View(data.ToList());
}
}
}


the error is seen pointing toward the return statement.

and the View :

@foreach (var item in Model)
{
<img src="~/images/@item.Image1" alt="" class="pro-image-front">
<img src="~/images/@item.Image2" alt="" class="pro-image-back">
}


what I did :


  • checked controller through Breakpoints:
    The data element doesn't get any value from database.

  • changed the model functions.


Answer

It looks like the table name you're after isn't "tblProduct" - I'm guessing it's more likely to be "tblProducts", so if you amend your SQL query it should work.

This is assuming you've created that table in the sql database - hard to tell without looking at the schema

string query = "select * from tblProducts where FREETEXT  (Meta, 'branded')";
IEnumerable<tblProduct> data = db.Database.SqlQuery<tblProduct>(query);

ps. the error looks like it's coming from the return statement because that's where the query is getting actually executed against the db.