?
Solved

Open access database in asp.net.

Posted on 2004-10-08
19
Medium Priority
?
711 Views
Last Modified: 2006-11-17
Hi
I am trying to open an access database in an asp.net page to display some data but get the following error when running the page:

"The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\Practice2\test.mdb'. It is already opened exclusively by another user, or you need permission to view its data".

Please can somebody help?

Thanks
0
Comment
Question by:ladykh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12263975
Sure. Make sure that you don't have any of the tables open in design view. Make certain also that the IUSR account has READ/WRITE permissions to the Practice2 directory.

FtB
0
 

Author Comment

by:ladykh
ID: 12264037
I'm not sure where the IUSR account is (am trying to learn).  Is it when you right click the practice2 directory then properties, then security? Or is it to do with the IIS server?
Thanks
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12264076
Are you running this on your local computer or on a web server? What is the operating system?

FtB
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12264085
In any event, if you right click on the folder, go to properties, and then permissions, that might give you a starting point. If you are working with Windows XP rather than with a server, you may need to disable simple file sharing and then add the IUSR account (READ/WRITE) to the directory in question.

FtB
0
 

Author Comment

by:ladykh
ID: 12264428
Running on IIS Server on with windows xp.  I have disabled simple file sharing and the same error message appears.
0
 
LVL 15

Expert Comment

by:gladxml
ID: 12264976
0
 
LVL 15

Expert Comment

