Database access failed after few entries

[Rico]

I have tried to access a database using asp.net. after some entries like
120+, I got an unspecified error message. Anyone know what is happening, it
seems to be stuck at the same line even when i added a new line.

Here is the error message:

Server Error in '/sonamy' 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.Ole Db.OleDbExcepti on: Unspecified error

Source Error:
Line 43:
Line 44: Dim tb1 As DataSet = New DataSet
Line 45: da.Fill(tb1)
Line 46: Dim dt1 As DataTable = tb1.Tables(0)
Line 47:
Source File: C:\Inetpub\wwwr oot\sonamy\atte ndance.aspx.vb Line: 45

Stack Trace:
[OleDbException (0x80004005): Unspecified error]
System.Data.Ole Db.OleDbConnect ion.ProcessResu lts(Int32 hr) +20
System.Data.Ole Db.OleDbConnect ion.InitializeP rovider() +57
System.Data.Ole Db.OleDbConnect ion.Open() +203
System.Data.Com mon.DbDataAdapt er.QuietOpen(ID bConnection connection,
ConnectionState & originalState) +44
System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet) +38
Sonamy.Form.Pag e_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwr oot\sonamy\atte ndance.aspx.vb: 45
System.Web.UI.C ontrol.OnLoad(E ventArgs e) +67
System.Web.UI.C ontrol.LoadRecu rsive() +35
System.Web.UI.P age.ProcessRequ estMain() +731


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.432 2.573; ASP.NET
Version:1.1.432 2.573

Here is the Vb code:

Public Class Form
Inherits System.Web.UI.P age

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub
InitializeCompo nent()
Me.ID = "Form"

End Sub
Protected WithEvents lblNotice As System.Web.UI.W ebControls.Labe l
Protected WithEvents lblName As System.Web.UI.W ebControls.Labe l
Protected WithEvents lblBarCode As System.Web.UI.W ebControls.Labe l
Protected WithEvents RequiredFieldVa lidator1 As
System.Web.UI.W ebControls.Requ iredFieldValida tor
Protected WithEvents lblTotal As System.Web.UI.W ebControls.Labe l
Protected WithEvents btnSubmit As System.Web.UI.W ebControls.Butt on
Protected WithEvents txtBarcode2 As System.Web.UI.W ebControls.Text Box
Protected WithEvents txtBarcode As
System.Web.UI.H tmlControls.Htm lInputText
Protected WithEvents test As System.Web.UI.H tmlControls.Htm lInputHidden

'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceho lderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeCompo nent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
'Put user code to initialize the page here
If Session("EventT itle") = "" Then
Response.Redire ct("admin.aspx" )
End If
System.Diagnost ics.Debug.Write Line("Posted hidden field = " &
Request("test") )
If Len(Request("te st")) <> 0 Then
'We know this is auto postback
Dim barcode As String = Request("test")
Dim da As IDbDataAdapter = GetRecord(barco de)

Dim tb1 As DataSet = New DataSet
da.Fill(tb1)
Dim dt1 As DataTable = tb1.Tables(0)

lblBarCode.Text = barcode

If dt1.Rows.Count = 0 Then
lblNotice.Text = "Person not found!"
Else
If dt1.Rows(0).Ite m("Name") Is System.DBNull.V alue Then
lblName.Text = "(anonymous )" Else lblName.Text = dt1.Rows(0).Ite m("Name")
If dt1.Rows(0).Ite m("Attendance ") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendanc e(dt1.Rows(0).I tem("Barcode"))
lblNotice.Text = "Attendance Updated!"
da.Update(tbl)
End If
End If

Me.txtBarcode.V alue = ""
Me.test.Value = ""
End If
SetFocus(txtBar code, "form1")
Me.lblTotal.Tex t = GetCount()
End Sub

Private Sub btnSubmit_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnSubmit.Click
Dim da As IDbDataAdapter = GetRecord(txtBa rcode.Value)

Dim tb1 As DataSet = New DataSet
da.Fill(tb1)
Dim dt1 As DataTable = tb1.Tables(0)

lblBarCode.Text = txtBarcode.Valu e

