Sql select query is only returning last row n times

Question

I'm using webmatrix. My home server is Ubuntu/mono/nginx/fastcgi. My code is simple. I have a MySql database with a table that contains 4 test records.

@{
        var db = Database.Open("wra");
        var sql = "SELECT * FROM Clients";
        var clientinfo = db.Query(sql);
        WebGrid grid = new WebGrid(clientinfo);
}

<div>
    @grid.GetHtml()
</div>

That's it - doesn't get simpler. However, the grid returns only the last record and displays it 4 times (= number of records). I have tested this with other databases and tables with same result. There is no error, so no stack trace. The problem doesn't appear to be webgrid as it only displays the results. Just to be sure, I removed webgrid and just created a table - same result. The problem doesn't appear to be the database as I've tested with other dbs with same result. I also ran the query on the server (using putty) with no probem, so the query should work. I have searched extensively for an answer. I would appreciate any assistance offered. Thanks in advance.

Thanks for your response Mike. I'm coming to the same conclusion. I tested it on my windows machine and the query executed properly. I may have to write the equivalent of the sql query which I am trying to avoid. Any other suggestions? — Rangi Krishnan, Sep 08 '13 at 20:12
I don't know very much about what mono is supposed to support viz. .NET 4.0, but the Database helper and WebGrid both rely on the relatively new `dynamic` type. My guess would be that this is the cause of the issue. Try using ADO.NET and generating your own HTML table from a SqlDataReader. — Mike Brind, Sep 08 '13 at 20:24

Mihai · Answer 1 · 2013-09-08T13:11:56.957

1

<div id="grid">
    @grid.GetHtml()
</div>

Try it with an id or if it doesn't work,remove the div.I have a feeling that grid runs a loop to display the results,and at each iteration the previous result is overwritten.

edited Sep 08 '13 at 13:11

answered Sep 08 '13 at 12:55

Mihai

26,325
7
66
81

1

Thanks for replying. I tried with an id and then with no div. Same result. As mentioned earlier, I don't think it is a webgrid problem. I removed webgrid and replaced it with a table that uses a foreach loop and that gave the same result. I think it is a problem with the query itself not being processed correctly which is really unusual. – Rangi Krishnan Sep 08 '13 at 19:10

score 0 · Answer 2 · edited May 23 '17 at 12:28

After much research, I've discovered that there was only one other post that raised this problem. For completeness, here is the url: Incorrect results when looping over recordset using Webmatrix/Mono/MySQL

After testing in a variety of ways, it seems the problem is with mono not executing the sql query correctly. Given that no one else appears to have logged this as a problem, I can only assume that it is a bug in my installation.

Anyway, as a work around, here is my solution for using webgrid in case others may be interested.

@using System.Collections;
@using System;
@using System.Data;
@using System.Data.SqlClient;
@using System.Dynamic;
@using System.Text;
@using System.Configuration; 
@using MySql.Data.MySqlClient;

@{
   //establish a connection to mysql db using the connection in web.config        
    MySqlConnection dbConn = new  MySqlConnection(ConfigurationManager.ConnectionStrings["myStringName"].ConnectionString);


    //====From here, source data is member table in database=====
    //create a mysql command var to store the sql query and reference to the connection string
    MySqlCommand command1 = new MySqlCommand("SELECT Id, Fname, Lname, Company, Email FROM ClientTable", dbConn);

    //create a mysql adapter to call the command to be executed
    MySqlDataAdapter dap1 = new MySqlDataAdapter(command1);

    //create a dataset to capture the results of the sql query
    DataSet dataset1 = new DataSet();
    //use the adapter to fill data with the results of the query.  
    //ClientTable will be the name of the table in dataset.
    dap1.Fill(dataset1, "ClientTable");

    //iterate dataset to store its info into a list with columnnames
    var clientProfile = new List<dynamic>();
    foreach (DataRow dr in dataset1.Tables["ClientTable"].Rows)
    {
        var obj = (IDictionary<string, object>)new ExpandoObject();
        foreach (DataColumn col in dataset1.Tables["ClientTable"].Columns)
        {
            obj.Add(col.ColumnName, dr[col.ColumnName]);
        }
        clientProfile.Add(obj);
    }

    WebGrid grid = new WebGrid(clientProfile, rowsPerPage:10);
}


   <div id="xyz">
   @grid.GetHtml(tableStyle : "gridtable",
        alternatingRowStyle: "altRow")
   </div>

Well, that's it. Hope it's useful.

Sql select query is only returning last row n times

2 Answers2