Solved

SMARTER WAY TO UPDATE RECORDS IN ADOSETS

Posted on 2004-08-29
3
220 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
bunnyEars challenge 6 99
strCount chalenge 3 69
Device same like our heart 12 75
Remove Password Protection from pdf (with password) using an command line executable 5 25
RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
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 …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

911 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

16 Experts available now in Live!

Get 1:1 Help Now