If dt1.Rows.Count = 0 Then
lblNotice.Text = "Person not found!"
Else
If dt1.Rows(0).Ite m("Name") Is System.DBNull.V alue Then
lblName.Text = "(anonymous )" Else lblName.Text = dt1.Rows(0).Ite m("Name")
If dt1.Rows(0).Ite m("Attendance ") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendanc e(dt1.Rows(0).I tem("Barcode"))
lblNotice.Text = "Attendance Updated!"
End If
End If

Me.txtBarcode.V alue = ""
SetFocus(txtBar code, "form1")

Me.lblTotal.Tex t = GetCount()

End Sub


Function GetRecord(ByVal barcode As Integer) As System.Data.IDb DataAdapter
'Dim connectionStrin g As String = "Provider=Micro soft.Jet.OLEDB. 4.0;
Ole DB Services=-4; Data Source=C:\Inetp ub\wwwroot\Luck Draw.mdb"
Dim dbConnection As System.Data.IDb Connection = New
System.Data.Ole Db.OleDbConnect ion(Configurati onSettings.AppS ettings("strCon n"))

Dim queryString As String = "SELECT * FROM " & Session("TableN ame")
& " WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDb Command = New
System.Data.Ole Db.OleDbCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection

Dim dbParam_barcode As System.Data.IDa taParameter = New
System.Data.Ole Db.OleDbParamet er
dbParam_barcode .ParameterName = "@Barcode"
dbParam_barcode .Value = barcode
dbParam_barcode .DbType = System.Data.DbT ype.Int32
dbCommand.Param eters.Add(dbPar am_barcode)

Dim dataAdapter As System.Data.IDb DataAdapter = New
System.Data.Ole Db.OleDbDataAda pter
dataAdapter.Sel ectCommand = dbCommand

Return dataAdapter
dbConnection.Cl ose()
End Function

Function UpdateAttendanc e(ByVal barcode As Integer) As Integer
'Dim connectionStrin g As String = "Provider=Micro soft.Jet.OLEDB. 4.0;
Ole DB Services=-4; Data Source=C:\Inetp ub\wwwroot\Luck Draw.mdb"
Dim dbConnection As System.Data.IDb Connection = New
System.Data.Ole Db.OleDbConnect ion(Configurati onSettings.AppS ettings("strCon n"))

Dim queryString As String = "UPDATE " & Session("TableN ame") & " SET
[Attendance]=true WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDb Command = New
System.Data.Ole Db.OleDbCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection

Dim dbParam_barcode As System.Data.IDa taParameter = New
System.Data.Ole Db.OleDbParamet er
dbParam_barcode .ParameterName = "@Barcode"
dbParam_barcode .Value = barcode
dbParam_barcode .DbType = System.Data.DbT ype.Int32
dbCommand.Param eters.Add(dbPar am_barcode)

Dim rowsAffected As Integer = 0
dbConnection.Op en()
Try
rowsAffected = dbCommand.Execu teNonQuery
Finally
dbConnection.Cl ose()
End Try

Return rowsAffected
End Function

Function GetCount() As Integer

Dim dbConnection As System.Data.IDb Connection = New
System.Data.Ole Db.OleDbConnect ion(Configurati onSettings.AppS ettings("strCon n"))
Dim queryString As String = "SELECT count(*) FROM " &
Session("TableN ame") & " WHERE Attendance = -1"
Dim dbCommand As System.Data.IDb Command = New
System.Data.Ole Db.OleDbCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim dataAdapter As System.Data.IDb DataAdapter = New
System.Data.Ole Db.OleDbDataAda pter
dataAdapter.Sel ectCommand = dbCommand

Dim tb1 As DataSet = New DataSet
dataAdapter.Fil l(tb1)
Dim dt1 As DataTable = tb1.Tables(0)

Return dt1.Rows(0).Ite m(0)
dbConnection.Cl ose()
End Function
End Class
Thank you for your help.

cheers,
Rico

[Cowboy \(Gregory A. Beamer\)]

First, move to a Try ... Finally model. Make sure you put the
Connection.Disp ose() in the finally (this will also close the object, so
..Close() is not necessary). Dispose() guarantees the objects move back into
the pool, eliminating one possible reason for your issue.

Next, do a check and make sure you have data before accessing Table(0).
Unless you are using a strongly typed dataset, you cannot guarantee the
table will always be there.

