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

Hi

I believe you need to have an update command associated with your
dataadapter.
da.updatecommand("SQL to update the database - e.g. Update ItemMast set
......" (there is an inset and delete command as well)
At the moment you are simply updating the disconnected dataset/recordset
- you need to explicitly write the changes to the BE.
Sorry I can't add more as I use a different approach for dataaccess (a
DAL (data access layer) dll that sort of does it all for me) 
Loads of stuff out there for exact code examples.

HTH

Richard 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: 08 December 2005 05:48
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