how to populate a table in HTML from SQL query

Question

I have a basic html page that I want to display a table on, populated from a SQL query.

I want to display all part numbers that are classified as essential items, that are out of stock.

I'm using CSHTML, razor syntax on webmatrix. I tried this:

var nilstock = db.QueryValue("select STOCKCODE, TOTALSTOCK from dbo.STOCK_ITEMS where STOCK_CLASSIFICATION in(170,190,200) and TOTALSTOCK <1 order by STOCKCODE");

I then call this var later, by@nilstock.ToString() or even just @nilstick but it returns only the first affected cell.

Any help gratefully accepted..:)

I'm not familiar with this stack but it seems to me like QueryValue would return a single value instead of a full result set. — jraede
– jraede, Commented Dec 11, 2013 at 22:49
No. Still gives me a single result: the first part number in the results. — Bevan
– Bevan, Commented Dec 11, 2013 at 22:57
Whoops, correct response : now runs error: System.Collections.ObjectModel.ReadOnlyCollection`1[System.Object] — Bevan
– Bevan, Commented Dec 11, 2013 at 23:09

Polynomial · Accepted Answer · 2013-12-11 23:20:20Z

1

You need to change your query from a single value to return a whole dataset, then iterate through the items as in Stefan's answer. The whole chunk of code should look like this:

@{
    // Open database connection etc
    var nilstock = db.Query("select STOCKCODE, TOTALSTOCK from dbo.STOCK_ITEMS where STOCK_CLASSIFICATION in(170,190,200) and TOTALSTOCK <1 order by STOCKCODE");
}

<table>
@foreach(var row in nilstock)
{
    <tr>
        <td>@row.STOCKCODE</td>
        <td>@row.TOTALSTOCK</td>
    </tr>
}
</table>

answered Dec 11, 2013 at 23:20

Polynomial

3,70427 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bevan Over a year ago

Yes! Thanks also to @stefan for his help

Bevan Over a year ago

@jraede we've got our answer - you weren't far off!

Polynomial Over a year ago

@Bevan Yeah, it was the two answers together that you needed! For reference, your database command methods are db.Execute() for things that return nothing but number of rows affected (inserts, etc), db.QueryValue() for queries that return a single value from a single row, db.QuerySingle() that returns a single row and, as in this answer, db.Query() which can return any number of fields and any number of rows.

Bevan Over a year ago

Thanks! Good follow thru. I'll store those, coz I'll be bound to need them soon!

Stefan · Accepted Answer · 2013-12-11 22:58:05Z

1

Not that familiar with how you query the db, but you probably have iterate over the result set like this:

<table>
    @foreach(var n in nilstock)
    {
        <tr><td>@n.STOCKCODE</td><td>@n.TOTALSTOCK</td></tr>
    }
<table>

answered Dec 11, 2013 at 22:58

Stefan

1,7492 gold badges15 silver badges27 bronze badges

2 Comments

Bevan Over a year ago

Hmmm... Now it crashes with reason: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'char' does not contain a definition for 'STOCKCODE'

Bevan Over a year ago

I'm sure you're on the right track though, because it's trying to do it, there just seems to be some declaration bug somewhere...

Collectives™ on Stack Overflow

how to populate a table in HTML from SQL query

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related