1

I have a VB.NET application and doing an update to an MS Access database but behaves very weird. If I supply only one parameter, it works like a charm but adding an extra parameter and they are swaps. My code:

Using cmd = New OleDbCommand
    cmd.Connection = conn

    'cmd.Parameters.AddWithValue("@currentStaffId", currentRow.Item("StaffId"))
    'cmd.Parameters.AddWithValue("@staffId", tempStaffId)
    'cmd.Parameters.AddWithValue("@firstName", firstNameEdit.Text.Trim())
    'cmd.Parameters.AddWithValue("@secondName", secondNameEdit.Text.Trim())
    'cmd.Parameters.AddWithValue("@phone", tempPhone)
    cmd.Parameters.AddWithValue("@email", emailAddressEdit.Text.Replace(" ", ""))
    cmd.Parameters.AddWithValue("@username", usernameEdit.Text.Trim())
    'cmd.Parameters.AddWithValue("@password", passwordEdit.Text.Trim())
    'cmd.Parameters.AddWithValue("@isAdmin", If(Not isAdminEdit.IsChecked Or IsNothing(isAdminEdit.IsChecked), False, True))

    cmd.CommandText = "UPDATE Tbusers SET [Username]=@username, [EmailAddress]=@email WHERE StaffId = 100"

    cmd.ExecuteNonQuery()

    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("StaffId") = tempStaffId
    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("FirstName") = firstNameEdit.Text.Trim()
    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("SecondName") = secondNameEdit.Text.Trim()
    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("PhoneNumber") = tempPhone
    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("EmailAddress") = emailAddressEdit.Text.Replace(" ", "")
    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("Username") = usernameEdit.Text.Trim()
    dataTable.Rows.Item(dataTable.Rows.IndexOf(currentRow)).Item("IsAdmin") = If(Not isAdminEdit.IsChecked Or IsNothing(isAdminEdit.IsChecked), False, True)

    CloseUpdateUserModal(sender, e)

    updateUsersTable()
End Using

Now, this swaps the email and username parameters but if I supply them individually they work okay. This has been driving me nuts, can anyone point out what's wrong with the code or why it's behaving that way? I have an insert statement which works okay with the same structure. I have commented out the other parameters because the update doesn't work completely as in it doesn't throw an error but the where clause just skips through because of an invalid value if I use all of them. And btw, I have tried to step through the code and checked the individual values of the parameters and they reflect the right values. Any help would be highly appreciated!

Parfait
  • 104,375
  • 17
  • 94
  • 125
lulliezy
  • 1,741
  • 5
  • 24
  • 49
  • 2
    Add the parameters in the order that they appear in the query - it doesn't actually use the parameter names, only their positions. Also, [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/). – Andrew Morton Jul 12 '19 at 13:10
  • Thanks that was really helpful, its so messed up if that's the case, I am from another language and this is not the case so excuse my ignorance. – lulliezy Jul 12 '19 at 13:26
  • 1
    This is only the case with OleDB parameters. – LarsTech Jul 12 '19 at 13:34
  • Possible duplicate of [OleDbCommand parameters order and priority](https://stackoverflow.com/questions/1476770/oledbcommand-parameters-order-and-priority) – Andrew Morton Jul 12 '19 at 16:49

1 Answers1

0

From the documentation for the OleDbCommand.Parameters property:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Community
  • 1
  • 1
Ryan Prechel
  • 6,592
  • 5
  • 23
  • 21