Problems reading from/writing to Access 2003 using ExecuteReader, ExecuteNonQuery - How do you make posts more readable by the way?RSS

1 reply

Last post Sep 22, 2004 10:36 PM by AlexB1318

  • Problems reading from/writing to Access 2003 using ExecuteReader, ExecuteNonQuery - How do you ma...

    Jul 29, 2004 11:44 AM|JimRossman|LINK

    Hi all, I'm attempting to write my first ASP.NET application that accesses an Access 2003 database. I created a database and a single table. Full privileges exist for everyone. Anyway, my application uses one web form and has two separate classes (one containing methods and a second containing the formats for all each field in the form) working in conjunction with the aspx.vb codebehind. When I try to read a record my OleDbDataReader appaerntly isn't working. I'm not certain if there is an error or not as I get no error message and associated trace. I can see that the attempt to read data yields nothing as the message in my Respond.Write appears on the form. I've included not only the code behind but the methods class as well. I know it is rather lengthy but the difficulty I'm encountering seems pretty straight forward. I'm certain many, if not all of yourselves, have encountered somethign similar to my problem in your cyber-primordial pasts. I might add that the same difficulties are occurring when I attempt to use insert and/or update existing database records. Your assistance would be greatly appreciated. Well, here it is ... ************************************************************************ Here are my methods (Methods.vb and Person.vb). The apsx.vb will follow these. ************************************************************************ Imports System.Data.OleDb Public Class Methods Public Shared Function GetPersons() As DataSet Dim sSelect As String = "SELECT Name FROM WAWF" Dim cmdPerson As New OleDbCommand(sSelect, Connection) Dim daPerson As New OleDbDataAdapter daPerson.SelectCommand = cmdPerson Dim dsPerson As New DataSet daPerson.Fill(dsPerson, "WAWF") Return dsPerson End Function 'Retrieve a single row from the WAWF table. Create SQL command and parameter. Create a connection, 'DBConnection, and new command parameter by running add method of cmdPersons command. Read, forward 'only through the result set returned in the command using OleDbReader. "Try" to open and ExecuteReader 'to receive a single row. Do a read and populate a structure in the Persons.vb class with the information 'from this one returned row. Public Shared Function GetPerson(ByVal Name As String) As Person Dim sSelect As String _ = "SELECT Name, Email, Company, Phone, CageCode, Session, " _ & "DCMA_Yes, DCMA_No, Registered_Yes, Registered_No, USAF, Army, Navy, Marines" _ & "FROM WAWF " _ & "WHERE Name = ? " Dim DBConnection As OleDbConnection = Connection() Dim cmdPersons As New OleDbCommand(sSelect, DBConnection) cmdPersons.Parameters.Add("?", Name) Dim drPersons As OleDbDataReader Try DBConnection.Open() drPersons = cmdPersons.ExecuteReader(CommandBehavior.SingleRow) If drPersons.Read() Then Dim Person As New Person Person.Name = drPersons.Item("Name") Person.Email = drPersons.Item("Email") Person.Company = drPersons.Item("Company") Person.Phone = drPersons.Item("Phone") Person.CageCode = drPersons.Item("CageCode") Person.Session = drPersons.Item("Session") Person.DCMA_Yes = drPersons.Item("DCMA_Yes") Person.DCMA_No = drPersons.Item("DCMA_No") Person.Registered_Yes = drPersons.Item("Registered_Yes") Person.Registered_No = drPersons.Item("Registered_No") Person.USAF = drPersons.Item("USAF") Person.Army = drPersons.Item("Army") Person.Navy = drPersons.Item("Navy") Person.Marines = drPersons.Item("Marines") 'If you were able to connect, open, read and populaet the Person structure 'then Return this structure. If not, then return an empty structure. Return Person End If Catch e As OleDbException 'If an error occurred, return nothing. This will trigger Return Nothing 'an error message to be printed on the web form. Finally DBConnection.Close() 'If it worked and the information was returned to the 'drPersons.Close() 'form, close the data reader and connection. End Try End Function 'Add a new row to the WAWF table. Take the input parameter, Person (structure) adn return 'True or False depending upon whether the attempt to insert into the database was successful. 'Create the connection (DBConnection) and command, cmdPerson. Assign values from Product object 'to the parameters. Open the connection and "Try" to execute a non query to add this record into 'the database. If successful, return True. If an exception occurred then return a False. Close 'the connection regardless. Public Shared Function AddPerson(ByVal Person As Person) As Boolean Dim sInsert As String _ = "INSERT INTO WAWF (Name, Email, Phone, Company, CageCode, Session, " _ & "DCMA_Yes, DCMA_No, Registered_Yes, Registered_No, " _ & "USAF, Army, Navy, Marines) " _ & "VALUES (?Name, ?Email, ?Phone, ?Company, ?CageCode, ?Session," _ & "?DCMA_Yes, ?DCMA_No, ?Registered_Yes, ?Registered_No, " _ & "?Service_USAF, ?Service_Army, ?Service_Navy, ?Service_Marines)" Dim DBConnection As OleDbConnection = Connection() Dim cmdPerson As New OleDbCommand(sInsert, DBConnection) cmdPerson.Parameters.Add("?Name", Person.Name) cmdPerson.Parameters.Add("?Email", Person.Email) cmdPerson.Parameters.Add("?Phone", Person.Phone) cmdPerson.Parameters.Add("?Company", Person.Company) cmdPerson.Parameters.Add("?CageCode", Person.CageCode) cmdPerson.Parameters.Add("?Session", Person.Session) cmdPerson.Parameters.Add("?DCMA_Yes", Person.DCMA_Yes) cmdPerson.Parameters.Add("?DCMA_No", Person.DCMA_No) cmdPerson.Parameters.Add("?Registered_Yes", Person.Registered_Yes) cmdPerson.Parameters.Add("?Registered_No", Person.Registered_No) cmdPerson.Parameters.Add("?Service_USAF", Person.USAF) cmdPerson.Parameters.Add("?Service_Army", Person.Army) cmdPerson.Parameters.Add("?Service_Navy", Person.Navy) cmdPerson.Parameters.Add("?Service_Marines", Person.Marines) AddPerson = True DBConnection.Open() Try cmdPerson.ExecuteNonQuery() Catch e As OleDbException AddPerson = False End Try DBConnection.Close() End Function 'Using the Person structure, update the WAWF database. Again, establish a connection and use this connection to 'establish a command. Load the parameters from the Person structure to the parameters via the command Add method. 'Open and perform ExecuteNonQuery to update the database. If the database was updated teh iCount will be > 0 else 'it will not be incremented. Return True or False dependign upon this return value. Close connection. Public Shared Function UpdatePerson(ByVal Person As Person) As Boolean Dim sUpdate As String _ = "UPDATE WAWF SET" _ & "Name=?Name,Email=?Email, Company=?Company, Phone=?Phone, CageCode=?CageCode," _ & "DCMA_Yes=?DCMA_Yes, DCMA_No=?DCMA_No, Registered_Yes=?Registered_Yes, Registered_No=?Registered_No," _ & "Session1=?Session, USAF=?Service_USAF, Army=?Service_Army, Navy=?Service_Navy, Marines=?Service_Marines" _ & "WHERE Name=?Name" Dim DBConnection As OleDbConnection = Connection() Dim cmdPerson As New OleDbCommand(sUpdate, DBConnection) cmdPerson.Parameters.Add("?Name", Person.Name) cmdPerson.Parameters.Add("?Email", Person.Email) cmdPerson.Parameters.Add("?Company", Person.Company) cmdPerson.Parameters.Add("?Phone", Person.Phone) cmdPerson.Parameters.Add("?CageCode", Person.CageCode) cmdPerson.Parameters.Add("?DCMA_Yes", Person.DCMA_Yes) cmdPerson.Parameters.Add("?DCMA_No", Person.DCMA_No) cmdPerson.Parameters.Add("?Registered_Yes", Person.Registered_Yes) cmdPerson.Parameters.Add("?Registered_No", Person.Registered_No) cmdPerson.Parameters.Add("?Session", Person.Session) cmdPerson.Parameters.Add("?Service_USAF", Person.USAF) cmdPerson.Parameters.Add("?Service_Army", Person.Army) cmdPerson.Parameters.Add("?Service_Navy", Person.Navy) cmdPerson.Parameters.Add("?Service_Marines", Person.Marines) DBConnection.Open() Dim iCount As Integer iCount = cmdPerson.ExecuteNonQuery() DBConnection.Close() If iCount > 0 Then Return True Else Return False End If End Function 'Given a person's name, delete that database entry. Using "Try", perform ExecuteNonQuery. 'If the database was not updated or if an exception occurred, return a False otherwise return 'a value of True. Close connection afterwards. Public Shared Function DeletePerson(ByVal Name As String) As Boolean Dim sDelete As String = "DELETE FROM WAWF " _ & "WHERE Name=?" Dim DBConnection As OleDbConnection = Connection() Dim cmdPerson As New OleDbCommand(sDelete, DBConnection) cmdPerson.Parameters.Add("?", Name) DBConnection.Open() Dim iCount As Integer iCount = cmdPerson.ExecuteNonQuery DeletePerson = True Try iCount = cmdPerson.ExecuteNonQuery If iCount = 0 Then DeletePerson = False End If Catch e As OleDbException DeletePerson = False End Try DBConnection.Close() End Function 'If the checkbox is selected for the DCMA_Yes, set the DCMA_No checkbox to "False". Private Shared Function DCMA_Changed(ByVal chkDCMAYes As Boolean, ByVal chkDCMANo As Boolean) As Boolean If chkDCMAYes Then Return chkDCMANo = False End If End Function 'If the checkbox is selected for the Registered_Yes, set the Registered_No checkbox to "False". Private Shared Function Registered_Changed(ByVal chkRegisteredYes As Boolean, ByVal chkRegisteredNo As Boolean) As Boolean If chkRegisteredYes Then Return chkRegisteredNo = False End If End Function 'Public Shared Function InitializeSessionCounts() As Array ' Dim rCounts(5) As Integer ' Dim x As Integer ' Dim sCounts As String = "SELECT COUNT(*) FROM WAWF" _ ' & "WHERE Session = ?x" ' Dim DBConnection As OleDbConnection = Connection() ' Dim cmdCounts As New OleDbCommand(sCounts, DBConnection) ' DBConnection.Open() ' For x = 0 To 4 ' rCounts(x) = cmdCounts.ExecuteScalar() ' Next x ' DBConnection.Close() ' Return rCounts 'End Function 'Public Shared Function ChkSessionCount(ByVal Count As Integer) As Boolean ' Dim Compare As Integer ' ChkSessionCount = True ' Try ' Compare = Count + 1 ' If Compare > 150 Then ' ChkSessionCount = False ' End If ' Catch e As OleDbException ' ChkSessionCount = False ' End Try 'End Function Public Shared Function Connection() As OleDbConnection Dim sConnectionString As String sConnectionString _ = ConfigurationSettings.AppSettings("ConnectionString") Return New OleDbConnection(sConnectionString) End Function Protected Overrides Sub Finalize() MyBase.Finalize() End Sub End Class ---------------------------------------------------------------------------------------- Person.vb ---------------------------------------------------------------------------------------- Imports System.Data.OleDb Public Class Person Public Name As String Public Email As String Public Company As String Public Phone As String Public CageCode As String Public Session As Integer Public DCMA_Yes As Boolean Public DCMA_No As Boolean Public Registered_Yes As Boolean Public Registered_No As Boolean Public USAF As Boolean Public Army As Boolean Public Navy As Boolean Public Marines As Boolean End Class ---------------------------------------------------------------------------------------- WebForm1.aspx.vb ---------------------------------------------------------------------------------------- Imports System.Data.OleDb Public Class WebForm1 Inherits System.Web.UI.Page Protected WithEvents txtEmail As System.Web.UI.WebControls.TextBox Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox Protected WithEvents txtCompany As System.Web.UI.WebControls.TextBox Protected WithEvents txtCageCode As System.Web.UI.WebControls.TextBox Protected WithEvents ddlSessions As System.Web.UI.WebControls.DropDownList Protected WithEvents chkService3 As System.Web.UI.WebControls.CheckBox Protected WithEvents chkService4 As System.Web.UI.WebControls.CheckBox Protected WithEvents chkService2 As System.Web.UI.WebControls.CheckBox Protected WithEvents chkService1 As System.Web.UI.WebControls.CheckBox Protected WithEvents lblServices As System.Web.UI.WebControls.Label Protected WithEvents lblRegistered As System.Web.UI.WebControls.Label Protected WithEvents lblDCMAContracts As System.Web.UI.WebControls.Label Protected WithEvents btnNew As System.Web.UI.WebControls.Button Protected WithEvents btnUpdate As System.Web.UI.WebControls.Button Protected WithEvents btnDelete As System.Web.UI.WebControls.Button Protected WithEvents lblName As System.Web.UI.WebControls.Label Protected WithEvents lblEmail As System.Web.UI.WebControls.Label Protected WithEvents lblPhone As System.Web.UI.WebControls.Label Protected WithEvents lblCompany As System.Web.UI.WebControls.Label Protected WithEvents lblCageCode As System.Web.UI.WebControls.Label Protected WithEvents lblSessions As System.Web.UI.WebControls.Label Protected WithEvents btnGet As System.Web.UI.WebControls.Button Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents chkDCMAYes As System.Web.UI.WebControls.CheckBox Protected WithEvents chkRegisteredYes As System.Web.UI.WebControls.CheckBox Protected WithEvents chkDCMANo As System.Web.UI.WebControls.CheckBox Protected WithEvents chkRegisteredNo As System.Web.UI.WebControls.CheckBox Protected WithEvents btnCancel As System.Web.UI.WebControls.Button Protected WithEvents lblTitle As System.Web.UI.WebControls.Label #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. Private Sub InitializeComponent() End Sub 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 Enum ControlState NoSelection NewPerson EditPerson End Enum Dim Session_Counts(4) As Integer Dim Session_Choice As Integer Dim Init_Session_Choice As Integer Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then Me.SetControlState(ControlState.NoSelection) 'Session_Counts = Methods.InitializeSessionCounts txtName.TabIndex = 1 txtEmail.TabIndex = 2 txtPhone.TabIndex = 3 txtCompany.TabIndex = 4 txtCageCode.TabIndex = 5 ddlSessions.TabIndex = 6 chkDCMAYes.TabIndex = 7 chkDCMANo.TabIndex = 8 chkRegisteredYes.TabIndex = 9 chkRegisteredNo.TabIndex = 10 chkService1.TabIndex = 11 chkService2.TabIndex = 12 chkService3.TabIndex = 13 chkService4.TabIndex = 14 btnGet.TabIndex = 15 btnNew.TabIndex = 16 btnUpdate.TabIndex = 17 btnDelete.TabIndex = 18 btnCancel.TabIndex = 19 End If End Sub Private Sub SetControlState(ByVal cs As ControlState) Select Case cs Case ControlState.NoSelection ddlSessions.Enabled = True btnNew.Enabled = True btnUpdate.Enabled = True btnUpdate.Text = "Update" btnDelete.Enabled = False btnCancel.Enabled = False btnGet.Enabled = True txtName.Enabled = False Me.EnableEntryControls(True) Case ControlState.NewPerson ddlSessions.Enabled = True btnNew.Enabled = False btnUpdate.Enabled = True btnUpdate.Text = "Add" btnDelete.Enabled = False btnCancel.Enabled = False btnGet.Enabled = False txtName.Enabled = True Me.EnableEntryControls(True) Case ControlState.EditPerson ddlSessions.Enabled = True btnNew.Enabled = False btnUpdate.Enabled = True btnUpdate.Text = "Update" btnDelete.Enabled = True btnCancel.Enabled = True btnGet.Enabled = False txtName.Enabled = False Me.EnableEntryControls(True) End Select End Sub Private Sub EnableEntryControls(ByVal Enabled As Boolean) txtName.Enabled = Enabled txtEmail.Enabled = Enabled txtPhone.Enabled = Enabled txtCompany.Enabled = Enabled txtCageCode.Enabled = Enabled ddlSessions.Enabled = Enabled chkDCMAYes.Enabled = Enabled chkDCMANo.Enabled = Enabled chkRegisteredYes.Enabled = Enabled chkRegisteredNo.Enabled = Enabled chkService1.Enabled = Enabled chkService2.Enabled = Enabled chkService3.Enabled = Enabled chkService4.Enabled = Enabled End Sub Private Sub btnGet_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnGet.Click Dim Person As Person Person = Methods.GetPerson(txtName.Text) If Not Person Is Nothing Then txtName.Text = Person.Name txtEmail.Text = Person.Email txtCompany.Text = Person.Company txtPhone.Text = Person.Phone txtCageCode.Text = Person.CageCode ddlSessions.DataValueField = Person.Session 'Init_Session_Choice = Person.Session chkDCMAYes.Checked = Person.DCMA_Yes chkDCMANo.Checked = Person.DCMA_No chkRegisteredYes.Checked = Person.Registered_Yes chkRegisteredNo.Checked = Person.Registered_No chkService1.Checked = Person.USAF chkService2.Checked = Person.Army chkService3.Checked = Person.Navy chkService4.Checked = Person.Marines Me.SetControlState(ControlState.EditPerson) Else Response.Write("Get Person returned Nothing") End If End Sub Private Sub btnNew_click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnNew.Click Me.Clearfields() Me.SetControlState(ControlState.NewPerson) End Sub Private Sub Clearfields() txtName.Text = "" txtEmail.Text = "" txtCompany.Text = "" txtPhone.Text = "" txtCageCode.Text = "" ddlSessions.SelectedIndex = 0 chkDCMAYes.Checked = False chkDCMANo.Checked = True chkRegisteredYes.Checked = False chkRegisteredNo.Checked = True chkService1.Checked = False chkService2.Checked = False chkService3.Checked = False chkService4.Checked = False End Sub Private Sub btnUpdate_click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click Dim Person As New Person Person.Name = txtName.Text 'Session_Choice = Person.Session Person.Email = txtEmail.Text Person.Phone = txtPhone.Text Person.Company = txtCompany.Text Person.CageCode = txtCageCode.Text Person.Session = ddlSessions.SelectedItem.Value Person.DCMA_Yes = chkDCMAYes.Checked Person.DCMA_No = chkDCMANo.Checked Person.Registered_Yes = chkRegisteredYes.Checked Person.Registered_No = chkRegisteredNo.Checked Person.USAF = chkService1.Checked Person.Army = chkService2.Checked Person.Navy = chkService3.Checked Person.Marines = chkService4.Checked If btnUpdate.Text = "Add" Then If Methods.AddPerson(Person) Then 'Session_Counts(Session_Choice) += 1 Response.Write("Method.AddPerson is True") Me.Clearfields() Else Response.Write("Method.AddPerson is False") 'Response.Write("That Person already exists.") End If Else If Methods.UpdatePerson(Person) Then Me.Clearfields() 'If Session_Choice <> Init_Session_Choice Then ' Session_Counts(Init_Session_Choice) -= 1 ' Session_Counts(Session_Choice) += 1 ' Response.Write("The session change was successful.") 'End If 'Else ' Session_Counts(Session_Choice) -= 1 ' Response.Write("This person has now been deleted.") 'End If End If End If Me.SetControlState(ControlState.NoSelection) End Sub Private Sub btnDelete_click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnDelete.Click If Methods.DeletePerson(txtName.Text) Then Me.Clearfields() Else Response.Write("This person has already been deleted.") End If Me.SetControlState(ControlState.NoSelection) End Sub 'Private Sub ddlSessions_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlSessions.SelectedIndexChanged ' ValidateSessionChoice whereby we take the value of Person.Session ' and see if "Session_Counts(ddlSessions.SelectedItem.Value)+1 >+ 150" ' If it is then we disallow the choice and present a dialog message ' Requestign they choose a different session. Change EnableViewState ' Property to True to allow for a different selection. ' If Not Methods.ChkSessionCount(Session_Counts(Session_Choice)) Then ' Response.Write("This particular session is full. Please select a different session.") ' End If 'End Sub Private Sub chkDCMAYes_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkDCMAYes.CheckedChanged If chkDCMAYes.Checked Then chkDCMANo.Checked = False Else chkDCMANo.Checked = True End If End Sub Private Sub chkRegisteredYes_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkRegisteredYes.CheckedChanged If chkRegisteredYes.Checked Then chkRegisteredNo.Checked = False Else chkRegisteredNo.Checked = True End If End Sub Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click Me.Clearfields() Me.SetControlState(ControlState.NoSelection) End Sub End Class
  • Re: Problems reading from/writing to Access 2003 using ExecuteReader, ExecuteNonQuery - How do yo...

    Sep 22, 2004 08:20 PM|AlexB1318|LINK

    You may want to try writing a simple test sub right in the form to test your connection/query. Then start simplifying/removing what ever you can to isolate the problem. Good luck.