Problem updating access table

[Jeremy]

When I execute the following, it finishes without error, but the field
dateOfInspectio n 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("u pdate cert set dateofinspectio n=@dof where
id=@id", cnnData)
With cmd
.CommandText = "update cert set dateofinspectio n=@dof where id=@id"
.Parameters.Add ("@id", OleDbType.BigIn t)
.Parameters.Add ("@dof", OleDbType.Date)
.Parameters("@i d").Value = mRow.Item("id")
.Parameters("@d of").Value = mRow("dateofins pection")
.ExecuteNonQuer y()
End With

[Jeremy]

Re: Problem updating access table

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

Dim cmd As New OleDbCommand("u pdate cert set dateofinspectio n=@dof where
id=@id", cnnData)
With cmd
.Parameters.Add ("@id", OleDbType.BigIn t)
.Parameters.Add ("@dof", OleDbType.Date)
.Parameters("@i d").Value = mRow.Item("id")
.Parameters("@d of").Value = mRow("dateofins pection")
.ExecuteNonQuer y()
End With

[Cor Ligthert]

Re: Problem updating access table

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("u pdate cert set dateofinspectio n=? where
id=?", cnnData)
With cmd
.CommandText = "update cert set dateofinspectio n=? where id=?"
.Parameters.Add ("", OleDbType.Date)
.Parameters.Add ("", OleDbType.BigIn t)
.Parameters(0). Value = mRow("dateofins pection")
.Parameters(1). Value = mRow.Item("id")
.ExecuteNonQuer y()
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]

Re: Problem updating access table

Jeremy,

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

microsoft.publi c.dotnet.framew ork.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]

Re: Problem updating access table

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


"Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
news:%23FfIankW FHA.3760@TK2MSF TNGP15.phx.gbl. ..[color=blue]
> 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("u pdate cert set dateofinspectio n=? where
> id=?", cnnData)
> With cmd
> .CommandText = "update cert set dateofinspectio n=? where id=?"
> .Parameters.Add ("", OleDbType.Date)
> .Parameters.Add ("", OleDbType.BigIn t)
> .Parameters(0). Value = mRow("dateofins pection")
> .Parameters(1). Value = mRow.Item("id")
> .ExecuteNonQuer y()
> 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
>
>
>[/color]

[Jeremy]

Re: Problem updating access table

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


"Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
news:%23FfIankW FHA.3760@TK2MSF TNGP15.phx.gbl. ..[color=blue]
> 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("u pdate cert set dateofinspectio n=? where
> id=?", cnnData)
> With cmd
> .CommandText = "update cert set dateofinspectio n=? where id=?"
> .Parameters.Add ("", OleDbType.Date)
> .Parameters.Add ("", OleDbType.BigIn t)
> .Parameters(0). Value = mRow("dateofins pection")
> .Parameters(1). Value = mRow.Item("id")
> .ExecuteNonQuer y()
> 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
>
>
>[/color]

[Armin Zingler]

Re: Problem updating access table

"Jeremy" <jeremy@ninprod ata.com> schrieb[color=blue]
> No, the code does not contain .commandtext= ... This is a typo in my
> post. The code really is:
>
> Dim cmd As New OleDbCommand("u pdate cert set dateofinspectio n=@dof
> where id=@id", cnnData)
> With cmd
> .Parameters.Add ("@id", OleDbType.BigIn t)
> .Parameters.Add ("@dof", OleDbType.Date)
> .Parameters("@i d").Value = mRow.Item("id")
> .Parameters("@d of").Value = mRow("dateofins pection")
> .ExecuteNonQuer y()
> End With[/color]


What is the return value of ExecuteNonQuery ?

debug.writeline (.ExecuteNonQue ry())

As Cor has already mentioned, there's also an ADO.Net group:
microsoft.publi c.dotnet.framew ork.adonet

Armin

[JeremyGrand]

Re: Problem updating access table

Armin, .executenonquer y() returns 0.

"Armin Zingler" <az.nospam@free net.de> wrote in message
news:%23VXr6hlW FHA.3716@TK2MSF TNGP12.phx.gbl. ..[color=blue]
> "Jeremy" <jeremy@ninprod ata.com> schrieb[color=green]
>> No, the code does not contain .commandtext= ... This is a typo in my
>> post. The code really is:
>>
>> Dim cmd As New OleDbCommand("u pdate cert set dateofinspectio n=@dof
>> where id=@id", cnnData)
>> With cmd
>> .Parameters.Add ("@id", OleDbType.BigIn t)
>> .Parameters.Add ("@dof", OleDbType.Date)
>> .Parameters("@i d").Value = mRow.Item("id")
>> .Parameters("@d of").Value = mRow("dateofins pection")
>> .ExecuteNonQuer y()
>> End With[/color]
>
>
> What is the return value of ExecuteNonQuery ?
>
> debug.writeline (.ExecuteNonQue ry())
>
> As Cor has already mentioned, there's also an ADO.Net group:
> microsoft.publi c.dotnet.framew ork.adonet
>
> Armin[/color]

[JeremyGrand]

Re: Problem updating access table

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

"Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
news:%23FfIankW FHA.3760@TK2MSF TNGP15.phx.gbl. ..[color=blue]
> 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).
>[/color]