Finally, add some code that throws the error to a log when it happens. You
want to know any input variables to the function, so you can try the query
on the database and observe what is happening beneath the hood. This will
aid you in capturing other issues and better help you troubleshoot the
problem. The first two suggestions may get rid of it, however.

--
Gregory A. Beamer

*************** *************** *************** ****
Think Outside the Box!
*************** *************** *************** ****
"Rico" <Ri**@discussio ns.microsoft.co m> wrote in message
news:1C******** *************** ***********@mic rosoft.com...

I have tried to access a database using asp.net. after some entries like
120+, I got an unspecified error message. Anyone know what is happening,
it
seems to be stuck at the same line even when i added a new line.

Here is the error message:

Server Error in '/sonamy' 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.Ole Db.OleDbExcepti on: Unspecified error

Source Error:
Line 43:
Line 44: Dim tb1 As DataSet = New DataSet
Line 45: da.Fill(tb1)
Line 46: Dim dt1 As DataTable = tb1.Tables(0)
Line 47:
Source File: C:\Inetpub\wwwr oot\sonamy\atte ndance.aspx.vb Line: 45

Stack Trace:
[OleDbException (0x80004005): Unspecified error]
System.Data.Ole Db.OleDbConnect ion.ProcessResu lts(Int32 hr) +20
System.Data.Ole Db.OleDbConnect ion.InitializeP rovider() +57
System.Data.Ole Db.OleDbConnect ion.Open() +203
System.Data.Com mon.DbDataAdapt er.QuietOpen(ID bConnection connection,
ConnectionState & originalState) +44
System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32
startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet) +38
Sonamy.Form.Pag e_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwr oot\sonamy\atte ndance.aspx.vb: 45
System.Web.UI.C ontrol.OnLoad(E ventArgs e) +67
System.Web.UI.C ontrol.LoadRecu rsive() +35
System.Web.UI.P age.ProcessRequ estMain() +731


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.432 2.573;
ASP.NET
Version:1.1.432 2.573

Here is the Vb code:

Public Class Form
Inherits System.Web.UI.P age

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub
InitializeCompo nent()
Me.ID = "Form"

End Sub
Protected WithEvents lblNotice As System.Web.UI.W ebControls.Labe l
Protected WithEvents lblName As System.Web.UI.W ebControls.Labe l
Protected WithEvents lblBarCode As System.Web.UI.W ebControls.Labe l
Protected WithEvents RequiredFieldVa lidator1 As
System.Web.UI.W ebControls.Requ iredFieldValida tor
Protected WithEvents lblTotal As System.Web.UI.W ebControls.Labe l
Protected WithEvents btnSubmit As System.Web.UI.W ebControls.Butt on
Protected WithEvents txtBarcode2 As System.Web.UI.W ebControls.Text Box
Protected WithEvents txtBarcode As
System.Web.UI.H tmlControls.Htm lInputText
Protected WithEvents test As System.Web.UI.H tmlControls.Htm lInputHidden

'NOTE: The following placeholder declaration is required by the Web
Form
Designer.
'Do not delete or move it.
Private designerPlaceho lderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeCompo nent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
'Put user code to initialize the page here
If Session("EventT itle") = "" Then
Response.Redire ct("admin.aspx" )
End If
System.Diagnost ics.Debug.Write Line("Posted hidden field = " &
Request("test") )
If Len(Request("te st")) <> 0 Then
'We know this is auto postback
Dim barcode As String = Request("test")
Dim da As IDbDataAdapter = GetRecord(barco de)

Dim tb1 As DataSet = New DataSet
da.Fill(tb1)
Dim dt1 As DataTable = tb1.Tables(0)

lblBarCode.Text = barcode

If dt1.Rows.Count = 0 Then
lblNotice.Text = "Person not found!"
Else
If dt1.Rows(0).Ite m("Name") Is System.DBNull.V alue Then
lblName.Text = "(anonymous )" Else lblName.Text = dt1.Rows(0).Ite m("Name")
If dt1.Rows(0).Ite m("Attendance ") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendanc e(dt1.Rows(0).I tem("Barcode"))
lblNotice.Text = "Attendance Updated!"
da.Update(tbl)
End If
End If

