Retrieving information from a database

Question

I am currently facing a dilemma within the project I am currently completing. I have a database where two tables are linked through foreign keys which are of data type int.

What I am trying to do is retrieve a value from tblProductColour and using the INNER JOIN function add the information to the other table which is tblProducts.

Here is the controller code,

public ActionResult Index() { var db = WebMatrix.Data.Database.Open("Database"); if (Session["AdministrationTeam"] != null) { View = "ViewAllProducts"; } else { return RedirectToAction("Login", "Home"); } return View(View, odb.tblproducts.SqlQuery("SELECT * FROM tblproducts t INNER JOIN tblProductColour ot ON t.ID = ot.ProductColour")); }

The getters and setters that are stored within the model itself are as shown below,

public int? ProductColour {get;set;}

Here is the code I am using in my View to retrieve the information from the model,

@foreach (var item in Model) { <div class="col-lg-3 col-md-6 col-sm-6 "> <img src="@item.ProductImagePath" alt="Image Should Show Here" style="margin:auto auto; background-color:#fff;" width="100%" height="20%"/> <p>Product Id: @Html.DisplayFor(modelItem => item.Id) </p> <p> Product Name: @Html.DisplayFor(modelItem => item.ProductName)</p> <p>Supplier: @Html.DisplayFor(modelItem => item.ProductSupplier) </p> <p>Colour: @Html.DisplayFor(modelIteem => item.ProductColour) </p> <p>Product Quantity: @Html.DisplayFor(modelItem => item.ProductQuantity)</p> <p> Product Description: @Html.DisplayFor(modelItem => item.ProductDescription)</p> </div>

When retrieving the information, I am faced with this error shown below,

Conversion failed when converting the varchar value 'Red' to data type int.

Here are the entities,
tblproducts
tblProductColour
If anyone has any solutions to how I can display the information it would be appreciated.

Which RDBMS is this for? It often does make a difference whether you're using MySQL, PostgreSQL, Oracle, SQL Server or IBM DB2 - or something else even. Please add a relevant tag to your question! — marc_s
– marc_s, Commented Feb 21, 2016 at 16:40
Hi thanks for the reply i am using Sql Server within MVC razor (C#) — Callum311
– Callum311, Commented Feb 21, 2016 at 16:44

GJKH · Accepted Answer · 2016-02-21 17:03:38Z

You're joining the wrong items

"SELECT * FROM tblproducts t INNER JOIN tblProductColour ot ON t.ID = ot.ProductColour"

should be

"SELECT * FROM tblproducts t INNER JOIN tblProductColour ot ON t.ProductColour = ot.ID"

You then need to change

@Html.DisplayFor(modelIteem => item.ProductColour)

in your view to (assuming the correct reference name for the table is tblProductColours) to

@Html.DisplayFor(modelIteem => item.tblProductColours.ProductColour) - or @Html.DisplayFor(modelIteem => item.ProductColours.ProductColour) -

may also work - just depends on how it is referenced, hopefully autocomplete will help you out.

Thanks for you answer But it is only displaying a numeric value aka the id of the other table instead of the data stored into column ProductColour
Without using the sql correction I gave you your code will never work. You should be able to replace item.ProductColour with item.tblProductColours.ProductColour to get the colour name - that's assuming the table is referenced as tblProductColours.

Collectives™ on Stack Overflow

Retrieving information from a database

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related