Solved

Problems with vb.net UPDATE statement for MS Access

Posted on 2014-12-09
7
85 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 84
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 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 250 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 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