recordset.addNew() with postgres

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
Fri Apr 13 12:58:51 CDT 2007

michal wrote:
> 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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

michal
Fri Apr 13 13:44:39 CDT 2007

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?

On Apr 13, 7:58 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> michal wrote:
> > 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.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Bob
Fri Apr 13 14:20:55 CDT 2007

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 wrote:
> 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?
>
> On Apr 13, 7:58 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> wrote:
>> michal wrote:
>>> 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.
>>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

michal
Fri Apr 13 15:22:12 CDT 2007

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?

On Apr 13, 9:20 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> 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 wrote:
> > 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?
>
> > On Apr 13, 7:58 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> > wrote:
> >> michal wrote:
> >>> 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.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.- Hide quoted text -
>
> - Show quoted text -

Bob
Fri Apr 13 15:44:19 CDT 2007

michal wrote:
> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

michal
Sat Apr 14 05:19:53 CDT 2007

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

On Apr 13, 10:44 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> michal wrote:
> > 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
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Bob
Sat Apr 14 08:08:37 CDT 2007

michal wrote:
> 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)


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"