Open access database in asp.net.

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
ladykhAsked:
Who is Participating?
 
fritz_the_blankConnect With a Mentor Commented:
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
 
fritz_the_blankCommented:
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
 
ladykhAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
fritz_the_blankCommented:
Are you running this on your local computer or on a web server? What is the operating system?

FtB
0
 
fritz_the_blankCommented:
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
 
ladykhAuthor Commented:
Running on IIS Server on with windows xp.  I have disabled simple file sharing and the same error message appears.
0
 
ThogekCommented:
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
 
fritz_the_blankCommented:
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
 
jitgangulyCommented:
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
 
fritz_the_blankCommented:
That's true, but that account is installed automatically with the .Net framework.

FtB
0
 
ladykhAuthor Commented:
Sorry for late reply but have been trying to get it to work but still comes up with same error.
0
 
fritz_the_blankCommented:
Actually, I may be wrong about Jit's comment. Please see:

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

FtB
0
 
ladykhAuthor Commented:

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
 
fritz_the_blankCommented:
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
 
ladykhAuthor Commented:
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
 
ladykhAuthor Commented:
The error appears on the following line:

OleDbDataAdapter1.Fill(DataSet11)
0
 
ladykhAuthor Commented:
Thanks for your help.
0
 
fritz_the_blankCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.