.NET OleDb parameterized query not working as expected

Question

I'm trying to pass this Update command to a database, it completes ok with no errors but it doesnt update the database and I can't understand why?

    Dim Cmd As OleDbCommand
    Dim SQL As String
    Dim objCmd As New OleDbCommand

    Dim Con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Settings.MasterPath)
    Dim Item = "08/08/2015"


            SQL = ("UPDATE [Hol Dates] SET [" & EmployeeList.Text & "]= @Htype WHERE [HDate]=@Hdate")

    Cmd = New OleDbCommand(SQL, Con)
    objCmd = New OleDbCommand(SQL, Con)
    objCmd.Parameters.AddWithValue("@Hdate", item)
    objCmd.Parameters.AddWithValue("@Htype", "None")
    Con.Open()
    Dim ans = MsgBox("Do you want to unbook this holiday?", MsgBoxStyle.YesNo)

    If ans = MsgBoxResult.Yes Then
        objCmd.ExecuteNonQuery()
    Else
        Exit Sub
    End If

    Con.Close()
    Con.Dispose()

Are you sure you are passing `Hdate` correctly ? I mean there is a possibility that Database date is in one format (e.g. dd/mm/yyyy) and you are passing different format value. And I suggest you should fire a query using `Access` first in `Access Query Window`. That'll give you an idea — Mahadev, Aug 14 '15 at 07:42
I've tried formatting the date to dd/mm/yyyy just to make sure, but it still doesnt update — LBPLC, Aug 14 '15 at 07:46
I suggest you should fire a query using `Access` first in `Access Query Window`. That'll give you an idea — Mahadev, Aug 14 '15 at 07:51
Could you try opening your connection before assigning into the command object? — Andrew Mortimer, Aug 14 '15 at 07:54
Are 'item' and 'Item' actually different variables? Might be better to declare it as a datetime so that it is passed into the command as such. Your current declaration may be setting it as a string. — Andrew Mortimer, Aug 14 '15 at 07:55
Item is the same variable, and I do delcare it as a DateTime earlier in the code. I've written the following now: `"UPDATE [Hol Dates] SET [Employee] = 'NHB' WHERE [HDate] = 10/08/2015"` and it still doesn't update even though the code executes OK. This is a basic Update command, I don't understand why it's not working — LBPLC, Aug 14 '15 at 08:55
Method `ExecuteNonQuery()` return number of the affected rows. Check this number `If objCmd.ExecuteNonQuery() > 0 Then ...`. If no rows affected it means that no rows exists in the database with condition `[HDate]=@Hdate`. — Fabio, Aug 14 '15 at 08:58
Do you have somewhere on the top level `Try Catch` block which swallow exceptions — Fabio, Aug 14 '15 at 09:01
@Fabio, I checked the `ExecuteNonQuery` and it isn't affecting any rows. `[Hdate]` is a `Date` type column. I've put a `Try Catch` in place and no exceptions are generated. Im sure I'm missing something tiny, probably to do with format of the date, I'll keep trying — LBPLC, Aug 14 '15 at 09:53
Try create a parameter manually: `Dim param As New OleDbParameter With {.ParameterName = "@HDate", .OleDbType = OleDbType.Date, .Value = item}`. Where item is `Date` type — Fabio, Aug 14 '15 at 10:00

score 2 · Accepted Answer · answered Aug 14 '15 at 11:16

2

You need to reverse the order in which you add the parameters to the OleDbCommand object. OleDb allows us to assign names to parameters but it ignores the names and only pays attention to the order in which the parameters appear in the CommandText.

Therefore, since your SQL statement refers to Htype and then Hdate you need to add the parameters in that same order.

answered Aug 14 '15 at 11:16

Gord Thompson

116,920
32
215
418

Thanks! I guess I didn't really notice the order I was doing it in. Learn something new every day! – LBPLC Aug 14 '15 at 11:28

.NET OleDb parameterized query not working as expected

1 Answers1

Linked

Related