Avatar of BozM
BozM
 asked on

Problems with vb.net UPDATE statement for MS Access

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")

Open in new window

Microsoft DevelopmentMicrosoft Access

Avatar of undefined
Last Comment
BozM

8/22/2022 - Mon
Shaun Kline

See this link on Microsoft's website, under remarks:
The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters.

As an alternative to using the command object, you could use the data adapter's update method.
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
BozM

ASKER
Thanks Guys,

I will try Scott's shorter version. Is there anything actually wrong with my code? I know it's long winded but is there any error in it?
Scott McDaniel (EE MVE )

I don't use that syntax, but perhaps changing the order in which you declare the parameters:

Using cmd As New OLEDB.Command
  With myCommand.Parameters
   .AddWithValue("@strSubject", strSubject)
   .AddWithValue("@strReminderText", strReminderText)
   .AddWithValue("@strRemNo", strRemNo)
  End With
 
cmd.CommandText = ""UPDATE tblReminders SET [ReminderText] = @strSubject, [Subject] = @strReminderText WHERE [RemNo]= @strRemNo"

<etc etc here>
End Using
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Shaun Kline

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
BozM

ASKER
Apologies all, I have been out of the office for the last few days.

I will rework the code on Monday and come back to you. In code for the same form, albeit for a different button, I have named parameters for an MS Access command and that work's fine - I will try your suggestions on Monday and report back.

Thanks for your patience.
BozM

ASKER
Thanks guys, so in the end, I just used executenonquery and took on Shaun's suggestion about positional parameters. Worked in the end. Thanks for your help. Happy Christmas to you both.