Solved

Open access database in asp.net.

Posted on 2004-10-08
19
695 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
19 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
Comment Utility
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
Comment Utility
Are you running this on your local computer or on a web server? What is the operating system?

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 15

Expert Comment

by:Thogek
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 46

Expert Comment

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

FtB
0
 

Author Comment

by:ladykh
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
The error appears on the following line:

OleDbDataAdapter1.Fill(DataSet11)
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
Comment Utility
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
Comment Utility
Thanks for your help.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

14 Experts available now in Live!

Get 1:1 Help Now