Hello everyone,

I have a problem, i am unsure how to read the data from my Access Database into my vb.net form. I did some searching but i didn't manage to find anything useful so i would appreciate any pointers.

Here is the code from frmAddNewCandidate i am using that takes the data from the form and saves it into the access database:

        'Database Operations: Add all data to Access Database
        Dim connection As OleDb.OleDbConnection
        Dim mydb, mystr As String

        mystr = ("Provider=Microsoft.jet.oledb.4.0;" & _
            "Data Source=|DataDirectory|\SkyCoffee.mdb")

        connection = New OleDb.OleDbConnection(mystr)

        connection.Open()

        mydb = "INSERT INTO tblCandidates (fldCandidateReferenceNumber,fldCandidateFirstName,fldCandidateSurname) values ('" & txtReferenceNumber.Text & "','" & txtFirstName.Text & "','" & txtSurname.Text & "')"

        Dim run = New OleDb.OleDbCommand

        Try

            run = New OleDb.OleDbCommand(mydb, connection)

            run.ExecuteNonQuery()

            'Load DisplayCandidates tab
            Me.tbDisplayCandidates.Show()
            tbcCandidatesManager.SelectedIndex = 0

            'Display message box
            MsgBox("Candidate Successfully Added to Database.")

            'Close connection
            connection.Close()

            'Reset form fields
            txtReferenceNumber.Text = ""
            txtFirstName.Text = ""
            txtSurname.Text = ""

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "OleDB Error")
        End Try

        'End If

Now in frmEditCandidate i also have a ComboBox that is filled in with the Data from the column fldCandidateReferenceNumber, here is the code i am using:

        'Load CandidateReferenceNumber column into ComboBox to use for Editing
        Dim conSelectCandidate As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.jet.oledb.4.0;" & _
        "Data Source=|DataDirectory|\SkyCoffee.mdb")
        Dim cmdSelectCandidate As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT fldCandidateReferenceNumber FROM tblCandidates", conSelectCandidate)

        conSelectCandidate.Open()

        Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmdSelectCandidate)
        Dim myDataSet As DataSet = New DataSet()

        myDA.Fill(myDataSet, "tblCandidates")

        cmbEditSelectCandidate.DataSource = myDataSet.Tables("tblCandidates").DefaultView
        cmbEditSelectCandidate.ValueMember = "fldCandidateReferenceNumber"
        cmbEditSelectCandidate.DisplayMember = "fldCandidateReferenceNumber"

        conSelectCandidate.Close()
        conSelectCandidate = Nothing

So now what i want to achieve is according to the CandidateReferenceNumber selected in my ComboBox i want to load my form with the Data from the Access Database so i can Edit the Data, then i need to be able to Save the Data back into the Database.

Hello Pgmer, connection.Open() works fine - my problem is that how would i code it so that once i select a record in my ComboBox it would load the textboxes of my form with the rest of the data from Access so that i can Edit the record.

I've spent some time searching and i came up with the following website, http://www.java2s.com/Code/VB/Database-ADO.net/DataBindingTextField.htm - however i tested the code provided and the following errors that the StoredProcedure doesn't exist:

    'Execute the Query
    objDataReader = objCommand.ExecuteReader()

Any help will be greatly appreciated.

Ok, i decided to change the whole logic of my Edit Form. But i get an error again, i would appreciate any help.

Let me explain first the structure of my program:
- I have frmMain - this is the main page of the program, from here i have several buttons which all load in Panels
- I have button called btnDisplayCandidates - this loads frmDisplayCandidates - this form has a TabControl with 3 TabPages.
- One of the TabPage is called tbEditCandidates

First i define my variables as Global:

Imports System.Data.OleDb
Module modGlobalVariables
    Public Class GlobalVariables
        Public Shared ds As New DataSet()
        Public Shared intCurrentIndex As Integer = 0
        Public Shared da As New OleDbDataAdapter()
        Public Shared conn As New OleDbConnection()
    End Class
End Module

