Solved

SMARTER WAY TO UPDATE RECORDS IN ADOSETS

Posted on 2004-08-29
3
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

752 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