CODEWITHSUNDEEP
asp.netrazor
derek
derek

Reputation: 1055

Razor asp.net webpages - displaying all rows from a database

i am trying to develop a web application using razor view engine. It is an vacation request management system where users log onto the web site and submit vacation requests through a web form. All requests are stored in a database table called "LeaveRequests". At the moment i am trying to alter a page so when a user is logged in all the vacation requests they made are displayed on the web page in a table view. the code shown below works fine to display 1 request made by a user but i need to alter it to display all requests made by the user. i have tried using a foreach statement but keep getting errors whatever i try , can anyone point my in the right direction and tell me how i need to alter my code to achieve what i want ? 

            var db = Database.Open("Annual Leave System");

            var dbCommand2 = "SELECT * FROM LeaveRequests WHERE email = @0";
            var row2 = db.QuerySingle(dbCommand2, theEmail);




           if(row2 != null) {


            description = row2.description;
            theLeaveType = row2.leaveType;
            startDate = row2.startDate;
            endDate = row2.endDate;
            shortStartDate = startDate.ToString("dd-MMMM-yyyy");
            shortEndDate = endDate.ToString("dd-MMMM-yyyy");
            inttotalDays = row2.totalDays;
            requestStatus = row2.requestStatus;



        }



            <fieldset>
            <legend>Employee Leave Request Details</legend>
            <table border="1" width="100%"> 



            <tr bgcolor="grey">
            <th>Description</th> 
            <th>Leave Type</th> 
            <th>Start Date</th> 
            <th>End Date</th> 
            <th>Total days leave requested</th> 
            <th>Request Status</th> 
            </tr>
            <tr>

            <th>@description</th>
            <th>@theLeaveType</th> 
            <th>@shortStartDate</th> 
            <th>@shortEndDate</th> 
            <th>@inttotalDays</th> 
            <th>@requestStatus</th> 
            </tr>

            </table>

            </fieldset>

Upvotes: 0

Views: 301

Answers (1)

Mike Brind
Mike Brind

Reputation: 30065

The QuerySingle method will only return one row. You need to use the Query method to get all rows.

var rows = db.Query(dbCommand2, theEmail);

Then in the HTML part of the file:

<fieldset>
    <legend>Employee Leave Request Details</legend>
    <table border="1" width="100%"> 
    <tr bgcolor="grey">
        <th>Description</th> 
        <th>Leave Type</th> 
        <th>Start Date</th> 
        <th>End Date</th> 
        <th>Total days leave requested</th> 
        <th>Request Status</th> 
    </tr>
@foreach(var row in rows){
    <tr>
        <td>@row.description</td>
        <td>@row.leaveType</td>

        <td>@row.startDate.ToString("dd-MMMM-yyyy")</td>
        <td>@row.endDate.ToString("dd-MMMM-yyyy")</td>
        <td>@row.totalDays</td>
        <td>@row.requestStatus;</td>
    </tr>
}
</table>

More information here:

http://www.mikesdotnetting.com/Article/214/How-To-Check-If-A-Query-Returns-Data-In-ASP.NET-Web-Pages http://www.asp.net/web-pages/tutorials/data/5-working-with-data

Upvotes: 1

Related Questions