Solved

SMARTER WAY TO UPDATE RECORDS IN ADOSETS

Posted on 2004-08-29
3
219 Views
Last Modified: 2012-05-05
'this is what I do
Private Sub sftBegin_LostFocus()
  Dim rsbrokers As New ADODB.Recordset
  rsbrokers.CursorLocation = adUseServer
  rsbrokers.Open "brookers", "provider=PervasiveOLEDB;Data source=Newindia", adOpenDynamic,       adLockOptimistic, adCmdTableDirect
rsbrokers.MoveFirst
 'I sroll in grid, and  because txtBrokerid is bound to  datasource
'  it will contain  the recordid, e.g    JACK01 when I click in sftBegin

' now below works, but;  is ok when file has e.g. 150 records,  and will be to slow if we goto 2500 'records,  so the question is,  after picking a line from the ADO recordset/grid and coming in this
sub knowing only the key wich belongs to this record, finding a fast way to put the index right away
'to the correct recordset and only do a update!
While rsbrokers.Fields(0) <> txtBrokerId                      
rsbrokers.MoveNext
Wend
 rsBrokers.Fields("Brokername") = sftBegin.text
rsbrokers.update
End Sub

Regards Jack
0
Comment
Question by:BIAPRO
  • 2
3 Comments
 

Expert Comment

by:RLBE
ID: 11926658
if I understand it right, you're looking for txtbrokerid in the recordset and if you did you gonna update the brokername..

if you like to use sql you could just execute a code like this..

-------------------------

dim conn as new Adodb.connection
dim res as new Adodb.recordset

'other codes

conn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source = " & App.Path & "\YOURDATABASE.mdb"

'other codes


res.Open "update YOURTABLE set BROKERNAME = '" & SFTBEGIN.TEXT & "', FIELD2 = " & TXTNUM.TEXT & ", FIELD3 = #" & TXTDATE.TEXT & "# where FIRSTFIELD = " & TXTBROKERID, conn

'other codes

something like that.. then close res..
the code would look for txtbrokerid in the firstfield and update yourtable with a new brokername...
0
 

Author Comment

by:BIAPRO
ID: 11930484
OK, this is what I do
but:  during run-time I get error while conn.Open "bro.................    
is blue at:      .Open       position with  error text:  wrong numbers of arguments  or invalid property assigment.
what did I do wrong??
Thank you,
 Jack



Private Sub cmdtest_Click()
Dim conn As New ADODb.Connection
Dim rsBrokers As New ADODb.Recordset
' it gets blue with error below,  at pos.  .OPEN "br......
conn.Open "brookers", "provider=PervasiveOLEDB;Data source=Newindia", adOpenDynamic, adLockOptimistic, adCmdTableDirect
res.Open "update brookers set  BROKERNAME1= " + txtName1 _ + ",BROKERSTREET1=" + txtStreet1 + ", BROKERCITY1=" + txtCity1 + " WHERE FIRSTFIELD =" + txtBrokerId, conn
Close rsBrokers
End Sub
0
 

Accepted Solution

by:
RLBE earned 250 total points
ID: 11946613
first your recordset is RsBrokers so there is no need for res

and Im not familliar about putting the "brookers" before the provider, try taking out the "brookers" like this:

conn.Open "Provider=PersuasiveOLEDB;Data source=Newindia", adOpenDynamic

and I'm not sure but if "+" wont work then try "&" but this is not really a problem area anyway...
reminders:
In sql, texts are enclosed in '   '  like ='" & txtname1 & "'
numbers - none
dates - #

*ignore* Close rsbrokers could also be written as rsbrokers.close *ignore*

just a reminder :)

I might be the house and internet access a few days.. :) Good luck...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Fix45 challenge 15 86
Using YubiKey with REST API application 2 80
Currency Conversion? 1 37
Recommendation vb6 to vb.net or others 14 39
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now