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.OleDb.OleDbException: 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\wwwroot\sonamy\attendance.aspx.vb Line: 45

Stack Trace:
[OleDbException (0x80004005): Unspecified error]
System.Data.OleDb.OleDbConnection.ProcessResults(I nt32 hr) +20
System.Data.OleDb.OleDbConnection.InitializeProvid er() +57
System.Data.OleDb.OleDbConnection.Open() +203
System.Data.Common.DbDataAdapter.QuietOpen(IDbConn ection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Sonamy.Form.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\sonamy\attendance.aspx.vb:45
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573

Here is the Vb code:

Public Class Form
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.ID = "Form"

End Sub
Protected WithEvents lblNotice As System.Web.UI.WebControls.Label
Protected WithEvents lblName As System.Web.UI.WebControls.Label
Protected WithEvents lblBarCode As System.Web.UI.WebControls.Label
Protected WithEvents RequiredFieldValidator1 As
System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents lblTotal As System.Web.UI.WebControls.Label
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents txtBarcode2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtBarcode As
System.Web.UI.HtmlControls.HtmlInputText
Protected WithEvents test As System.Web.UI.HtmlControls.HtmlInputHidden

'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
If Session("EventTitle") = "" Then
Response.Redirect("admin.aspx")
End If
System.Diagnostics.Debug.WriteLine("Posted hidden field = " &
Request("test"))
If Len(Request("test")) <> 0 Then
'We know this is auto postback
Dim barcode As String = Request("test")
Dim da As IDbDataAdapter = GetRecord(barcode)

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).Item("Name") Is System.DBNull.Value Then
lblName.Text = "(anonymous)" Else lblName.Text = dt1.Rows(0).Item("Name")
If dt1.Rows(0).Item("Attendance") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendance(dt1.Rows(0).Item("Barcode"))
lblNotice.Text = "Attendance Updated!"
da.Update(tbl)
End If
End If

Me.txtBarcode.Value = ""
Me.test.Value = ""
End If
SetFocus(txtBarcode, "form1")
Me.lblTotal.Text = GetCount()
End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click
Dim da As IDbDataAdapter = GetRecord(txtBarcode.Value)

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

lblBarCode.Text = txtBarcode.Value

If dt1.Rows.Count = 0 Then
lblNotice.Text = "Person not found!"
Else
If dt1.Rows(0).Item("Name") Is System.DBNull.Value Then
lblName.Text = "(anonymous)" Else lblName.Text = dt1.Rows(0).Item("Name")
If dt1.Rows(0).Item("Attendance") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendance(dt1.Rows(0).Item("Barcode"))
lblNotice.Text = "Attendance Updated!"
End If
End If

Me.txtBarcode.Value = ""
SetFocus(txtBarcode, "form1")

Me.lblTotal.Text = GetCount()

End Sub


Function GetRecord(ByVal barcode As Integer) As System.Data.IDbDataAdapter
'Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\LuckDraw.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(ConfigurationSet tings.AppSettings("strConn"))

Dim queryString As String = "SELECT * FROM " & Session("TableName")
& " WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_barcode As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_barcode.ParameterName = "@Barcode"
dbParam_barcode.Value = barcode
dbParam_barcode.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_barcode)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand

Return dataAdapter
dbConnection.Close()
End Function

Function UpdateAttendance(ByVal barcode As Integer) As Integer
'Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\LuckDraw.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(ConfigurationSet tings.AppSettings("strConn"))

Dim queryString As String = "UPDATE " & Session("TableName") & " SET
[Attendance]=true WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_barcode As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_barcode.ParameterName = "@Barcode"
dbParam_barcode.Value = barcode
dbParam_barcode.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_barcode)

Dim rowsAffected As Integer = 0
dbConnection.Open()
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close()
End Try

Return rowsAffected
End Function

Function GetCount() As Integer

Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(ConfigurationSet tings.AppSettings("strConn"))
Dim queryString As String = "SELECT count(*) FROM " &
Session("TableName") & " WHERE Attendance = -1"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand

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

Return dt1.Rows(0).Item(0)
dbConnection.Close()
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.Dispose() 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**@discussions.microsoft.com> wrote in message
news:1C**********************************@microsof t.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.OleDb.OleDbException: 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\wwwroot\sonamy\attendance.aspx.vb Line: 45

