locked
OLEDB Provider and WebGrid RRS feed

  • Question

  • User-1044114509 posted
    I am bring data in from a MS Access file with the following code. The connection seemed to work fine but it was not able to populate the WebGrid since its constructor need something other than a DataSet. Does anybody know a way around it?
     
    @using System.Configuration;   
    @using System.Data;   
    @using System.Data.OleDb;
    @{ 
        string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\thchen\Projects\WeeklyReport\Demo.accdb;Persist Security Info=False;";
        string MySQL = "SELECT * FROM DETAIL";
        OleDbConnection myConnection = new OleDbConnection(ConnStr); 
        OleDbCommand myCommand = new OleDbCommand(MySQL, myConnection); 
        DataSet dataset = new DataSet(); 
     
        // Open connection 
        myConnection.Open(); 
         
        // Fill DataSet 
        OleDbDataAdapter oda = new OleDbDataAdapter(myCommand); 
        oda.Fill(dataset); 
        
        var grid = new WebGrid(dataset);
     
        // Close connection 
        myConnection.Close(); 
    } 
     
    <!DOCTYPE html> 
     
    <html lang="en"> 
        <head> 
            <meta charset="utf-8" /> 
            <title>Weekly Status Report</title> 
            <style type="text/css"> 
              .grid { margin: 4px; border-collapse: collapse; width: 600px; } 
              .grid th, .grid td { border: 1px solid #C0C0C0; padding: 5px; } 
              .head { background-color: #E8E8E8; font-weight: bold; color: #FFF; } 
              .alt { background-color: #E8E8E8; color: #000; } 
            </style> 
        </head> 
        <body> 
            <h1>Weekly Status Report</h1> 
            <div> 
                @grid.GetHtml( 
                    tableStyle: "grid", 
                    headerStyle: "head", 
                    alternatingRowStyle: "alt", 
                    columns: grid.Columns( 
                        grid.Column("Project"), 
                        grid.Column("Status"), 
                        grid.Column("Title"), 
                        grid.Column("Contact"),
                        grid.Column("FM"),
                        grid.Column("Created"),
                        grid.Column("LastUpdate"),
                        grid.Column("Update")
                    ) 
                ) 
            </div> 
        </body> 
    </html>
    Friday, November 30, 2012 6:04 PM

Answers

  • User3866881 posted

    Well……If you are using AsEnumerable() this will return an array of DataRow collection, which doesn't include columns, and columns just belong to DataTable itself.

    So you should use LINQ to analyze that and do binding:

     var products= from p in table.AsEnumerable() 
                        select new
                        {
                         Project = p["ColumnName"].ToString(),
                         ……………………
                        };
    
    //Please bind "products" to webGrid

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 4, 2012 8:40 PM

All replies

  • User3866881 posted

    var grid = new WebGrid(dataset);

    Hi,

    You cannot use dataset because dataset is a collection of datatables, so you have to use dataset.Tables[0]

    Saturday, December 1, 2012 4:08 AM
  • User-1044114509 posted

    Decker Dong. Thanks for your reply.

    I tried that and now I am getting this error:

    CS1502: The best overloaded method match for 'System.Web.Helpers.WebGrid.WebGrid(System.Collections.Generic.IEnumerable<dynamic>, System.Collections.Generic.IEnumerable<string>, string, int, bool, bool, string, string, string, string, string, string, string)' has some invalid arguments
     

    I looked up the constructor for WebGrid Class on MSDN and it says to use IEnumerable<Object> for the Source of WebGrid

    http://msdn.microsoft.com/en-us/library/system.web.helpers.webgrid.webgrid(v=vs.111).aspx

     

    So it seemed that I am passing something to WebGrid and it does not like. How do I get around that and convert it to something that WebGrid would accept?


     

    Monday, December 3, 2012 10:26 AM
  • User3866881 posted

    says to use IEnumerable<Object> for the Source of WebGrid

    Hi again,

    Any class type must be the son of object, please use AsEnumerable to do conversion:

    dataset.Tables[0].AsEnumerable()

    Monday, December 3, 2012 8:27 PM
  • User-1044114509 posted

    Hi,

    dataset.Tables[0].AsEnumerable() get me past the previoius error message. However, I now have a different error. When I run my code, this is what I get:

    Server Error in '/' Application.


    Column "Status" does not exist.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.InvalidOperationException: Column "Status" does not exist.

    I double check my MS Access database and the column name is spelled correctly. And if I comment out my code like this:

    columns: grid.Columns( 
                        //grid.Column("Project"), 
                        grid.Column("Status"), 
                        grid.Column("Title"), 
                        grid.Column("Contact"),
                        grid.Column("FM"),
                        grid.Column("Created"),
                        grid.Column("LastUpdate"),
                        grid.Column("Update")

    it would then tell me that Column "Status" does not exist. And if I comment out the line with "Status", it would then tell me that Column "Title" does not exist. Any idea? Thanks in advance.

    Tuesday, December 4, 2012 10:23 AM
  • User3866881 posted

    Well……If you are using AsEnumerable() this will return an array of DataRow collection, which doesn't include columns, and columns just belong to DataTable itself.

    So you should use LINQ to analyze that and do binding:

     var products= from p in table.AsEnumerable() 
                        select new
                        {
                         Project = p["ColumnName"].ToString(),
                         ……………………
                        };
    
    //Please bind "products" to webGrid

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 4, 2012 8:40 PM
  • User-1044114509 posted

    I am unfamiliar with LINQ; however, my preliminary researches suggest that it is the way to go. So let me catch up with my readings on the subject and mark the case resolved for now. Thank you for pointing the way. I really appreciate your help.

    Wednesday, December 5, 2012 6:32 PM
  • User3866881 posted

    Hello

    again,

    If your problem is solved, I'll mark this as an answer and close the issue. And if you have anything else urgent, please create a new thread to continue your discussion.

    Wednesday, December 5, 2012 8:38 PM