Working with an Access DB

In my database, I have a list of employees, and some info about them (address, social sec no, etc). I made a form in vb.net opening the db, reading the records into a dataset, then i dim a datarow, and push the selected employee's values into the row, and finally display them on the form.

These are my dims: (all in the declarations section)

Code:

---

Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & clientdb)
    Dim objPayeeDA As New OleDb.OleDbDataAdapter("Select * from Payees", objConnection)
    Dim objPayeeCA As New OleDb.OleDbCommandBuilder(objPayeeDA)
    Dim objDataSet As New DataSet()

---

I sucessfully open the the table, and show all the values to txtboxes. Next, the user changes whatever values that are nescessary, and they click the "save" button. This is the code behind it:

Code:

---

Dim objRow As DataRow, idno As Int32
        idno = cboID.SelectedIndex
        objRow = objDataSet.Tables("Payees").Rows(idno)     
        objDataSet.Tables("Payees").Rows(idno).Item("ID") = strID
        objRow.Item("LastName") = txtLastName.Text
        objRow.Item("FirstName") = txtFirstName.Text
        objRow.Item("Address") = txtAddress.Text
        objRow.Item("CitySTZip") = txtCitySTZip.Text
        objRow.Item("SocialSec") = txtSocialSec.Text
        objRow.Item("State") = txtState.Text
        objRow.Item("Locality") = txtLocality.Text
        objRow.Item("Type") = txtType.Text
        objRow.Item("Retirement") = chkRetirement.Checked
        objRow.Item("LegalRep") = chkLegalRep.Checked
        objRow.Item("HshldPayee") = chkHshldPayee.Checked
        objRow.Item("3rdPrtySickPay") = chk3rdPrtySickPay.Checked
        objRow.Item("StatutoryEmployee") = chkStatutoryEmployee.Checked
        objRow.Item("DeferredComp") = chkDeferredComp.Checked
        objRow.Item("ShowSSinFedID") = chkShowSSinFedID.Checked
        objRow.Item("SubjectToSUI") = chkSubjectToSUI.Checked
        objPayeeDA.Update(objDataSet, "Payees")
    End Sub

---

The code errors on the last line! Basically, after I update the datarow, i wanna update the dataset, and save it with the DataAdapter. What am I missing?

By the way, if Karl Moore actually looks at this forum, I would like to thank him for writing such an amazing book. I recomend it anyone who knows a little vb6, and wants to get into the .net world!

What kind of errors are you getting??

Did you fill the dataset?

Yes, I am filling it! Its done in two subs:

Code:

---

    Private Sub frmAddPayee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim i As Integer, strCurrentID As String
        objDataSet.Clear()
        objPayeeDA.FillSchema(objDataSet, SchemaType.Source, "ID")
        objPayeeDA.Fill(objDataSet, "Payees")
        For i = 1 To objDataSet.Tables("Payees").Rows.Count
            strCurrentID = objDataSet.Tables("Payees").Rows(i - 1).Item("ID")
            cboID.Items.Add(strCurrentID)
        Next
        cboID.SelectedIndex = 0
        FillCoDetails()
    End Sub

---

Code:

---

    Public Sub FillCoDetails()
        'On Error Resume Next
        Dim objRow As DataRow
        objRow = objDataSet.Tables("Payees").Rows(cboID.SelectedIndex)
        strID = cboID.SelectedItem
        txtLastName.Text = objRow.Item("LastName")
        txtFirstName.Text = objRow.Item("FirstName")
        txtAddress.Text = objRow.Item("Address")
        txtCitySTZip.Text = objRow.Item("CitySTZip")
        txtSocialSec.Text = objRow.Item("SocialSec")
        txtState.Text = objRow.Item("State")
        txtLocality.Text = objRow.Item("Locality")
        txtType.Text = objRow.Item("Type")
        chkRetirement.Checked = objRow.Item("Retirement")
        chkLegalRep.Checked = objRow.Item("LegalRep")
        chkHshldPayee.Checked = objRow.Item("HshldPayee")
        chk3rdPrtySickPay.Checked = objRow.Item("3rdPrtySickPay")
        chkStatutoryEmployee.Checked = objRow.Item("StatutoryEmployee")
        chkDeferredComp.Checked = objRow.Item("DeferredComp")
        chkShowSSinFedID.Checked = objRow.Item("ShowSSinFedID")
        chkSubjectToSUI.Checked = objRow.Item("SubjectToSUI")
    End Sub

---

wazzzup

What is the error message you are receiving ?

There could be 2 errors your code didnt work...

1) Your access database MUST have a primary key defined for the auto-generation of the CommandBuilder's Update Command to work.

2) Set the CommandBuilder's Update Command manually

+++++++++++++++++++
Dim objConn As OleDbConnection
Dim objAdap As OleDbDataAdapter
Dim objDS As New DataSet()
Dim objTBL As DataTable
Dim objCOMM As OleDbCommandBuilder
Dim sConnect As String = _
"Provider=Microsoft.JET.OLEDB.4.0;Data Source=Demo.mdb"
Dim sSQL As String = _
"SELECT * FROM tblPayees"

objConn = New OleDbConnection(sConnect)
objAdap = New OleDbDataAdapter(sSQL, objConn)
objCOMM = New OleDbCommandBuilder(objAdap)
objAdap.UpdateCommand = objCOMM.GetUpdateCommand

objAdap.Fill(objDS, "Payees")

'Change some data
objTBL = objDS.Tables("Payees")
objTBL.Rows(2)("FieldOne") = "I Have Changed"

objAdap.Update(objDS, "Payees")
+++++++++++++++++++

hth

in vb.net and databases, can you just use an sql update statement?

AHH!! i like the old ways better... :(

Its "easier" to work with this way with mdb's!

no... i prefer sql (the ansi version, not that stupid 1 that comes with oracle)

least for me, i'm used to the old ways of asp and vb... no time 2 adjust to the new ways :(