Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problems with vb.net UPDATE statement for MS Access

Posted on 2014-12-09
7
Medium Priority
?
89 Views
Last Modified: 2014-12-22
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
Comment
Question by:BozM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40488780
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 40488856
You can also just update directly:

mycommand.commandtext = "UPDATE tblReminders SET [ReminderText] ='" & strSubject & "', [Subject] =' & " strReminterTExt & "' WHERE [RemNo]='" & strRemNo & "'"
mycommand.execuatenonquery
0
 

Author Comment

by:BozM
ID: 40488954
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 85
ID: 40488969
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
 
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 1000 total points
ID: 40489098
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
 

Author Comment

by:BozM
ID: 40497183
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
 

Author Closing Comment

by:BozM
ID: 40514038
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question