We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

OLEDB Provider and WebGrid[Answered] RSS

7 replies

Last post Dec 05, 2012 08:38 PM by Decker Dong - MSFT

‹ Previous Thread|Next Thread ›

Shortcuts

thchen

None

0 Points

4 Posts

OLEDB Provider and WebGrid

Nov 30, 2012 06:04 PM|thchen|LINK

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>

- Reply
Decker Dong...
All-Star

94130 Points

18109 Posts

Re: OLEDB Provider and WebGrid

Dec 01, 2012 04:08 AM|Decker Dong - MSFT|LINK

thchen
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]
- Reply
thchen
None

0 Points

4 Posts

Re: OLEDB Provider and WebGrid

Dec 03, 2012 10:26 AM|thchen|LINK

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?
- Reply
Decker Dong...
All-Star

94130 Points

18109 Posts

Re: OLEDB Provider and WebGrid

Dec 03, 2012 08:27 PM|Decker Dong - MSFT|LINK

thchen
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()
- Reply
thchen
None

0 Points

4 Posts
Re: OLEDB Provider and WebGrid

Dec 04, 2012 10:23 AM|thchen|LINK
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.
- Reply
Decker Dong...
All-Star

94130 Points

18109 Posts
Re: OLEDB Provider and WebGrid

Dec 04, 2012 08:40 PM|Decker Dong - MSFT|LINK
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
```
- Reply
thchen
None

0 Points

4 Posts

Re: OLEDB Provider and WebGrid

Dec 05, 2012 06:32 PM|thchen|LINK

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.
- Reply
Decker Dong...
All-Star

94130 Points

18109 Posts

Re: OLEDB Provider and WebGrid

Dec 05, 2012 08:38 PM|Decker Dong - MSFT|LINK

Hello

thchen
thchen
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.

‹ Previous Thread|Next Thread ›

OLEDB Provider and WebGrid[Answered] RSS

7 replies

Shortcuts

OLEDB Provider and WebGrid

Re: OLEDB Provider and WebGrid

thchen

Re: OLEDB Provider and WebGrid

Re: OLEDB Provider and WebGrid

thchen

Re: OLEDB Provider and WebGrid

Server Error in '/' Application.

Column "Status" does not exist.

Re: OLEDB Provider and WebGrid

Re: OLEDB Provider and WebGrid

Re: OLEDB Provider and WebGrid

thchen