Update MS Access from vb .net fails to stick

[JeremyGrand]

This is reposted from the vb forum since a couple of folks there thought it
was more suited to this forum.

When I execute the following, it finishes without error (return value is 0),
but the field dateOfInspection remains unchanged. I've verified that the
values assigned to parameters (held in mRow) are correct and that the
connection string is correct and the connection is open.

I've also tried Cor's suggestion of using "?" parameters, and referring to
them by number. Outcome is identical.

What can I possibly be doing wrong here?

Dim cmd As New OleDbCommand("update cert set dateofinspection=@dof where
id=@id", cnnData)
With cmd
.CommandText = "update cert set dateofinspection=@dof where id=@id"
.Parameters.Add("@id", OleDbType.BigInt)
.Parameters.Add("@dof", OleDbType.Date)
.Parameters("@id").Value = mRow.Item("id")
.Parameters("@dof").Value = mRow("dateofinspection")
.ExecuteNonQuery()
End With

 

[JeremyGrand]

ok, ok, I finally got it. Parameters are positionally sensitive. I can't
believe I spent all this time not finding the answer to this. Surely
hundreds of folks have had this problem!

 

[Norman Yuan]

Just a reminder: "Parameters are positionally sensitive" is only applied to
OleDbClient namespace. If you work with SQL Server, using SqlClient, your
code will be working OK. That is why many .NET samples dealing with Access
DB uses "?" in SQL statement instead of "@ParameterName". Since
@ParameterName is not used by OleDbClient, use it in code could lead to
hard-to-debug mistake, as you just experienced.

 

[Joe Fallon]

Named parameters do not work with Access and .Net code.
The order they are created is the key.
In other words they are positionally sensitive. <g>

I have run into the same issue. As have many others.