Then in btnDisplayCandidates_Click i have the following code:

        'Enable Editing
        GlobalVariables.conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        GlobalVariables.da.SelectCommand = New OleDbCommand("SELECT fldCandidateReferenceNumber, fldCandidateFirstName, fldCandidateSurname, fldCandidateGender FROM tblCandidates")
        GlobalVariables.da.SelectCommand.Connection = GlobalVariables.conn


        GlobalVariables.da.UpdateCommand = New OleDbCommand("UPDATE tblCandidates SET fldCandidateFirstName = @fldCandidateFirstName, fldCandidateSurname = @fldCandidateSurname, fldCandidateGender = @fldCandidateGender WHERE fldCandidateReferenceNumber = @fldCandidateReferenceNumber")
        GlobalVariables.da.UpdateCommand.Connection = GlobalVariables.conn
        GlobalVariables.da.UpdateCommand.Parameters.Add("@fldCandidateFirstName", OleDbType.VarChar, 40, "fldCandidateFirstName")
        GlobalVariables.da.UpdateCommand.Parameters.Add("@fldCandidateSurname", OleDbType.VarChar, 40, "fldCandidateSurname")
        GlobalVariables.da.UpdateCommand.Parameters.Add("@fldCandidateGender", OleDbType.VarChar, 40, "fldCandidateGender")
        GlobalVariables.da.UpdateCommand.Parameters.Add("@fldCandidateReferenceNumber", OleDbType.Integer, 5, "fldCandidateReferenceNumber") '.SourceVersion = DataRowVersion.Original



        GlobalVariables.da.InsertCommand = New OleDbCommand("INSERT INTO tblCandidates(fldCandidateFirstName, fldCandidateSurname, fldCandidateGender) VALUES(@fldCandidateFirstName,@fldCandidateSurname,@fldCandidateGender)")
        GlobalVariables.da.InsertCommand.Connection = GlobalVariables.conn
        GlobalVariables.da.InsertCommand.Parameters.Add("@fldCandidateFirstName", OleDbType.VarChar, 40, "fldCandidateFirstName")
        GlobalVariables.da.InsertCommand.Parameters.Add("@fldCandidateSurname", OleDbType.VarChar, 40, "fldCandidateSurname")
        GlobalVariables.da.InsertCommand.Parameters.Add("@fldCandidateGender", OleDbType.VarChar, 40, "fldCandidateGender")


        GlobalVariables.da.DeleteCommand = New OleDbCommand("DELETE FROM tblCandidates WHERE fldCandidateReferenceNumber = @fldCandidateReferenceNumber")
        GlobalVariables.da.DeleteCommand.Connection = GlobalVariables.conn
        GlobalVariables.da.DeleteCommand.Parameters.Add("@fldCandidateReferenceNumber", OleDbType.Integer, 5, "fldCandidateReferenceNumber")




        GlobalVariables.da.Fill(GlobalVariables.ds)

        If GlobalVariables.ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFields()
        Else
            MsgBox("No records exist in database")
        End If

I also have the following function:

    Public Sub FillFields()
        frmCandidateManager.txtEditReferenceNumber.Text = GlobalVariables.ds.Tables(0).Rows(GlobalVariables.intCurrentIndex).Item("fldCandidateReferenceNumber").ToString()
        frmCandidateManager.txtEditFirstName.Text = GlobalVariables.ds.Tables(0).Rows(GlobalVariables.intCurrentIndex).Item("fldCandidateFirstName").ToString()
        frmCandidateManager.txtEditSurname.Text = GlobalVariables.ds.Tables(0).Rows(GlobalVariables.intCurrentIndex).Item("fldCandidateSurname").ToString()
        frmCandidateManager.cmbEditGender.Text = GlobalVariables.ds.Tables(0).Rows(GlobalVariables.intCurrentIndex).Item("fldCandidateGender").ToString()
    End Sub

In tbEditCandidate i have an button called btnUpdate and i have the following code in this button:

    Dim dr As DataRow

    dr = GlobalVariables.ds.Tables(0).Rows(GlobalVariables.intCurrentIndex) 'This gets a reference to the row currently being edited
    dr.BeginEdit()
    dr("fldCandidateFirstName") = txtEditFirstName.Text
    dr("fldCandidateSurname") = txtEditSurname.Text
    dr("fldCandidateGender") = cmbGender.Text
    dr.EndEdit()

    GlobalVariables.da.Update(GlobalVariables.ds)  'Ask the dataadapter to call the UpdateCommand and update the database
    GlobalVariables.ds.AcceptChanges() 'Commits the change to the dataset.

When i run the program in debug form and i click on the Update button i get the following error message; the error message concerns the function GlobalVariables.da.Update(GlobalVariables.ds);

System.Data.DBConcurrencyException was unhandled
  HResult=-2146232011
  Message=Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
  RowCount=1
  Source=System.Data
  StackTrace:
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
       at SkyCoffee.frmCandidateManager.btnUpdate_Click(Object sender, EventArgs e) in C:\Users\Constantinos\Dropbox\Application\App - SkyCoffee\SkyCoffee\SkyCoffee\Interviews\frmCandidateManager.vb:line 327
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at SkyCoffee.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Edited 4 Years Ago by consc197

This article has been dead for over six months. Start a new discussion instead.