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.

Recommended Answers

What is the problem? Are you able to get into DB after connection.open()??

Jump to Post

All 4 Replies

What is the problem? Are you able to get into DB after connection.open()??

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: 

Does anyone have any pointers?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.