Solved

Need dsn-less connection to Access database

Posted on 2006-06-20
19
335 Views
Last Modified: 2010-05-18
I have a Access database. I would like to connect to this database from a web page and query the database with the results printed out on the web page. What would be the best way to do this. Thanks.
0
Comment
Question by:valicon
  • 9
  • 8
  • 2
19 Comments
 
LVL 12

Accepted Solution

by:
fruhj earned 250 total points
ID: 16942464
Hi valicon,
  see www.connectionstrings.com for a great list of connection strings,
  They have specific examples of using access databases with asp.net

  Do you also need help with the code? If so, what development environment are you using - things changed a bit between .net 1.1 and .net 2.0 so the approach you take to do the display would differ slightly dependant on that.

Thanks!
0
 
LVL 12

Author Comment

by:valicon
ID: 16942614
Once I get the connection code correct I just need a snippet of code to both query the table and return the results via  a web page and to list the table on a web page. It is a very basic Access database.
0
 
LVL 12

Author Comment

by:valicon
ID: 16942692
It is ASP 2.0 running on a XP Pro box right now...
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16942750
Hey Valicon,

Check out the samples here on the asp.net website

http://www.asp.net/learn/howdoi/default.aspx?tabid=63  The "data driven web sites" example looks like it would have what you're looking for.  I like that they have a video there - so you can see the tools in action.


This page had a few more advanced examples...
http://www.asp.net/learn/dataaccess/default.aspx?tabid=63
0
 
LVL 12

Author Comment

by:valicon
ID: 16942829
That appears to be a great resource but I do not have any software to do this with. I know that it can be done with just code. What is the code that is needed?
0
 
LVL 7

Assisted Solution

by:kGenius
kGenius earned 250 total points
ID: 16942909
You can download Visual Web Developer Express Edition for free from http://msdn.microsoft.com/vstudio/express/vwd/download/

With this IDE you write your ASP.NET pages and codes with Intellisense, Drag & Drop, Smart Tags, ... and all the benefits of an IDE

kGenius
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16942919
Next steps depend on your needs.

If this is homework, and you need pure code only, then we have to find a site that shows how to do that.

If you need to get a page done, then we need to get you some free tools to make your job easier.
if thats the case, you can download the free edition of Visual Web Developer at http://msdn.microsoft.com/vstudio/express/vwd/download/
(tutorial videos are at: http://msdn.microsoft.com/vstudio/express/vwd/easytolearn/)

let me know which route you need/prefer - it might take a bit to dig up some examples you can use in notepad, as most everyone uses visual studio to do the basics...

0
 
LVL 12

Expert Comment

by:fruhj
ID: 16942956
Still haven't found any notepad only examples, but a few sites comes to mind that I've used before...

www.411asp.net  (look under how tos)

www.codeproject.com (Awesome site)
0
 
LVL 12

Author Comment

by:valicon
ID: 16942984
Not homework, I am downloading Visual Web Developer Express Edition. Let me try that and see.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:fruhj
ID: 16943057
Cool have fun!
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16943101
From Visual Web Developer Express, you'll be able to drag and drop a few controls to get the results you want.
Since much of this work is done via drag and drop, I think you'd really benefit from the videos.
Lesson 8:  Working with the GridView and FormView should be just what you need. (it probably isn't a bad idea to start with lesson1, but I know you're anxious to get started so have a look at this one first)
0
 
LVL 12

Author Comment

by:valicon
ID: 16945850
This is cool aapplication!  I created an aspx page that displays the data from the Access database. It works fine when I preview it within the IDE. But if I try to access that same page through a browser I get the following error:

Server Error in '/Intranet' Application.
--------------------------------------------------------------------------------

The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\db\VBTEST.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.
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.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\db\VBTEST.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.

Source Error:


Line 1099:            this.Adapter.SelectCommand = this.CommandCollection[0];
Line 1100:            DataSet1.tblTrespassDataTable dataTable = new DataSet1.tblTrespassDataTable();
Line 1101:            this.Adapter.Fill(dataTable);
Line 1102:            return dataTable;
Line 1103:        }
 

Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\intranet\47131b70\82f7bb29\App_Code.wadxwbzj.0.cs    Line: 1101

Stack Trace:


[OleDbException (0x80004005): The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\db\VBTEST.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.]
   System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1054705
   System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.OleDb.OleDbConnection.Open() +37
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +162
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +107
   DataSet1TableAdapters.tblTrespassTableAdapter.GetData() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\intranet\47131b70\82f7bb29\App_Code.wadxwbzj.0.cs:1101

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +296
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +482
   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2040
   System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
   System.Web.UI.WebControls.ListControl.PerformSelect() +31
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
   System.Web.UI.Control.PreRenderRecursiveInternal() +77
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16948440
Thats a pretty common error amongst the first timers (Happened to me once too)

The problem is that you don't have permissions setup for the access folder...

Ok first, you need to set permissions on the folder that contains the MDB (not just the MDB) - this is becuase of the need to create the LDB file - you (or iis) need write permission to do that.

Now figuring out which ID you need to grant permission to, that can be a small challenge.. <Assumption1>
Right click on your application folder from within the IIS admin tool and choose properties.
Look for a 'Directory security' tab then look for a 'edit' button under anonymous access and auth. control section
Very likely, the 'Anonymous access' checkmark is checked.
If thats  the case then you need to grant access to the ID listed next to 'User Name' (Likely it's IUSR_Machinename (replace machine name with your machine name))

So from within XP you need to find the directory with the access database and grant it change permissions to the IUSR_PCNAME account.

That should do it on your PC, and then be prepaired to visit this again if you move it to a server...

On 2003, (IIS version 6) they added the concept of the 'application pool' - you need to look at the pool and see what ID it's set to run as. Then grant that ID to the folder.

Hope this helps

Note about my <assumption1> - IIS 5.1 (XP) can be configured to
0
 
LVL 12

Author Comment

by:valicon
ID: 16951913
I gave the IUSR account full control over the database file and the folder in which it resides but still the same error.  What else can I check?
0
 
LVL 12

Author Comment

by:valicon
ID: 16953913
One more thing.  I need a search box and the ability to search the database. I cannot see how to do this with the tutorials. Do you know how to add this function?
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16959887
Hey Valicon,

the search box would really be a separate question, (this question is pretty long as it is & it'll be more usefull on the search if the answers pertain to the asked question at the top)

Hm,

If the Iusr account has full perms on the folder, then it should work (common mistake is to put the perms just on the file, but you said folder)

Since this is on your XP workstation, I'd double check the permissions one more time, then reboot.
If it were a server I'd do the same, except I'd restart IIS instead of the whole server, but since it's your XP workstation, rebooting shouldn't carry too much of a penalty.
0
 
LVL 12

Author Comment

by:valicon
ID: 16970601
Thanks all. I really appreciate the help.
0
 
LVL 7

Expert Comment

by:kGenius
ID: 16974218
Shouldn't the ASPNET account have full access to the folder?
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16975613
KGenius,

Thanks for bringing that up,

I believe the aspnet account needs perms with server 2003, this seemed to be the default with the introduction of the 'application pool'
On my XP machine, it was IUSR_Machine name.

Valicon was using XP.

But again thanks for the suggestion, if iusr_PC isnt working, then certainly it doesn't hurt to try the aspnet account.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now