• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Update DB Using a Datatabel and datarow

Im tring to move move through a DB and parse one of the columns and place the result into two columns.  the data is last name "," First name.  I need to put the name into two fields.  Im not sure I'm doing this correctly.  It seems there is a million ways to do update the DB.  Every thing is working but I am having trouble saving the parse back to the database.  I have a datagride bound to the dataset so that I can see if what I am doing is working.  Here is my code.

        daEmployee.Fill(DsEmployee1)
        Dim tbl As dsEmployee.T_EMPLOYEEDataTable
        Dim row As dsEmployee.T_EMPLOYEERow
        tbl = DsEmployee1.T_EMPLOYEE
        For Each row In tbl
            Dim myArray() As String = _
            Split(row.Item(4), ",", 3, CompareMethod.Text)
            row(2) = myArray.GetValue(0)
            row(3) = myArray.GetValue(1)
            row.EndEdit()
            row.AcceptChanges()


            daEmployee.Update(DsEmployee1)
            daEmployee.Update(tbl)
        Next row
        DsEmployee1.AcceptChanges()

        daEmployee.Update(DsEmployee1.T_EMPLOYEE)
        DsEmployee1.Clear()
        daEmployee.Fill(DsEmployee1.T_EMPLOYEE)

am I not correct on thinking that the daEmployee.update(tbl) should update the database?
0
justinwood88
Asked:
justinwood88
  • 2
  • 2
1 Solution
 
amyhxuCommented:
Remove row.AcceptChanges() and DsEmployee1.AcceptChanges() before you call daEmployee.Update(DsEmployee1.T_EMPLOYEE).
When you use AcceptChanges before dataAdapter.Update, it thinks of the dataset as all changes committed therefore will not write any changes back to the database.


0
 
justinwood88Author Commented:
I changed the code to:
daEmployee.Fill(DsEmployee1)
        Dim tbl As dsEmployee.T_EMPLOYEEDataTable
        Dim row As dsEmployee.T_EMPLOYEERow
        tbl = DsEmployee1.T_EMPLOYEE
        For Each row In tbl
            Dim myArray() As String = _
            Split(row.Item(4), ",", 3, CompareMethod.Text)
            row(2) = myArray.GetValue(0)
            row(3) = myArray.GetValue(1)
            row.EndEdit()

            Try
                daEmployee.Update(DsEmployee1.T_EMPLOYEE)
                daEmployee.Update(tbl)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        Next row

Now I am getting an syntax error on my update statement but I let the dataadapter wizard build it and it looks fine to me.

UPDATE    T_EMPLOYEE
SET              DEPT_ID = ?, EMPLOYEE_ID = ?, FIRST = ?, LAST = ?, NAME = ?
WHERE     (EMPLOYEE_ID = ?)

I wonder if this line makes a pointer for me to my dataset or what
Do these lines make it so any changes to my tbl and row get changed in my dsEmployee.T_EMPLOYEEDATATABLE
Dim tbl As dsEmployee.T_EMPLOYEEDataTable
Dim row As dsEmployee.T_EMPLOYEERow
0
 
amyhxuCommented:
Clean up your code a little bit. See if this is working:

        daEmployee.Fill(DsEmployee1)

        Dim row As dsEmployee.T_EMPLOYEERow
        For Each row In DsEmployee1.T_EMPLOYEE
            Dim myArray() As String = _
            Split(row.Item(4), ",", 3, CompareMethod.Text)
            row(2) = myArray.GetValue(0)
            row(3) = myArray.GetValue(1)
        Next

        daEmployee.Update(DsEmployee1.T_EMPLOYEE)
        DsEmployee1.Clear()
        daEmployee.Fill(DsEmployee1.T_EMPLOYEE)
0
 
justinwood88Author Commented:
Thanks Amyhxu your awsome.  I also changed the column name FIRST AND LAST because I thought they might be a key word for access or VB.  When I did this I also changed deleted the DataSet and DataAdapter.

Thanks

Justin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now