CODEWITHSUNDEEP
excelrazorwebmatrix
Nibbler
Nibbler

Reputation: 334

Export Filtered webgrid to Excel - Webmatrix

I'm trying to export a filtered webgrid to excel, however when the file is downloaded it gave me all the records from the database, then changed my code to this.

This code is the page where i create the filter:

var db = Database.Open("formIGF");
var query = "SELECT * FROM exportTable  WHERE dataEncomenda LIKE @0 ";
var dataPedido = "%" + Request["dataPedido"] + "%";
var data = db.Query(query, dataPedido);
var columns = new[]{"dataEncomenda"};
var grid = new WebGrid(data, columnNames: columns, rowsPerPage:10);


<form method="post">
<div class="grid">

    <div id="grid">
        <label for="dataRecolha">Data do Pedido</label><input id="dataPedido" name="dataPedido" type="text" value="" placeholder="Selecionar um dia"/>
        <input type="submit" />


            @grid.GetHtml(    
                caption: "Numero Total de Entradas: " + @grid.TotalRowCount + " ",
                tableStyle : "table",
                alternatingRowStyle : "alternate",
                headerStyle : "header",
                columns: grid.Columns(
                                        grid.Column("ID"),
                                        grid.Column("dataEncomenda", "Data da Encomenda")
                )
            )

        <a href="GenerateExcel.cshtml?cond=@Request["dataPedido"]" target="_blank">Exportar para Excel</a>

    </div>
  </div>
</form>

And this is the code for the page that will export to excel:

@{
Layout = null;
var db = Database.Open("formIGF");
var dataPedido = Request.QueryString["dataPedido"];
var data = db.Query(dataPedido);
var columns = data.First().Columns;
Response.AddHeader("Content-disposition", "attachment; filename=report.xls");
Response.ContentType = "application/octet-stream";
}


<table style="border: 1px solid #a59f9f;">
<tr style="font-weight: bold">
@foreach(var column in columns){
    <td style="border: 1px solid #a59f9f;">@column</td>
}
</tr>
@foreach(var row in data){
    <tr>
    @foreach(var column in columns){
        <td style="border: 1px solid #a59f9f;">@row[column]</td>
    }
    </tr>
}
</table>

Then i got an error: Value cannot be null or an empty string. Parameter name: commandText

That means that no value is being passed right ?

On my url i got this:

http://localhost:44552/GenerateExcel.cshtml?cond=04-06-2014

That value should not pass to my querystring ?

Upvotes: 0

Views: 625

Answers (1)

Mike Brind
Mike Brind

Reputation: 30110

You are referencing a query string value that does not exist. You have named it cond, but are using Request.QueryString["dataPedido"] to get the value. That should be 

var dataPedido = Request.QueryString["cond"];

Not only that, but you are then passing the value as a SQL command to the Database.Query method. It is not a valid sql command. You have missed that bit out in your changes:

var query = "SELECT * FROM exportTable  WHERE dataEncomenda LIKE @0 ";
var dataPedido = "%" + Request.QueryString["cond"] + "%";
var data = db.Query(query, dataPedido);

Upvotes: 1

Related Questions