Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Problems with vb.net UPDATE statement for MS Access

Avatar of BozM
BozM asked on
Microsoft AccessMicrosoft Development
7 Comments1 Solution148 ViewsLast Modified:
Hi,
I have a a short procedure which runs through a datatable and updates a certain number of entries. It doesn't seem to make the changes to the database at all. It displays the correct row counts etc. but just won't update the rows in the database.

I don't mind how it happens, i just want the contents of two fields in the Database swopped in certain cases.

Any suggestions on what i have done wrong would be very much appreciated.

Dim Conn As New OleDbConnection
        Conn.ConnectionString = My.Settings.ConnReminder
        Conn.Open()
        Dim strSQL As String
        strSQL = " SELECT * FROM tblReminders"

        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter(strSQL, Conn)
        Dim row As DataRow
        Dim strReminderText As String
        Dim strSubject As String
        Dim strRemNo As String
        Dim donecount As Integer
        donecount = 1

        da.Fill(dt)
        MsgBox(dt.Rows.Count)

        For Each row In dt.Rows
            If row("MMDept").ToString = "MBM" Then
                donecount = donecount + 1
                strRemNo = row("RemNo").ToString
                strSubject = row("Subject").ToString
                strReminderText = row("ReminderText").ToString

                Dim myCommand As New OleDbCommand("UPDATE tblReminders SET [ReminderText] = @strSubject, [Subject] = @strReminderText WHERE [RemNo]= @strRemNo", Conn)

                With myCommand.Parameters
                    .AddWithValue("@strSubject", strSubject)
                    .AddWithValue("@strReminderText", strReminderText)
                    .AddWithValue("@strRemNo", strRemNo)
                End With
                da.UpdateCommand = myCommand
                da.Update(dt)
            End If
            
        Next
        dt.AcceptChanges()

        MsgBox(donecount)

        Conn.Close()
        MsgBox("Done")