Update MS Access via vb.net
the below code runs without error, but the MS Access table isn't updating.
What am I missing?
Try
cnn = New
OleDbConnection(ConfigurationManager.ConnectionStrings("accConnectionString").ToString())
cnn.Open()
Catch ex As Exception
Debug.Print("Oops - no connection to database")
Exit Sub
End Try
Dim sql As String
sql = "SELECT * FROM tblSend WHERE UploadedSuccessfullyOn is null
ORDER BY QueuedOn;"
Dim da As New OleDbDataAdapter(sql, cnn)
Dim ds As New DataSet
da.Fill(ds, "dsQueuedToSend")
For Each dr As DataRow In ds.Tables("dsQueuedToSend").Rows
' Perform other unrelated tasks in this space, removed for brevity
' If those other tasks were successful, update Access.
If success = True Then
sql = "UPDATE [tblSend] SET [UploadedSuccessfullyOn] =
@uploadedOn WHERE (([UploadID]) = @uploadId);"
Dim cmd As OleDbCommand
cmd = New OleDbCommand(sql, cnn)
cmd.Parameters.Add("@uploadedOn", OleDbType.Date).Value = Now
cmd.Parameters.Add("@uploadedId", OleDbType.Integer).Value =
dr("UploadID")
cmd.ExecuteNonQuery()
cmd.Dispose()
Console.WriteLine("Success: updated.")
Else
Console.WriteLine("Failed: Not updated.")
End If
Next
da.Dispose()
cnn.Close()
I've also tried with:
sql = "UPDATE [tblSend] SET [UploadedSuccessfullyOn] = ? WHERE
(([UploadID]) = ?);"
Or simply add the values in the not-preferred/non-parameter approach:
sql = "UPDATE [tblSend] SET [UploadedSuccessfullyOn] = #" & Now & "#
WHERE (([UploadID]) = " & dr("UploadID") & ");"
Dim cmd As OleDbCommand
cmd = New OleDbCommand(sql, cnn)
cmd.ExecuteNonQuery()
cmd.Dispose()
None of these approaches updates the MS Access table. Ideas? Thanks!
No comments:
Post a Comment