Stack Trace:
[OleDbException (0x80004005): Unspecified error]
System.Data.OleDb.OleDbConnection.ProcessResults(I nt32 hr) +20
System.Data.OleDb.OleDbConnection.InitializeProvid er() +57
System.Data.OleDb.OleDbConnection.Open() +203
System.Data.Common.DbDataAdapter.QuietOpen(IDbConn ection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Sonamy.Form.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\sonamy\attendance.aspx.vb:45
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET
Version:1.1.4322.573

Here is the Vb code:

Public Class Form
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.ID = "Form"

End Sub
Protected WithEvents lblNotice As System.Web.UI.WebControls.Label
Protected WithEvents lblName As System.Web.UI.WebControls.Label
Protected WithEvents lblBarCode As System.Web.UI.WebControls.Label
Protected WithEvents RequiredFieldValidator1 As
System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents lblTotal As System.Web.UI.WebControls.Label
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents txtBarcode2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtBarcode As
System.Web.UI.HtmlControls.HtmlInputText
Protected WithEvents test As System.Web.UI.HtmlControls.HtmlInputHidden

'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
If Session("EventTitle") = "" Then
Response.Redirect("admin.aspx")
End If
System.Diagnostics.Debug.WriteLine("Posted hidden field = " &
Request("test"))
If Len(Request("test")) <> 0 Then
'We know this is auto postback
Dim barcode As String = Request("test")
Dim da As IDbDataAdapter = GetRecord(barcode)

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).Item("Name") Is System.DBNull.Value Then
lblName.Text = "(anonymous)" Else lblName.Text = dt1.Rows(0).Item("Name")
If dt1.Rows(0).Item("Attendance") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendance(dt1.Rows(0).Item("Barcode"))
lblNotice.Text = "Attendance Updated!"
da.Update(tbl)
End If
End If

Me.txtBarcode.Value = ""
Me.test.Value = ""
End If
SetFocus(txtBarcode, "form1")
Me.lblTotal.Text = GetCount()
End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click
Dim da As IDbDataAdapter = GetRecord(txtBarcode.Value)

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

lblBarCode.Text = txtBarcode.Value

If dt1.Rows.Count = 0 Then
lblNotice.Text = "Person not found!"
Else
If dt1.Rows(0).Item("Name") Is System.DBNull.Value Then
lblName.Text = "(anonymous)" Else lblName.Text = dt1.Rows(0).Item("Name")
If dt1.Rows(0).Item("Attendance") = True Then
lblNotice.Text = "Attendance Updated Already!"
Else
UpdateAttendance(dt1.Rows(0).Item("Barcode"))
lblNotice.Text = "Attendance Updated!"
End If
End If

Me.txtBarcode.Value = ""
SetFocus(txtBarcode, "form1")

Me.lblTotal.Text = GetCount()

End Sub


Function GetRecord(ByVal barcode As Integer) As
System.Data.IDbDataAdapter
'Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\LuckDraw.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(ConfigurationSet tings.AppSettings("strConn"))

Dim queryString As String = "SELECT * FROM " & Session("TableName")
& " WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_barcode As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_barcode.ParameterName = "@Barcode"
dbParam_barcode.Value = barcode
dbParam_barcode.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_barcode)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand

Return dataAdapter
dbConnection.Close()
End Function

Function UpdateAttendance(ByVal barcode As Integer) As Integer
'Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\LuckDraw.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(ConfigurationSet tings.AppSettings("strConn"))

Dim queryString As String = "UPDATE " & Session("TableName") & "
SET
[Attendance]=true WHERE (Barcode = @Barcode)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_barcode As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_barcode.ParameterName = "@Barcode"
dbParam_barcode.Value = barcode
dbParam_barcode.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_barcode)

Dim rowsAffected As Integer = 0
dbConnection.Open()
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close()
End Try

Return rowsAffected
End Function

Function GetCount() As Integer

Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(ConfigurationSet tings.AppSettings("strConn"))
Dim queryString As String = "SELECT count(*) FROM " &
Session("TableName") & " WHERE Attendance = -1"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand

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

Return dt1.Rows(0).Item(0)
dbConnection.Close()
End Function
End Class
Thank you for your help.

cheers,
Rico