Disconnected Recordset using Command AddNew

I want to make ADODB disconnected recordset using Command object(Set rS = Command.Execute) and then apply changes, add new records to recordset (Add New) and most important update Batch these changes.
I get "Provider doesn't support ..." when setting LockType to LockBatchOptimistic or calling Rs.AddNew
I set Cursor location to Use Client and
else but still doesn' work ...
DamnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vaggCommented:
When the LockType is LocaBatchOptimistic,the CursorType is adOpenStatic,so you can not change any record.

You should use
BeginTrans
RollbackTrans
CommitTrans



0
DamnAuthor Commented:
Sorry vaqq I dont quite get it.
Problem is not disconnected recordset, it can update database, I want to get it using command object because I want to use command type property , parameters etc...
Do you know how can I get diconnected recordset using command _
0
vaggCommented:
Pls see the following example:

Set a = New Connection
Set b = New Command

With a
  .Provider = "sqloledb"
  .ConnectionString = "user id=sa;password=;data source=test;initial catalog=student"
  .Open
End With

a.BeginTrans

On Error GoTo LocalErr
   
'not you can use command
   b.Execute "select * from namelist"
     
     
a.CommitTrans


LocalErr:
  a.RollbackTrans


0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DamnAuthor Commented:
OK vaqq, I'm sorry but I don't see any recordset in your example and I need data and not only to pass SQL to server.
The point here is disconnected recordset.
(One you get with Set Rs.ActiveConnection = Nothing)
I will have to reject you now, so I could unlock question, but if you think you now understand question feel free to try again or post comment ....
Thanks,

Damn
0
gwambCommented:
For example u can create an Recordset connected to SQL server.
And with metod Recordset.Save u can save this recordset to disk.
With metod Recordset.Open, u can load this Recordset from disk.

Another example:
Dim rcTemp As New ADODB.Recordset

rcTemp.Fields.Append "Name", adVarChar, 100
rcTemp.Fields.Append "Age", adInteger

rcTemp.Open

rcTemp.AddNew
rcTemp.Fields![Name] = "Michal"
rcTemp.Fields![Age] = 25
rcTemp.Update

rcTemp.AddNew
rcTemp.Fields![Name] = "Orlando"
rcTemp.Fields![Age] = 28
rcTemp.Update

rcTemp.AddNew
rcTemp.Fields![Name] = "Henry"
rcTemp.Fields![Age] = 22
rcTemp.Update

rcTemp.Save "c:\temp\rcTemp.xml", adPersistXML

rcTemp.Close
Set rcTemp = Nothing

0
DamnAuthor Commented:
qwamb, you propose that I get recordset with command, then save it to disk and later open it ?
Or manually fill new recordset from recordset obtained through command ?
I'm aware of this but it's
I guess it could work but isn't there a way to disconnect recordset obtained through command ?
If you are shure that there isn't and you can prove it, I'm ready to grade your answer and give you points...
0
gwambCommented:
Yes, there are two ways how to create unbound recordset with command.
1. Save to disk and open it
2. Manually fill new recordset.

I'm sorry, I don't know about another solution.


U can't use Recordset.Clone method!

U can't set connection to NOTHING of an opened recordset.


0
DamnAuthor Commented:
gwamb,
I will reject your answer for now.
If nobody gives another solution I will give you points.
Let someone else try but if nobody gets anything better untill tommorow, please lock question again and i'll give you points.
0
leesssCommented:
Set rS = Command.Execute

This is the problem.

You create a default recordset.  any time when you create a default recordset the cursor is set to adOpenforwardOnly and the locktype is set to adLockReadOnly.

You cannot change those settings.

So you have to find ways around it if you insist using command.execute.  The way to do this is to creat a new rich recordset and copy the data from the default recordset.

qwamb provides a bypass method which gives you a rich recordset, that is why you can do all the updates, etc.  I would use the command.execute if all you need is read only data which is 8 times faster than the rich recordset that you create using new.  If you want updates, you should to use the rich recordset.

Hope this clear things up.
0
DamnAuthor Commented:
this is same as qwamb-s .
I am going ti give him points if he locks question
0
gwambCommented:
Ok. So I'm locking the question.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.