.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
– Mahadev, Commented Aug 14, 2015 at 7:42
I've tried formatting the date to dd/mm/yyyy just to make sure, but it still doesnt update — LBPLC
– LBPLC, Commented Aug 14, 2015 at 7:46
I suggest you should fire a query using Access first in Access Query Window. That'll give you an idea — Mahadev
– Mahadev, Commented Aug 14, 2015 at 7:51
Could you try opening your connection before assigning into the command object? — Andrew Mortimer
– Andrew Mortimer, Commented Aug 14, 2015 at 7: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
– Andrew Mortimer, Commented Aug 14, 2015 at 7:55

Gord Thompson · Accepted Answer · 2015-08-14 11:16:16Z

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, 2015 at 11:16

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LBPLC Over a year ago

Thanks! I guess I didn't really notice the order I was doing it in. Learn something new every day!

Collectives™ on Stack Overflow

.NET OleDb parameterized query not working as expected

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related