Programmatically bind GridView to Access 2003 Database

this code throws errors, but sort of communicates what I'm trying to do.  In short, i want to bind a ASP.NET 2.0 GridView to an access database source upon a command button click event.

 protected void Button1_Click(object sender, EventArgs e)
    {
        AccessDataSource db = new AccessDataSource();
        db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TmpSteel.mdb;database password = 1n_t@e!r^m+ez-z0";
        AccessDataSourceView ds = new AccessDataSourceView();
        ds.SelectCommand = "SELECT * FROM Table1";
        GridView1.DataSource = ds.SelectCommand;
        GridView1.DataBind();  
    }


any suggestions for how I bind to a password protected access database at runtime???
LVL 1
BoggyBayouBoyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nehayaCommented:

AccessDataSource db = new AccessDataSource();
db.DataFile = @"c:\TmpSteel.mdb";
db.SelectCommand = "Select * from Table1";
GridView1.DataSource = db;
GridView1.DataBind();

Regards,
Nehaya
BoggyBayouBoyAuthor Commented:
Thanks Nehaya, do you know the syntax for sending the database password over??
nehayaCommented:
// this works for Access with password
        SqlDataSource db = new SqlDataSource();
        db.ConnectionString = "ACCESS CONNECTION STRING HERE WITH PASSWORD";
        db.SelectCommand = "SELECT COMMAND HERE";
        GridView1.DataSource = db;
        GridView1.DataBind();

P.S.: this link to Microsoft note that it's not good thing to use Access on production.. use the SqlServer (express edition is free) for better performance, availability and integeration:
http://www.asp.net/QuickStart/aspnet/doc/ctrlref/data/accessdatasource.aspx


Regards,
Nehaya

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

BoggyBayouBoyAuthor Commented:
Thanks Nehaya,

I'm still throwing an error  -- do you see anything wrong with my connection string??  The compiler does not like the "Provider" portion of the connection string.

public partial class _Default : System.Web.UI.Page
{
      protected void Button1_Click(object sender, EventArgs e)
    {
        SqlDataSource db = new SqlDataSource();
        db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Inventory.mdb; Database Password=hoover";
        db.SelectCommand = "SELECT * from Products";
        GridView1.DataSource = db;
        GridView1.DataBind();
    }
}
GavinMannionCommented:
I don't think you are meant to use the SqlDataSource?

I would think your original code is close

AccessDataSource db = new AccessDataSource();
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TmpSteel.mdb;database password = 1n_t@e!r^m+ez-z0";
db.SelectCommand = "SELECT * FROM table";
GridView1.DataSource = db.SelectCommand;
GridView1.DataBind();  

I don't work much in access but if you can give this a try and let us know what the error is that is returned I am sure we can fix it.
GavinMannionCommented:
ALso try change the last two lines to

GridView1.DataSourceID = db;
GridView1.DataBind()
nehayaCommented:
Gavin,
First, AccessDataSource doesn't allow you to change the connection string. If you need to set a password, use SqlDataSource..
Second, DataSourceID is a string value, not a real datasource!


..
BoggyBayouBoy
I don't see the problem ( but try copy the mdb file into you App_Data folder and give security permsission to user (aspnet) to eliminate the possible security errors.. and change the file path in you connection string..

If it doesn't work, give me the exception message to discuss it further..

Regards,
Nehaya
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.