Me.txtBarcode.V alue = ""
Me.test.Value = ""
End If
SetFocus(txtBar code, "form1")
Me.lblTotal.Tex t = GetCount()
End Sub

Private Sub btnSubmit_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnSubmit.Click
Dim da As IDbDataAdapter = GetRecord(txtBa rcode.Value)

Dim tb1 As DataSet = New DataSet
da.Fill(tb1)
Dim dt1 As DataTable = tb1.Tables(0)

lblBarCode.Text = txtBarcode.Valu e

If dt1.Rows.Count = 0 Then
lblNotice.Text = "Person not found!"
Else
If dt1.Rows(0).Ite m("Name") Is System.DBNull.V alue Then
lblName.Text = "(anonymous )" Else lblName.Text = dt1.Rows(0).Ite m("Name")
If dt1.Rows(0).Ite m("Attendance ") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendanc e(dt1.Rows(0).I tem("Barcode"))
lblNotice.Text = "Attendance Updated!"
End If
End If

Me.txtBarcode.V alue = ""
SetFocus(txtBar code, "form1")

Me.lblTotal.Tex t = GetCount()

End Sub


Function GetRecord(ByVal barcode As Integer) As
System.Data.IDb DataAdapter
'Dim connectionStrin g As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;
Ole DB Services=-4; Data Source=C:\Inetp ub\wwwroot\Luck Draw.mdb"
Dim dbConnection As System.Data.IDb Connection = New
System.Data.Ole Db.OleDbConnect ion(Configurati onSettings.AppS ettings("strCon n"))

Dim queryString As String = "SELECT * FROM " & Session("TableN ame")
& " WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDb Command = New
System.Data.Ole Db.OleDbCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection

Dim dbParam_barcode As System.Data.IDa taParameter = New
System.Data.Ole Db.OleDbParamet er
dbParam_barcode .ParameterName = "@Barcode"
dbParam_barcode .Value = barcode
dbParam_barcode .DbType = System.Data.DbT ype.Int32
dbCommand.Param eters.Add(dbPar am_barcode)

Dim dataAdapter As System.Data.IDb DataAdapter = New
System.Data.Ole Db.OleDbDataAda pter
dataAdapter.Sel ectCommand = dbCommand

Return dataAdapter
dbConnection.Cl ose()
End Function

Function UpdateAttendanc e(ByVal barcode As Integer) As Integer
'Dim connectionStrin g As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;
Ole DB Services=-4; Data Source=C:\Inetp ub\wwwroot\Luck Draw.mdb"
Dim dbConnection As System.Data.IDb Connection = New
System.Data.Ole Db.OleDbConnect ion(Configurati onSettings.AppS ettings("strCon n"))

Dim queryString As String = "UPDATE " & Session("TableN ame") & "
SET
[Attendance]=true WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDb Command = New
System.Data.Ole Db.OleDbCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection

Dim dbParam_barcode As System.Data.IDa taParameter = New
System.Data.Ole Db.OleDbParamet er
dbParam_barcode .ParameterName = "@Barcode"
dbParam_barcode .Value = barcode
dbParam_barcode .DbType = System.Data.DbT ype.Int32
dbCommand.Param eters.Add(dbPar am_barcode)

Dim rowsAffected As Integer = 0
dbConnection.Op en()
Try
rowsAffected = dbCommand.Execu teNonQuery
Finally
dbConnection.Cl ose()
End Try

Return rowsAffected
End Function

Function GetCount() As Integer

Dim dbConnection As System.Data.IDb Connection = New
System.Data.Ole Db.OleDbConnect ion(Configurati onSettings.AppS ettings("strCon n"))
Dim queryString As String = "SELECT count(*) FROM " &
Session("TableN ame") & " WHERE Attendance = -1"
Dim dbCommand As System.Data.IDb Command = New
System.Data.Ole Db.OleDbCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim dataAdapter As System.Data.IDb DataAdapter = New
System.Data.Ole Db.OleDbDataAda pter
dataAdapter.Sel ectCommand = dbCommand

Dim tb1 As DataSet = New DataSet
dataAdapter.Fil l(tb1)
Dim dt1 As DataTable = tb1.Tables(0)

Return dt1.Rows(0).Ite m(0)
dbConnection.Cl ose()
End Function
End Class
Thank you for your help.

cheers,
Rico