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")
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"
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.
As an alternative to using the command object, you could use the data adapter's update method.