We help IT Professionals succeed at work.

Oracle recordset update

Grunge
Grunge asked
on
I have set up a record set which is populated, however there is no .edit extension available on the recordset object

how do I modify the recordset?

the connect string:
COR = "Provider=MSDASQL.1;Persist Security Info=False;UID=TTTT;PWD=TTTT;Data Source=COR;Mode=write"

I think the 'Mode' bit is wrong
Comment
Watch Question

Commented:
Try changing your Recordset cursor location to ADuseclient.

If that doesn't work, use an update sql command...

Cheers,

CJ.
Head of Software Services
CERTIFIED EXPERT
Commented:
Grunge, you don't need to specify .Edit with ADO.

Presumably you have at some point:

rstRecords.Open "Select * From MyTable",COR,adOpenStatic,adLockOptimistic

Or similar

If so then:

With rstRecords
  .Fields("MyField").Value = "New Value"
  .Update
End With

Or similar is sufficient. The .Update is crucial as the change will not be committed otherwise. .AddNew is provided to create a new empty record but there is no equivalent to the DAO .Edit method.

Author

Commented:
what do I put in the 'mode' part of the connection string?

Author

Commented:
I get data into a db field now, however i recieve this Error:

Key column information is insufficient or incorrect. Too many rows were affected by update.


CERTIFIED EXPERT
Top Expert 2012

Commented:
Post the SQL statement.

Anthony

Commented:
dim cor as string
dim rstMyRst as recordset
dim conMyCon as connection
dim sql as string

COR = "Provider=MSDASQL.1;Persist Security Info=False;UID=TTTT;PWD=TTTT;Data Source=COR;"

set rstMyRst = createobject("adodb.recordset")
set conMyCon = createobject("adodb.connection")

'open connection to  database with read and write access
conMyCon.mode = adModeReadWrite
ConMyCon.cursorlocation =  aduseclient
ConMyCon.open COR

sql = "select * from tblMyTable"

rstMyRst.open sql, adopenforwardonly, adlockoptimistic
rstMyRst("fldField1") = "yada"
rstMyRst("fldfield2") = "yadayada"
rstmyrst.update

Or if you already know exactly what you need to update:

dim cor as string
dim rstMyRst as recordset
dim conMyCon as connection
dim sql as string

COR = "Provider=MSDASQL.1;Persist Security Info=False;UID=TTTT;PWD=TTTT;Data Source=COR;"

set rstMyRst = createobject("adodb.recordset")
set conMyCon = createobject("adodb.connection")

'open connection to  database with write access
conMyCon.mode = adModeWrite
ConMyCon.cursorlocation =  aduseclient
ConMyCon.open COR

sql = "update tblMyTable set fldfield1='yada'"

sql = sql & " where fldfield1= 'yadayada'"

rstMyRst.open sql, adopenforwardonly, adlockoptimistic

Commented:
Ooops, sorry, forgot part of the rstMyrst.open syntax

They should be :
rstMyRst.open sql, conMyCon, adopenforwardonly, adlockoptimistic

Commented:
I forgot to 'select' one of the fields that I was using 'where' on ...this caused the error

Author

Commented:
oops!... using my colleagues computer and forgot to log on as me!...

Author

Commented:
The answer to my core question... thanks all

Explore More ContentExplore courses, solutions, and other research materials related to this topic.