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

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

0
BozM
Asked:
BozM
  • 3
  • 2
  • 2
2 Solutions
 
Shaun KlineLead Software EngineerCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can also just update directly:

mycommand.commandtext = "UPDATE tblReminders SET [ReminderText] ='" & strSubject & "', [Subject] =' & " strReminterTExt & "' WHERE [RemNo]='" & strRemNo & "'"
mycommand.execuatenonquery
0
 
BozMAuthor Commented:
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?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
Shaun KlineLead Software EngineerCommented:
Per Microsoft's documentation, you cannot use named parameters in your query. You need to use the question mark (?) placeholder, and the parameters you define are substituted into your query at execution time in the order you add the parameters to the command object.
0
 
BozMAuthor Commented:
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.
0
 
BozMAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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