Please help me ASP.NET connect to Database Microsoft Access

.net , asp.net , db Cambodia
  • 12 years ago
    Dear All Member, I have some problem with ASP.NET connet to database Microsoft Access. I want create class for connect to database microsoft access as below: Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.OleDb Imports System.Configuration Imports System.Web.Configuration Imports System.Web.Hosting Namespace DbAll_Database.DAL Public Class Db_Database Private cmd As IDbCommand = New OleDbCommand() Private strConnectionString As String = "" Private handleErrors As Boolean = False Private strLastError As String = "" Public Sub New() strConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:\A1 Angkor Document\Project Customer\ANR Tours\Administrator\App_Data\ASPNetDB.mdb" Dim cnn As New OleDbConnection() cnn.ConnectionString = strConnectionString cmd.Connection = cnn cmd.CommandType = CommandType.StoredProcedure End Sub Public Function ExecuteReader() As IDataReader Dim reader As IDataReader = Nothing Try Me.Open() reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Catch ex As Exception If handleErrors Then strLastError = ex.Message Else Throw End If End Try Return reader End Function Public Function ExecuteReader(ByVal commandtext As String) As IDataReader Dim reader As IDataReader = Nothing Try cmd.CommandText = commandtext reader = Me.ExecuteReader() Catch ex As Exception If (handleErrors) Then strLastError = ex.Message Else Throw End If End Try Return reader End Function Public Function ExecuteScalar() As Object Dim obj As Object = Nothing Try Me.Open() obj = cmd.ExecuteScalar() Me.Close() Catch ex As Exception If handleErrors Then strLastError = ex.Message Else Throw End If End Try Return obj End Function Public Function ExecuteScalar(ByVal commandtext As String) As Object Dim obj As Object = Nothing Try cmd.CommandText = commandtext obj = Me.ExecuteScalar() Catch ex As Exception If (handleErrors) Then strLastError = ex.Message Else Throw End If End Try Return obj End Function Public Function ExecuteNonQuery() As Integer Dim i As Integer = -1 Try Me.Open() i = cmd.ExecuteNonQuery() Me.Close() Catch ex As Exception If handleErrors Then strLastError = ex.Message Else Throw End If End Try Return i End Function Public Function ExecuteNonQuery(ByVal commandtext As String) As Integer Dim i As Integer = -1 Try cmd.CommandText = commandtext i = Me.ExecuteNonQuery() Catch ex As Exception If handleErrors Then strLastError = ex.Message Else Throw End If End Try Return i End Function Public Function ExecuteDataSet() As DataSet Dim da As OleDbDataAdapter = Nothing Dim ds As DataSet = Nothing Try da = New OleDbDataAdapter() da.SelectCommand = CType(cmd, OleDbCommand) ds = New DataSet() da.Fill(ds) Catch ex As Exception If (handleErrors) Then strLastError = ex.Message Else Throw End If End Try Return ds End Function Public Function ExecuteDataSet(ByVal commandtext As String) As DataSet Dim ds As DataSet = Nothing Try cmd.CommandText = commandtext ds = Me.ExecuteDataSet() Catch ex As Exception If handleErrors Then strLastError = ex.Message Else Throw End If End Try Return ds End Function Public Property CommandText() As String Get Return cmd.CommandText End Get Set(ByVal value As String) cmd.CommandText = value cmd.Parameters.Clear() End Set End Property Public ReadOnly Property Parameters() As IDataParameterCollection Get Return cmd.Parameters End Get End Property Public Sub AddParameter(ByVal paramname As String, ByVal paramvalue As Object) Dim param As OleDbParameter = New OleDbParameter(paramname, paramvalue) cmd.Parameters.Add(param) End Sub Public Sub AddParameter(ByVal param As IDataParameter) cmd.Parameters.Add(param) End Sub Public Property ConnectionString() As String Get Return strConnectionString End Get Set(ByVal value As String) strConnectionString = value End Set End Property Private Sub Open() cmd.Connection.Open() End Sub Private Sub Close() cmd.Connection.Close() End Sub Public Property HandleExceptions() As Boolean Get Return handleErrors End Get Set(ByVal value As Boolean) handleErrors = value End Set End Property Public ReadOnly Property LastError() As String Get Return strLastError End Get End Property Public Sub Dispose() cmd.Dispose() End Sub End Class End Namespace This class is working well for 3 to 5 day. After this day it will display message like this Server Error in '/' Application. Unspecified error 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: Unspecified error Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [OleDbException (0x80004005): Unspecified error] System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233 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 DbAll_Database.DAL.Db_Database.ExecuteReader() +112 DbAll_Database.DAL.Db_Database.ExecuteReader(String commandtext) +93 HotelGuestHouse.BOL.Welcompage.GetWelcome(Int32 id) +119 _Default.Page_Load(Object sender, EventArgs e) +74 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436 Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 Do you have any solution, please help me? Thank you,
  • 12 years ago

    This looks as though it might be a connection issue. After you get this error, do your pages always consistently return similar errors or are they intermittent?

     

     

  • 12 years ago
    Can you help me for edit issue? Because I'm try find issue, but i can't find. Thank you, kimsea
  • 12 years ago

    Hi,

    Can I ask if this error only occurs on your web server and not your IDE server?

    Can you verify the connection string is correct for your database on the servers?

    PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:\A1 Angkor Document\Project Customer\ANR Tours\Administrator\App_Data\ASPNetDB.mdb

    It doesnt look like a good connection string to me is all, i would choose something a bit simpler and avoid the spaces to make sure that it doesnt confuse the command factory.

    Regards

    Simon C

     

  • 12 years ago

    Simon,

    I agree that the connection string looks a bit suspect with the spaces but if it was incorrect, it's a bit strange that it works for 3-5 days before it fails. If the connection string was incorrect, I would expect the database connection to not work at all.  

    Without any formatting on that block of code, it's difficult to comprehend so well done on spotting that!

    Tim 

  • 12 years ago

    Hi,

    Lol, I spotted the weird connection string, but actually missed his error.

    Have just formatted out the class, and I think the problem is the datareaders.  When you close the connection to the database, the datareaders keep their connection going.

    I have seen this problem occur in my own applications, whereby oracledatareaders held a connection to my database, and eventually filled up the maximum allowed connections, halting anymore connections to the database and bringing down my site.

    My advice, in the close sub and the dispose sub, make sure you close the datareaders.

    Let us know if that works.

    Regards

    Simon C

  • 12 years ago

    Can you clarify what the edit issue is? Is it the same problem with the connection failing after 3-5 days? 

  • 12 years ago
    Sorry that I post before it is difficult to debug. But Class below is correct format:

        1 Imports Microsoft.VisualBasic

        2 Imports System.Data

        3 Imports System.Data.OleDb

        4 Imports System.Configuration

        5 Imports System.Web.Configuration

        6 Imports System.Web.Hosting

        7 

        8 Namespace DbAll_Database.DAL

        9     Public Class Db_Database

       10         Private cmd As IDbCommand = New OleDbCommand()

       11         Private strConnectionString As String = ""

       12         Private handleErrors As Boolean = False

       13         Private strLastError As String = ""

       14 

       15         Public Sub New()

       16            

      17             strConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ASPNetDB.mdb"

       18             Dim cnn As New OleDbConnection()

       19             cnn.ConnectionString = strConnectionString

       20             cmd.Connection = cnn

       21             cmd.CommandType = CommandType.StoredProcedure

       22         End Sub

       23 

       24         Public Function ExecuteReader() As IDataReader

       25             Dim reader As IDataReader = Nothing

       26             Try

       27                 Me.Open()

       28                 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

       29             Catch ex As Exception

       30                 If handleErrors Then

       31                     strLastError = ex.Message

       32                 Else

       33                     Throw

       34                 End If

       35             End Try

       36             Return reader

       37         End Function

       38 

       39         Public Function ExecuteReader(ByVal commandtext As String) As IDataReader

       40             Dim reader As IDataReader = Nothing

       41             Try

       42                 cmd.CommandText = commandtext

       43                 reader = Me.ExecuteReader()

       44             Catch ex As Exception

       45                 If (handleErrors) Then

       46                     strLastError = ex.Message

       47                 Else

       48                     Throw

       49                 End If

       50             End Try

       51             Return reader

       52         End Function

       53 

       54         Public Function ExecuteScalar() As Object

       55             Dim obj As Object = Nothing

       56             Try

       57                 Me.Open()

       58                 obj = cmd.ExecuteScalar()

       59                 Me.Close()

       60             Catch ex As Exception

       61                 If handleErrors Then

       62                     strLastError = ex.Message

       63                 Else

       64                     Throw

       65                 End If

       66             End Try

       67             Return obj

       68         End Function

       69         Public Function ExecuteScalar(ByVal commandtext As String) As Object

       70             Dim obj As Object = Nothing

       71             Try

       72                 cmd.CommandText = commandtext

       73                 obj = Me.ExecuteScalar()

       74             Catch ex As Exception

       75                 If (handleErrors) Then

       76                     strLastError = ex.Message

       77                 Else

       78                     Throw

       79                 End If

       80             End Try

       81             Return obj

       82         End Function

       83 

       84         Public Function ExecuteNonQuery() As Integer

       85             Dim i As Integer = -1

       86             Try

       87                 Me.Open()

       88                 i = cmd.ExecuteNonQuery()

       89                 Me.Close()

       90             Catch ex As Exception

       91                 If handleErrors Then

       92                     strLastError = ex.Message

       93                 Else

       94                     Throw

       95                 End If

       96             End Try

       97             Return i

       98         End Function

       99 

      100 

      101 

      102         Public Function ExecuteNonQuery(ByVal commandtext As String) As Integer

      103             Dim i As Integer = -1

      104             Try

      105                 cmd.CommandText = commandtext

      106                 i = Me.ExecuteNonQuery()

      107             Catch ex As Exception

      108                 If handleErrors Then

      109                     strLastError = ex.Message

      110                 Else

      111                     Throw

      112                 End If

      113             End Try

      114             Return i

      115         End Function

      116         Public Function ExecuteDataSet() As DataSet

      117             Dim da As OleDbDataAdapter = Nothing

      118             Dim ds As DataSet = Nothing

      119             Try

      120                 da = New OleDbDataAdapter()

      121                 da.SelectCommand = CType(cmd, OleDbCommand)

      122                 ds = New DataSet()

      123                 da.Fill(ds)

      124             Catch ex As Exception

      125                 If (handleErrors) Then

      126                     strLastError = ex.Message

      127                 Else

      128                     Throw

      129                 End If

      130             End Try

      131             Return ds

      132         End Function

      133         Public Function ExecuteDataSet(ByVal commandtext As String) As DataSet

      134             Dim ds As DataSet = Nothing

      135             Try

      136                 cmd.CommandText = commandtext

      137                 ds = Me.ExecuteDataSet()

      138             Catch ex As Exception

      139                 If handleErrors Then

      140                     strLastError = ex.Message

      141                 Else

      142                     Throw

      143                 End If

      144             End Try

      145             Return ds

      146         End Function

      147 

      148         Public Property CommandText() As String

      149             Get

      150                 Return cmd.CommandText

      151             End Get

      152             Set(ByVal value As String)

      153                 cmd.CommandText = value

      154                 cmd.Parameters.Clear()

      155             End Set

      156         End Property

      157 

      158         Public ReadOnly Property Parameters() As IDataParameterCollection

      159             Get

      160                 Return cmd.Parameters

      161             End Get

      162         End Property

      163 

      164         Public Sub AddParameter(ByVal paramname As String, ByVal paramvalue As Object)

      165             Dim param As OleDbParameter = New OleDbParameter(paramname, paramvalue)

      166             cmd.Parameters.Add(param)

      167         End Sub

      168 

      169         Public Sub AddParameter(ByVal param As IDataParameter)

      170             cmd.Parameters.Add(param)

      171         End Sub

      172 

      173         Public Property ConnectionString() As String

      174             Get

      175                 Return strConnectionString

      176             End Get

      177             Set(ByVal value As String)

      178                 strConnectionString = value

      179             End Set

      180         End Property

      181 

      182         Private Sub Open()

      183             cmd.Connection.Open()

      184         End Sub

      185 

      186         Private Sub Close()

      187             cmd.Connection.Close()

      188         End Sub

      189 

      190         Public Property HandleExceptions() As Boolean

      191             Get

      192                 Return handleErrors

      193             End Get

      194             Set(ByVal value As Boolean)

      195                 handleErrors = value

      196             End Set

      197         End Property

      198 

      199         Public ReadOnly Property LastError() As String

      200             Get

      201                 Return strLastError

      202             End Get

      203         End Property

      204 

      205         Public Sub Dispose()

      206             cmd.Dispose()

      207         End Sub

      208     End Class

      209 End Namespace

      210 


    Please help me.
    Thank you,

    Kimsea
  • 12 years ago

    Hi,

    Can you confirm that where ever you call executereader, that the reader that is returned has close called on it before you dispose of it?

    Regards

    Simon C

  • 12 years ago

    This class is working well for 3 to 5 day. After this day it will display message like this

     

    Error in '/' Application.

    Unspecified error

    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: Unspecified error

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [OleDbException (0x80004005): Unspecified error]
       System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233
       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
       DbAll_Database.DAL.Db_Database.ExecuteReader() +112
       DbAll_Database.DAL.Db_Database.ExecuteReader(String commandtext) +93
       HotelGuestHouse.BOL.Welcompage.GetWelcome(Int32 id) +119
       _Default.Page_Load(Object sender, EventArgs e) +74
       System.Web.UI.Control.OnLoad(EventArgs e) +99
       System.Web.UI.Control.LoadRecursive() +47
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
    


    Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

    For Class that I use this connection as below:

        1 Imports Microsoft.VisualBasic

        2 Imports DbAll_Database.DAL

        3 Imports System

        4 Imports System.Data

        5 Imports System.Data.OleDb

        6 

        7 Namespace Example.BOL

        8     Public Class Db_Room

        9         Private intID As Integer

       10         Private strTitle As String

       11         Private strTitleUrl As String

       12         Private strFileImage As String

       13         Private strDescription As String

       14         Private strFloor As String

       15 

       16         Public Property ID() As Integer

       17             Get

       18                 Return intID

       19             End Get

       20             Set(ByVal value As Integer)

       21                 intID = value

       22             End Set

       23         End Property

       24 

       25         Public Property Title() As String

       26             Get

       27                 Return strTitle

       28             End Get

       29             Set(ByVal value As String)

       30                 strTitle = value

       31             End Set

       32         End Property

       33 

       34         Public Property TitleUrl() As String

       35             Get

       36                 Return strTitleUrl

       37             End Get

       38             Set(ByVal value As String)

       39                 strTitleUrl = value

       40             End Set

       41         End Property

       42 

       43         Public Property FileImage() As String

       44             Get

       45                 Return strFileImage

       46             End Get

       47             Set(ByVal value As String)

       48                 strFileImage = value

       49             End Set

       50         End Property

       51 

       52         Public Property Description() As String

       53             Get

       54                 Return strDescription

       55             End Get

       56             Set(ByVal value As String)

       57                 strDescription = value

       58             End Set

       59         End Property

       60 

       61         Public Property Floor() As String

       62             Get

       63                 Return strFloor

       64             End Get

       65             Set(ByVal value As String)

       66                 strFloor = value

       67             End Set

       68         End Property

       69 

       70         Public Shared Function Insert(ByVal p As Db_Room) As Integer

       71             Dim db As Db_Database = New Db_Database

       72             db.Parameters.Add(New OleDbParameter("@sTitle", p.Title))

       73             db.Parameters.Add(New OleDbParameter("@sTitleUrl", p.TitleUrl))

       74             db.Parameters.Add(New OleDbParameter("@sFileImage", p.FileImage))

       75             db.Parameters.Add(New OleDbParameter("@sDescription", p.Description))

       76             db.Parameters.Add(New OleDbParameter("@sFloor", p.Floor))

       77             Dim retval As Integer = db.ExecuteNonQuery("Db_Room_Insert")

       78             Return retval

       79         End Function

       80 

       81         Public Shared Function Update(ByVal p As Db_Room) As Integer

       82             Dim db As Db_Database = New Db_Database

       83             db.Parameters.Add(New OleDbParameter("@iID", p.ID))

       84             db.Parameters.Add(New OleDbParameter("@sTitle", p.Title))

       85             db.Parameters.Add(New OleDbParameter("@sTitleUrl", p.TitleUrl))

       86             db.Parameters.Add(New OleDbParameter("@sDescription", p.Description))

       87             Dim retval As Integer = db.ExecuteNonQuery("Db_Room_Update")

       88             Return retval

       89         End Function

       90 

       91         Public Shared Function UpdateImage(ByVal p As Db_Room) As Integer

       92             Dim db As Db_Database = New Db_Database

       93             db.Parameters.Add(New OleDbParameter("@iID", p.ID))

       94             db.Parameters.Add(New OleDbParameter("@sFileImage", p.FileImage))

       95             Dim retval As Integer = db.ExecuteNonQuery("Db_Room_UpdateImage")

       96             Return retval

       97         End Function

       98 

       99         Public Shared Function UpdateFloor(ByVal p As Db_Room) As Integer

      100             Dim db As Db_Database = New Db_Database

      101             db.Parameters.Add(New OleDbParameter("@iID", p.ID))

      102             db.Parameters.Add(New OleDbParameter("@sFloor", p.Floor))

      103             Dim retval As Integer = db.ExecuteNonQuery("Db_Room_UpdateFloor")

      104             Return retval

      105         End Function

      106 

      107         Public Shared Function Delete(ByVal p As Db_Room) As Integer

      108             Dim db As Db_Database = New Db_Database

      109             db.Parameters.Add(New OleDbParameter("@iID", p.ID))

      110             Dim retval As Integer = db.ExecuteNonQuery("Db_Room_Delete")

      111             Return retval

      112         End Function

      113 

      114         Public Shared Function GetRoom(ByVal pID As Integer) As Db_Room

      115             Dim db As Db_Database = New Db_Database

      116             db.Parameters.Add(New OleDbParameter("@iID", pID))

      117             Dim dr As OleDbDataReader = CType(db.ExecuteReader("Db_Room_SelectOne"), OleDbDataReader)

      118             If dr.HasRows Then

      119                 Dim obj As Db_Room = New Db_Room

      120                 While dr.Read

      121                     obj.ID = dr.GetInt32(dr.GetOrdinal("ID"))

      122                     obj.Title = dr.GetString(dr.GetOrdinal("Title"))

      123                     obj.TitleUrl = dr.GetString(dr.GetOrdinal("TitleUrl"))

      124                     obj.FileImage = dr.GetString(dr.GetOrdinal("FileImage"))

      125                     obj.Description = dr.GetString(dr.GetOrdinal("Description"))

      126                     obj.Floor = dr.GetString(dr.GetOrdinal("Floor"))

      127                 End While

      128                 dr.Close()

      129                 Return obj

      130             Else

      131                 dr.Close()

      132                 Return New Db_Room

      133             End If

      134         End Function

      135 

      136         Public Shared Function GetRoom(ByVal pTitleUrl As String) As Db_Room

      137             Dim db As Db_Database = New Db_Database

      138             db.Parameters.Add(New OleDbParameter("@sTitleUrl", pTitleUrl))

      139             Dim dr As OleDbDataReader = CType(db.ExecuteReader("Db_Room_SelectUrl"), OleDbDataReader)

      140             If dr.HasRows Then

      141                 Dim obj As Db_Room = New Db_Room

      142                 While dr.Read

      143                     obj.ID = dr.GetInt32(dr.GetOrdinal("ID"))

      144                     obj.Title = dr.GetString(dr.GetOrdinal("Title"))

      145                     obj.TitleUrl = dr.GetString(dr.GetOrdinal("TitleUrl"))

      146                     obj.FileImage = dr.GetString(dr.GetOrdinal("FileImage"))

      147                     obj.Description = dr.GetString(dr.GetOrdinal("Description"))

      148                     obj.Floor = dr.GetString(dr.GetOrdinal("Floor"))

      149                 End While

      150                 dr.Close()

      151                 Return obj

      152             Else

      153                 dr.Close()

      154                 Return New Db_Room

      155             End If

      156         End Function

      157 

      158         Public Shared Function GetRoomAll() As DataSet

      159             Dim db As Db_Database = New Db_Database

      160             Return db.ExecuteDataSet("Db_Room_SelectAll")

      161         End Function

      162 

      163     End Class

      164 End Namespace

    If you need more please confirm me. I will wait good news from you,

    Thank you,

    Kimsea

  • 12 years ago
    Hi, Now I have post clear format class.
    Please help me.

    Thank you,
  • 12 years ago

    Hi,

    I have been chewing this one over for a few days now and i can't see anything wrong with the code you are using, so it is pointing me to the configuration/implementation of the site.

    When it fails, is it the same operation that is being carried out that causes it?  What I am thinking is whether all the read statements are working, but any update statements are failing etc, so if it can be pinpointed to one sort of area.

    Are you using any user impersonation on the website?  If so, does the user have all the permissions necessary to use the database, e.g. the folder for the database lock file.

    How long is the app_pool recycle set to on IIS, does it co-incide with the error happening?

    Any information you can give on these would be useful for us to help you.

    Si

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only two kinds of languages: the ones people complain about and the ones nobody uses” - Bjarne Stroustrup