by:Thogek
ID: 12265145
Good points above, particularly the "READ/WRITE permissions to the Practice2 directory" that Fritz suggested, which is usually the culprit in cases like this (that I've seen).

Also, even if it's not part of your current problem, do make sure that your code that opens the Connection object also closes that Connection when it is done with it.  Otherwise, the MS Access lock file (LDB) that is created upon the Connection's opening may take awhile to get cleaned up, and successive attempts to access the database in the meantime may suffer a similar error.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12266114
Thogek makes a very good point there--one that is too often ignored.

These articles are more geared to classic ASP but relate to the problems that you are experiencing:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306518
http://www.webwizguide.info/asp/faq/access_database_faq.asp


FtB
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 12267050
All of these above were true for ASP pages and partially for .net pages. I guess he/she talks about .net pages, so in that case you will also need to allow ASPNET user to use this mdb.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12267438
That's true, but that account is installed automatically with the .Net framework.

FtB
0
 

Author Comment

by:ladykh
ID: 12271255
Sorry for late reply but have been trying to get it to work but still comes up with same error.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12271350
Actually, I may be wrong about Jit's comment. Please see:

http://www.dotnet247.com/247reference/msgs/45/228153.aspx

FtB
0
 

Author Comment

by:ladykh
ID: 12271381

I just have a very simple page with a button which when clicked I would like the data to be displayed in a datagrid.  The above error appears when the button is clicked.  Is it anything in the code that is the problem?  The only bit I hand coded was near the bottom when button1 is clicked.


Imports System.Data.OleDb
Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
        Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
        Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
        Me.DataSet11 = New Practice2.DataSet1
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'OleDbDataAdapter1
        '
        Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
        Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
        Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
        Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblAlumni_pref", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("email_direct_pref", "email_direct_pref"), New System.Data.Common.DataColumnMapping("photos_pref", "photos_pref"), New System.Data.Common.DataColumnMapping("skills_direct_pref", "skills_direct_pref"), New System.Data.Common.DataColumnMapping("student_id", "student_id"), New System.Data.Common.DataColumnMapping("student_name", "student_name")})})
        Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
        '
        'OleDbDeleteCommand1
        '
        Me.OleDbDeleteCommand1.CommandText = "DELETE FROM tblAlumni_pref WHERE (student_id = ?) AND (email_direct_pref = ?) AND" & _
        " (photos_pref = ?) AND (skills_direct_pref = ?) AND (student_name = ? OR ? IS NU" & _
        "LL AND student_name IS NULL)"
        Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_student_id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "student_id", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_email_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "email_direct_pref", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_photos_pref", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "photos_pref", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_skills_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "skills_direct_pref", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_student_name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "student_name", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_student_name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "student_name", System.Data.DataRowVersion.Original, Nothing))
        '
        'OleDbConnection1
        '
        Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\Databases\alumni2.mdb" & _
        """;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provide" & _
        "r=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Exten" & _
        "ded Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:" & _
        "Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OL" & _
        "EDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Databas" & _
        "e=False"
        '
        'OleDbInsertCommand1
        '
        Me.OleDbInsertCommand1.CommandText = "INSERT INTO tblAlumni_pref(email_direct_pref, photos_pref, skills_direct_pref, st" & _
        "udent_name) VALUES (?, ?, ?, ?)"
        Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("email_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, "email_direct_pref"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("photos_pref", System.Data.OleDb.OleDbType.Boolean, 2, "photos_pref"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("skills_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, "skills_direct_pref"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("student_name", System.Data.OleDb.OleDbType.VarWChar, 50, "student_name"))
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT email_direct_pref, photos_pref, skills_direct_pref, student_id, student_na" & _
        "me FROM tblAlumni_pref"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
        '
        'OleDbUpdateCommand1
        '
        Me.OleDbUpdateCommand1.CommandText = "UPDATE tblAlumni_pref SET email_direct_pref = ?, photos_pref = ?, skills_direct_p" & _
        "ref = ?, student_name = ? WHERE (student_id = ?) AND (email_direct_pref = ?) AND" & _
        " (photos_pref = ?) AND (skills_direct_pref = ?) AND (student_name = ? OR ? IS NU" & _
        "LL AND student_name IS NULL)"
        Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("email_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, "email_direct_pref"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("photos_pref", System.Data.OleDb.OleDbType.Boolean, 2, "photos_pref"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("skills_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, "skills_direct_pref"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("student_name", System.Data.OleDb.OleDbType.VarWChar, 50, "student_name"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_student_id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "student_id", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_email_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "email_direct_pref", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_photos_pref", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "photos_pref", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_skills_direct_pref", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "skills_direct_pref", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_student_name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "student_name", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_student_name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "student_name", System.Data.DataRowVersion.Original, Nothing))
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-GB")
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter
    Protected WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
    Protected WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
    Protected WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand
    Protected WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand
    Protected WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection
    Protected WithEvents DataSet11 As Practice2.DataSet1

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DataGrid1.DataMember = "tblAlumni_pref"
        DataGrid1.DataSource = DataSet11
        OleDbDataAdapter1.Fill(DataSet11)
        DataGrid1.DataBind()
    End Sub

    Private Sub OleDbDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles OleDbDataAdapter1.RowUpdated

    End Sub

    Private Sub OleDbConnection1_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.OleDb.OleDbInfoMessageEventArgs) Handles OleDbConnection1.InfoMessage

    End Sub
End Class
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12271431
Oh, this looks a mess--were you using one of those code editors? It looks like you have way too many repetitions and that may be locking up Access on you!

FtB
0
 

Author Comment

by:ladykh
ID: 12271482
A lock file does keep appearing.  I just made the page in visual studio and dragged and dropped one button, an oledbConnection, oledbDataAdapter. DataSet and DataGrid and only added the following code by hand, all the rest appeared automatically.

At the top of the code:
Imports System.Data.OleDb


 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DataGrid1.DataMember = "tblAlumni_pref"
        DataGrid1.DataSource = DataSet11
        OleDbDataAdapter1.Fill(DataSet11)
        DataGrid1.DataBind()
    End Sub
0
 

Author Comment

by:ladykh
ID: 12271496
The error appears on the following line:

OleDbDataAdapter1.Fill(DataSet11)
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 2000 total points
ID: 12271517
Here is a simple page to get you started--it should display all of your records in the datagrid.

<%@ Page Language="VB" %>
<script runat="server">
    Function qryStudents() As System.Data.IDataReader
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Databases\alumni2.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
        Dim queryString As String = "SELECT * FROM tblAlumni_pref "
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection
        dbConnection.Open
        Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        Return dataReader
    End Function

    sub Page_Load(Sender as Object, E as EventArgs)
        grdStudents.DataSource = qryStudents
        grdStudents.DataBind()
   end sub
</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        <asp:DataGrid id="grdStudents" runat="server"></asp:DataGrid>
    </form>
</body>
</html>
0
 

Author Comment

by:ladykh
ID: 12271604
Thanks for your help.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12271645
Glad to have helped.

At least we know now that you can connect to Access, that all of your permissions are in place and etc. The next task will be to figure out why your code generator spits out so many duplicates!

FtB
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 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