Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

SMARTER WAY TO UPDATE RECORDS IN ADOSETS

'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
BIAPRO
Asked:
BIAPRO
  • 2
1 Solution
 
RLBECommented:
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
 
BIAPROAuthor Commented:
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
 
RLBECommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now