Laurent

hi folks, i'm trying to update a recordset depending on a condition, here's my code

Dim rsSoumission As Recordset
Set rsSoumission = New Recordset
rsSoumission.LockType = adLockOptimistic
rsSoumission.CursorType = adOpenDynamic
Set rsSoumission = frmMenuPrincipale.cn.Execute("select * from soumission where nosoumission = '" & tempSoum & "' and norevision='" & tempRev & "'")
With rsSoumission
If newRev Then .AddNew
!noSoumission = tempSoum
!noRevision = tempRev
...
.update
set rssoumission = nothing

but now i get the error message saying that the provider cannot support....blah blah blah
I'll be among the best soon, very soon!!!<P ID="edit"><FONT class="small"><EM>Edited by Laurent on 08/03/01 07:43 AM.</EM></FONT></P>

Thinker

Even though you are requesting a Dynamic cursor, the provider you are using may not support Dynamic. When it doesn't support a type it changes to a type it does support. I am guessing it is changing to a type that won't allow adding a new record. You can check this with this line of code.
<pre>
If rsSoumission.Supports(adAddNew) Then Debug.Print "Yes" Else Debug.Print "No"
</pre>
This is just a guess because otherwise the code looks good to me.

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]

Laurent

basicly what i want it to do is create a new record if newrev (new revision) equal true, but if it doesn't than cange the existing record. do i have the correct syntax for this?

I'll be among the best soon, very soon!!!

Thinker

I think it is ok. Will it update an existing record? If the only problem is adding a new record then check my previous post. Otherwise there is a bigger problem.

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]

Laurent

i was modifying it this morning, it was adding correctly any new records, but i can,t get it to modify an existing one? i just don't get it!

I'll be among the best soon, very soon!!!

Thinker

Do you get an error? Or does it act like it is changing but when you look again it hasn't changed it.

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]

Laurent

THE RESQUESTED OPERATION CANNOT BE SUPPORTED BY THE PROVIDER is my error message?

I'll be among the best soon, very soon!!!

jcd

I dont know if this will make a difference but have you tried this syntax [recordset].open [query statement], [activeconection], [cursor], [locktype]...I dont know if this will help the error but, it doesnt hurt to try.
Regards
jcd

Thinker

This might seem a little strange but try this.
<pre>
...
If Not newRev Then rsSoumission.Move 0
.Update
</pre>

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]

Laurent

i tried all in one line also but the same error occurs

I'll be among the best soon, very soon!!!

Laurent

i also tried to move it to 0 but no succes!! : (

I'll be among the best soon, very soon!!!

Thinker

I am sorry I missed the Error message in your first post.
Please add this line right before the .Update
<pre>
If rsSoumission.Supports(adUpdate) Then MsgBox "Yes" Else MsgBox "No"
</pre>
Run again and see if it says Yes or No. Does it say Yes for a new addition and No for an update?

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]

Laurent

is says false to both? i don't get it i'm using the same function for the past 3 months with addnew and it says it doesn't support it...

I'll be among the best soon, very soon!!!

ANUNEZ

Hi.
I bet that you are using micosoft jet for this.
This happens because the execute method returns a readonly recordset for that provider. Try using the open method:

set rst = new adodb.recordset

rst.Open "SELECT * FROM table1", cnn, adOpenDynamic, adLockOptimistic

instead of execute.

I hope this help.

Thinker

Laurent, I'm sorry I couldn't get back to you sooner but it
looks like ANUNEZ figured it out. I hope you followed his
advice because he is right. When you created a new
recordset and set its parameters
<pre>
Set rsSoumission = New Recordset
rsSoumission.LockType = adLockOptimistic
rsSoumission.CursorType = adOpenDynamic
</pre>
Then it was right. But as soon as you did
<pre>
Set rsSoumission = frmMenuPrincipale.cn.Execute("select * from soumission where nosoumission = '" & _
tempSoum & "' and norevision='" & tempRev & "'")
</pre>
You wiped out the recordset you created to begin with and
replaced it with another one created by the Connection
object. As ANUNEZ pointed out, the default would not allow
changes. It really shouldn't have allowed additions either
but somehow that still worked. By using the .Open method
and passing all the parameters, the Connection object could
make sure the recordset was initialized the way you needed
it.

There are a couple of small changes I would make to ANUNEZ's
Open statement.
<pre>
.Open "SELECT * FROM table1", cnn, adOpenKeyset, adLockOptimistic, adCmdText
</pre>
You can't get a Dynamic cursor even if you ask for it. It
changes it to Keyset. But, it doesn't give you all the features
of a Keyset like the Bookmark and RecordCount. If you
specify Keyset then you do get them. Also telling the
Connection object that the source is an SQL statement with the adCmdText option, will speed it up a little.

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]