none
connecting to a local access database from asp.net page? RRS feed

  • Question

  •  

    Hello,

     

    I am new to the web world.  I am writing a fairly basic web application with a access database.  I am creating a business object that will access the database.  I am having issues connecting the to access database. 

    My end goal is to run a stored procedure that returns my results into a dataset. 

     

    I have this so far.  I wrote it real quick to see if it was going to work.  And it didn't....

     

    Is there a better way to access the database.  I don't like using the full path of the database. 

     

    Code Block

    Dim connection As New Data.OleDb.OleDbConnection

    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\Dealership\App_Data"

     

    Dim sq As New Data.OleDb.OleDbDataAdapter("Select * from MakeTypes", connection)

    Dim dataset As New DataSet

    dataset.Tables.Add("table0")

    sq.FillSchema(dataset.Tables(0), SchemaType.Source)

     

    Return dataset

     

     

     

    When I ran it I got an exception on the FillSchema line:

     

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

     

     

    Any help or guidance would be appreciated.

     

    Thank you,

    Brandon

     

    Saturday, November 10, 2007 12:23 AM

Answers

  • I believe the following will work with your website:

     

    Data Source=|DataDirectory|Dealership.mdb

    Saturday, November 10, 2007 1:36 AM

All replies

  • You aren't specifying the name of the MDB file.  For the connection string to work, the name of the Access file must be specified.  The attempt to connect is not made until the call to FillSchema.

     

    Also, I would recommend storing the connection string in the web.config instead of the source file.

    Saturday, November 10, 2007 1:30 AM
  •  

    Yes, I just realized that.

     

    Is there a better way to reference a mdb file in the appdata folder?

     

    I got this to work:

     

    Code Block

    Dim dataset As New DataSet

    Dim connection As New Data.OleDb.OleDbConnection

    Try

    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\Dealership\App_Data\Dealership.mdb"

    connection.Open()

    Dim sq As New Data.OleDb.OleDbDataAdapter("Select * from MakeTypes", connection)

    sq.Fill(dataset)

    Finally

    connection.Close()

    End Try

    Return dataset

     

     

    Saturday, November 10, 2007 1:33 AM
  • I believe the following will work with your website:

     

    Data Source=|DataDirectory|Dealership.mdb

    Saturday, November 10, 2007 1:36 AM