Read Access Database into VB.net form

[consc197]

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.

[oussama_1]

Try this

'Read data according to CandidateReferenceNumber
Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter()
' * = everything
if combobox1.selecteditem = "" or nothing then
msgbox("Enter CandidateReferenceNumber")
else
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from tblCandidates WHERE fldCandidateReferenceNumber = " & combobox1.selecteditem, Connection)
   Dim DtSet1 As System.Data.DataSet
        DtSet1 = New System.Data.DataSet
        MyCommand.Fill(DtSet1)
     'Candidate = your table in form
     Candidate.DataSource = DtSet.Tables(0)
        Connection.Close()
        end if

'########################################


'Delete Data
run.CommandType = CommandType.Text
run.CommandText = "DELETE FROM tblCandidates WHERE fldCandidateReferenceNumber = " & combobox1.selecteditem
 Using Connection
                Connection.Open()
                run.ExecuteNonQuery()
                Connection.Close()
            End Using


'for updating use INSERT INTO statement