Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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???
0
BoggyBayouBoy
Asked:
BoggyBayouBoy
  • 3
  • 2
  • 2
1 Solution
 
nehayaCommented:

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

Regards,
Nehaya
0
 
BoggyBayouBoyAuthor Commented:
Thanks Nehaya, do you know the syntax for sending the database password over??
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

GridView1.DataSourceID = db;
GridView1.DataBind()
0
 
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
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now