Problem updating access table

[Jeremy]

When I execute the following, it finishes without error, 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. 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

 

[Jeremy]

No, the code does not contain .commandtext= ... This is a typo in my post.
The code really is:

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

 

[Cor Ligthert]

Jeremy,

A very easy one. When you look in ADONET you can see a long thread about
this done in the last few days. (Involved the OP, Scott M, Bill Vaughn and
slightly me)

The OleDb parameters have not in all cases not named parameters. (I try
telling it based on the thread however somebody proved in my opinion "never"
at the end, as is my assumption as well).

Try it this way.

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

(typed and changed in this message not really checked so watch typos or
other things)

Those names are no problem by the way, however nothing is done with it.

I hope this helps,

Cor

 

[Cor Ligthert]

Jeremy,

At almost the same time I answered a question from somebody in the
newsgroup.

microsoft.public.dotnet.framework.adonet.

His name is Jeremy, I thought it was the same, now I see it are two
different persons.

However I assumed that you was well informed about this newsgroup.

Cor

 

[Jeremy]

Cor, I'm not able to find this thread. Can you give me the subject?

 

[Jeremy]

Cor, I changed the code as you describe, but the result is the same.

 

[Armin Zingler]

No, the code does not contain .commandtext= ... This is a typo in my
post. The code really is:

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

What is the return value of ExecuteNonQuery?

debug.writeline(.ExecuteNonQuery())

As Cor has already mentioned, there's also an ADO.Net group:
microsoft.public.dotnet.framework.adonet

Armin

 

[JeremyGrand]

Armin, .executenonquery() returns 0.

What is the return value of ExecuteNonQuery?

debug.writeline(.ExecuteNonQuery())

As Cor has already mentioned, there's also an ADO.Net group:
microsoft.public.dotnet.framework.adonet

Armin

 

[JeremyGrand]

Cor, now I see what you mean. You've gotta put the params in the right
sequence, since the data provider is too dumb to recognize their names. If
I was the flamin' kinda guy, I'd give MS an earful right now. How much
would it cost them to put something in their dox like "oh, by the way,
parameters are positional, not named". AAAARGH.

Jeremy