[AccessD] VB.Net, trying to update some records in Access

Have you stepped through the code and tested rows as you go along?   The
Fill method you're using implements IDataAdapter.Fill, so be sure that's
working before you try to troubleshoot the rest.  An alternative would
be

Da.Fill(ds, "Itemmast")

You don't need the BeginEdit, so remove that line.

You also need to dispose the connection and the dataadapter at the end
of the routine.  

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, December 07, 2005 9:48 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VB.Net, trying to update some records in Access


I found that after I sent off the message. This is currently what I am
working with:


Dim row As DataRow
Dim ds As DataSet
Dim da As OleDbDataAdapter
Dim conn As OleDbConnection
Dim Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MOMS_UPDATE\MOMS_UPDATE.MDB;Persist Security Info=False"

Dim mySQL As String = "SELECT ItemNo, Desc1 FROM Itemmast WHERE Desc1
Like " & Chr(34) & "%|%" & Chr(34) & ";"

Try
   conn = New OleDbConnection(Connstr)
   da = New OleDbDataAdapter(mySQL, conn)
   ds = New DataSet
   da.Fill(ds)

   For Each row In ds.Tables(0).Rows
      row.BeginEdit()
      row.Item("Desc1") = replacePipes(row.Item("Desc1"))
   Next

   da.Update(ds.Tables(0))

   MessageBox.Show("Done")

Catch ex As Exception
   MessageBox.Show("Error " & Err.Number & " : " &
ex.Message.ToString()) End Try


The code runs through without any errors, only the Access table is never
updated. Gawd, I miss DAO.

The code is just a small sample test of a larger project. This project
takes cdb tables from a pocket PC then puts them into an Access table,
which is read into datasets that are passed to a web service which
"synchronizes" with a SQL Server database. and data is brought back in
reverse order. One table has an item description field which contains
apostrophes which were hanging up a process. These apostrophes were
converted to Pipes and I want to simply run this function on the data
before it is pumped back into the PPC.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Wednesday, December 07, 2005 5:24 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VB.Net, trying to update some records in Access


What do you want it to do and why are you using AcceptChanges?  That
removes the modified flag from the row.  And calling in update on the
adapter does tell it to write the updates back to the table.

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, December 07, 2005 4:22 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] VB.Net, trying to update some records in Access


Does anyone see what I am missing? I am trying to update some rows in a
temporary Access database by changing any pipes ( | ) to apostrophes ( '
). It runs through the code just fine. It's like the dataset is writing
the updates back to the table.

        Dim strTemp As String
        Dim row As DataRow
        Dim ds As DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection
        Dim mystr As String
        Dim Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MOMS_UPDATE\MOMS_UPDATE.MDB;Persist Security Info=False"
        Dim mySQL = "SELECT ItemNo, Desc1 FROM Itemmast WHERE Desc1 Like
" &
Chr(34) & "%|%" & Chr(34) & ";"
        Try
            conn = New OleDbConnection(Connstr)
            da = New OleDbDataAdapter(mySQL, conn)
            ds = New DataSet
            da.Fill(ds)

            For Each row In ds.Tables(0).Rows
                strTemp = ""
                strTemp = replacePipes(row.Item("Desc1"))
                row.BeginEdit()
                'row("Desc1") = replacePipes(row("Desc1"))
                row.Item("Desc1") = strTemp
                'row.EndEdit()
                row.AcceptChanges()

            Next

            'ds.AcceptChanges()
            da.Update(ds.Tables(0))


            MessageBox.Show("dONE")
        Catch ex As Exception
            MessageBox.Show("Error " & Err.Number & " : " &
ex.Message.ToString())
        End Try

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com