recordset.addNew() with postgres

[michal]

hi guys,
i am using postgres 8.2 and try to add some records to a table with
ADODO ... unfortunately i end up with an error when calling the
addNew() method. it says that the recordset does not support
updating...

i do open the connection the following

set RS = server.createObject("scripting.dictionary")
RS.open "usr", cn, 1, 3, 2
RS.addNew()
RS.update()
RS.close()
set RS = nothing

cn is the connection established with OLEDB ... connection works fine
(i can execute SQL-statements, etc) but the addnew does not work ...
the same works fine on MSSQL

does anyone have experience in this?

 

[Bob Barrows [MVP]]

hi guys,
i am using postgres 8.2 and try to add some records to a table with
ADODO ... unfortunately i end up with an error when calling the
addNew() method. it says that the recordset does not support
updating...

i do open the connection the following

set RS = server.createObject("scripting.dictionary")
scripting.dictionary???????

RS.open "usr", cn, 1, 3, 2

Why don't you show us your real code. This Open statement would have
thrown an error if your code was really as shown.

 

[michal]

oh sorry,
i just typed the wrong thing. the code has adodb.recordset ...
the above code should just demonstrate what i am doing .. i never
worked with postgres before so thats why i am wondering why this is
not working ....
should it work or is adodb somehow not supported by postgres?

 

[Bob Barrows [MVP]]

AFAIK, it should work, but I've never used postgres, and I've never even
considered using adCmdTable from ASP. Try using a sql SELECT statement
and adCmdText (1). If you are going to be adding records only, then use
" ... WHERE =" to make sure you don't retrieve all the records in the
table without intending to use them.

You might consider using DML (INSERT, UPDATE and DELETE sql statements)
instead of recordsets for maintaining data in any case.

 

[michal]

tried it with a the comandText but it ends up with the same error.
i know i could do it with DML but want to avoid this for several
reasons .. i like the way with the recordset but unfortunately it
makes problems with postgres. damn!
any other ideas?

 

[Bob Barrows [MVP]]

tried it with a the comandText but it ends up with the same error.

Show the code: "commandText" looks fishy.

i know i could do it with DML but want to avoid this for several
reasons .. i like the way with the recordset but unfortunately it
makes problems with postgres. damn!
any other ideas?

DML.,DML, DML ... oh, sorry.
IMO, in ASP, recordsets should only be used to retrieve readonly data
for display purposes. All data modifications should be done via DML.
Since cursors (recordsets) are notoriously inefficient for data
modifications, avoiding them increases the site's scalability.

However, how about a disconnected client-side cursor instead of
server-side:

const adLockBatchOptimistic = 4
set rs=createobject("adodb.recordset")
rs.cursorlocation=3 'adUseClient
rs.open "select <list of fields> from usr where 1=2",cn, _
,adLockBatchOptimistic ,1

'http://www.aspfaq.com/show.asp?id=2096

set rs.activeconnection=nothing
cn.close
rs.addnew
....
rs.update
rs.addnew
....
rs.update
cn.open
set rs.activeconnection=cn
rs.updatebatch
rs.close
cn.close

 

[michal]

will try the disconnected recordset, but i am already thinking of
switching back to mssql
you dont have to be sorry ) i just prefer the manipulation with the
recordset ...

- i never had performance issues
- dont have to care about the datatype (e.g. dml i need apostrophies
for string, etc.)
- the code looks nicer than some cryptic string concatination

anyway thats a pragmatic question and is not important here ... i will
play around and maybe discover the reason ... or maybe its just not
supported by the postgres provider ...

michal

 

[Bob Barrows [MVP]]

will try the disconnected recordset, but i am already thinking of
switching back to mssql
you dont have to be sorry ) i just prefer the manipulation with the
recordset ...

- i never had performance issues
- dont have to care about the datatype (e.g. dml i need apostrophies
for string, etc.)

If you use parameters, delimiters are no longer an issue:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

- the code looks nicer than some cryptic string concatination

No concatenation when using parameters (see above)