Connect to Access database in web.config

Question

User1217419278 posted

Hi guys,

Im running a gridview on a page to display users from an Access Database.  This is being run on a hosting server for my website.  The database file is in the root directory. 

In the Web.config fil i have this connection string<connectionStrings>

    <add name="usersConnectionString"
         connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb"
        providerName="System.Data.OleDb" />
  </connectionStrings>

And when I call it to view my users in the grid view I have this statement ON my admin.aspx page

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:usersConnectionString %>" ProviderName="<%$ ConnectionStrings:usersConnectionString.ProviderName %>" SelectCommand="SELECT [username], [EmailAddress] FROM [users]"></asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True">
    <PagerSettings Mode="NumericFirstLast" />
    </asp:GridView>

Can anyone see why this is failing? I think my connectionString is wrong in my web.config.  If the users.mdb is in the root directory on the hosted server shouldn't this work?  Let me know, all your help is greatly appreciated thanks in advance!

Answer 1

User197322208 posted

"SqlDataSource Represents an SQL database to data-bound controls."

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.aspx

You want an accessdatasource

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.accessdatasource.aspx

Answer 2

User1217419278 posted

I was under the impression you could use sqldatasource to work with an Access database as well. 

The code works on my machine, it just doesn' work when on the hosted server so I assumed it couldn't find my users.mdb

directory.I will change the datasource controls though and see what happens. Thanks.

I

Answer 3

User1217419278 posted

Alright it worked thanks soo much!

Answer 4

User-1199946673 posted

I was under the impression you could use sqldatasource to work with an Access database as well. 

 

Off course you can! In fact, the AccessDataSource is a modified SQLDataSource. The problem is your connectionstring. Put the database in the App_Data folder, and change your connectionstring:

<add name="usersConnectionString"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|users.mdb"
providerName="System.Data.OleDb" />

The DataDirectory Directive is pointing to the App_Data folder...

http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

Answer 5

User1217419278 posted

Thanks soo much this is the answer I was really looking for; just because the database is on a  remote server.  I'll try it out and let you know.  Btw the article really helps.Thanks

Answer 6

User-1199946673 posted

just because the database is on a  remote server

 

What do you mean with "a remote server"? Because it's no problem if the database is located on a server if the same network (you need to set the right permissions, read this), but connection to remote server by IP address is only possible in theory, In reality, I never seen this work, because of all kinds of security and permissions problems, because as you can read in the article, the ASP.NET users needs to have Modify permissions on the folder where the database is located.

IN this scenatio, you better start using MS SQL Express

Answer 7

User1217419278 posted

I was referring to just the database on the remote server, but the whole project is on the remote server.  Sorry I wasn't so clear.  I was just saying the project wasn't working on the remote server, but when I would test it locally it would work.  Thats why I wanted to know the proper connectionString to write in the Web.config file.  But as I understand it, from the previous answer: If I put the Access Database in the app_folder on the host server and refer to it using

"Provider=Microsoft.ACE.OleDb.12.0;Data Source=|DataDirectory|myDB.accdb"

it should work.

Answer 8

User-1199946673 posted

Yes, but when using a accdb file, make sure that the ACE OleDb 12.0 driver is installed. Another option is to use a mdb file, because there's no rreal advantage in using a accdb file... 

Answer 9

User1217419278 posted

OK.  I'm lucky there my database is mdb format.

Answer 10

User-1199946673 posted

OK.  I'm lucky there my database is mdb format.

 

But then use the OleDb 4.0 connectionstring....

Answer 11

User1217419278 posted

I did that and it worked. Thanks alot for the help.