We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Programmatically bind GridView to Access 2003 Database

BoggyBayouBoy
on
Medium Priority
400 Views
Last Modified: 2012-08-13
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???
Comment
Watch Question

Commented:

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

Regards,
Nehaya

Author

Commented:
Thanks Nehaya, do you know the syntax for sending the database password over??
Commented:
// 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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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();
    }
}
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.
ALso try change the last two lines to

GridView1.DataSourceID = db;
GridView1.DataBind()

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.