Please help me ASP.NET connect to Database Microsoft Access
-
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,
-
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?
-
Can you help me for edit issue? Because I'm try find issue, but i can't find. Thank you, kimsea
-
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
-
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
-
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
-
Can you clarify what the edit issue is? Is it the same problem with the connection failing after 3-5 days?
-
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 -
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
-
This class is working well for 3 to 5 day. After this day it will display message like this
Error in '/' Application.
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.Unspecified error
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
-
Hi, Now I have post clear format class.
Please help me.
Thank you, -
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
.NET forum discussion
-
edmonton female escort services near me
by canadapleasure (0 replies)
-
Bagaimana memenangkan $ 1,54 miliar dalam Mega Jutaan
by gametogelan (0 replies)
-
input integer from text file and output text file
by shmilon (0 replies)
-
cSharp stuck at exercise
by xander_Michiels (0 replies)
-
Need help in selected the Tax Audit Year from drop down menu and displaying results for the selected year
by citymumbai (0 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).