PostgreSQL Query Errors?

Hi,

I just moved a site from a Windows 2000 server running SQL Server 2000
to a Win2003 Server with PostGreSQL 8.0 installed.

After transferring the data from one DB to the other, I thought that
there may not be much difference as all of my database stuff is fairly
straight-forward querying.

Sure enough the public part of the site works fine, but I'm hitting
errors when trying to update records through the site's CMS:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Query-based update failed because the row to update could not be
found.

Now, I'm certain that I was able to update existing records right after
transferring the site, but now none of the current articles on the site
can be updated because of this same error. I think it may be a cursor
problem, but it's new to me and have no idea how to cope with it.

The code is far from fancy:

<%
Set rsWirecontent = Server.CreateObject("ADODB.Recordset")
rsWirecontent.Open "wirecontent", strConnect, 1, 2,
adCmdTableDirect
rsWirecontent.Filter = "contentid = '" & contentID & "'"
<<...snip...>>
rsWirecontent.Update
rsWirecontent.Close
%>


Is it something I can fix through a database setting, or do I need to
conform my code to some different standard?

Thanks.

Re: PostgreSQL Query Errors?

Byron wrote:
> Hi,
>
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> Query-based update failed because the row to update could not be
> found.
>
In SQL Server, this error is usually due to the lack of a primary key on the
table being updated.

Why use a recordset to do the update? You would avoid this problem by using
an UPDATE statement...

Bob Barrows
--
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.

Re: PostgreSQL Query Errors?

"Bob Barrows [MVP]" wrote in message
news:uO3DNZ1TFHA.1152@tk2msftngp13.phx.gbl...
> Byron wrote:
>> Hi,
>>
>> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>> Query-based update failed because the row to update could not be
>> found.
>>
> In SQL Server, this error is usually due to the lack of a primary key on
> the
> table being updated.
>
> Why use a recordset to do the update? You would avoid this problem by
> using
> an UPDATE statement...
>
And he is also opening the whole table instead of a single row.

I've seen similar problems in a C++ environment using the MS Cursor Library
where the driver could not identify the row to update unless the primary key
was included in the select clause in addition to the where clause.

Re: PostgreSQL Query Errors?

In article <#3ttey1TFHA.3840@tk2msftngp13.phx.gbl>, notvalid@email.net
says...
> "Bob Barrows [MVP]" wrote in message
> news:uO3DNZ1TFHA.1152@tk2msftngp13.phx.gbl...
> > Byron wrote:
> >> Hi,
> >>
> >> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> >> Query-based update failed because the row to update could not be
> >> found.
> >>
> > In SQL Server, this error is usually due to the lack of a primary key on
> > the
> > table being updated.
> >
> > Why use a recordset to do the update? You would avoid this problem by
> > using
> > an UPDATE statement...
> >
> And he is also opening the whole table instead of a single row.
>
> I've seen similar problems in a C++ environment using the MS Cursor Library
> where the driver could not identify the row to update unless the primary key
> was included in the select clause in addition to the where clause.


So it would be better to use an UPDATE statement? As I said, this code
has worked with SQL Server for a long time. It's only switching to a
PostGreSQL DB that the errors started.

I've used UPDATE statements before when needing to update a single field
in several rows within a table, but in this case I need to update 22
fields in a single row, and some of those fields contain a large and
varied amount of characters, so it seems a bit unwieldy to write an
UPDATE statement for 22 fields, doesn't it?

Thanks.

Re: PostgreSQL Query Errors?

Byron wrote:
>
>
> So it would be better to use an UPDATE statement?

Yes

> As I said, this code
> has worked with SQL Server for a long time.

That is irrelevant to the question of whether or not using DML (update,
insert, delete) is better than a cursor for data modification (hint: cursors
are evil).

> It's only switching to a
> PostGreSQL DB that the errors started.
>
> I've used UPDATE statements before when needing to update a single
> field
> in several rows within a table, but in this case I need to update 22
> fields in a single row, and some of those fields contain a large and
> varied amount of characters, so it seems a bit unwieldy to write an
> UPDATE statement for 22 fields, doesn't it?
>
Nope.

Don't use dynamic sql. Use parameters. If Postgre supports stored
procedures, use them:
http://tinyurl.com/jyy0

If not, use a Command object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Bob Barrows

--
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"