Bizarre error causes update of one record to update ALL records

I came across something rather odd when updating a value in a database
table. Instead of updating the record with the new value, it updates
ALL the records in the table.

Make a database table with fields ID (int, PK) and DATE1
(smalldatetime). Add three records 1, 2, and 3 with null DATE1. Then
execute the following ASP page:


<%
const DBServer = "localhost"
const DBName = "DEVL"
const DBOwner = "dbo"
const DBPrefix = "DEVL.dbo."
const DBPassword = "password"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "DRIVER={SQL Server};UID=" & DBOwner & ";PWD=" &
DBPassword & ";SERVER=" & DBServer & ";DATABASE=" & DBName

Set rsRec = Server.CreateObject("ADODB.Recordset")
strSql = "select DATE1 from TEST where ID=1"
rsRec.Open strSql, objConn, adOpenForwardOnly, adLockOptimistic

rsRec("DATE1") = "10/10/2005"
rsRec.Update

rsRec.Close
objConn.Close

%>

The DATE1 field for ALL THREE records is set to 10/10/2005, even though
only ID=1 was specified in the SQL where clause.

Some things to note:

1) It does not happen if the connection is adOpenStatic.
2) It does not happen if you do "select *" or include the ID (primary
key) field in the select statement.

I'm sure this has to do with using adOpenForwardOnly (which I have
stopped doing) but I would just like some sort of reason why it
happened in the first place.

Re: Bizarre error causes update of one record to update ALL records

See inline:
Paul wrote:
> I came across something rather odd when updating a value in a database
> table. Instead of updating the record with the new value, it updates
> ALL the records in the table.
>
> Make a database table with fields ID (int, PK) and DATE1
> (smalldatetime).

Is this what you mean?

create table Test (
ID int primary key,
DATE1 smalldatetime NULL)


> Add three records 1, 2, and 3 with null DATE1.

Is this what you mean?
INSERT INTO Test
select 1,null
union select 2, null
union select 3, null



> Then
> execute the following ASP page:
>
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.open "DRIVER={SQL Server};UID=" & DBOwner & ";PWD=" &
> DBPassword & ";SERVER=" & DBServer & ";DATABASE=" & DBName
>
> Set rsRec = Server.CreateObject("ADODB.Recordset")
> strSql = "select DATE1 from TEST where ID=1"
> rsRec.Open strSql, objConn, adOpenForwardOnly, adLockOptimistic
>
> rsRec("DATE1") = "10/10/2005"
> rsRec.Update
>
> rsRec.Close
> objConn.Close
>
> %>
>
> The DATE1 field for ALL THREE records is set to 10/10/2005, even
> though only ID=1 was specified in the SQL where clause.
>
> Some things to note:
>
> 1) It does not happen if the connection is adOpenStatic.

You mean CursorType, not "connection". The recordset is a cursor which has a
cursor type.

> 2) It does not happen if you do "select *" or include the ID (primary
> key) field in the select statement.

This makes sense. You should always include the primary key field when using
a cursor (ugh) to perform an update. ADO uses it to identify the row that
needs to be updated. In this case, the only information ADO has from your
cursor is the DATE1 column and its initial value (null). So when it creates
a sql statement to perform the update, all it can do is:

update test set DATE1='20051010' WHERE DATE1 is null

>
> I'm sure this has to do with using adOpenForwardOnly

Well, it sort of does. Static cursors include primary key information even
when not specified in the select list. ForwardOnly cursors are designed to
be very lightweight and fast, so they only include the minimal information
specified in the sql statement.

> (which I have stopped doing)

Go back to doing it for your readonly recordsets (which are the only type
you should be opening in ASP). ForwardOnly cursors are the way to go in ASP.

> but I would just like some sort of reason why it
> happened in the first place.

It's because you failed to include the primary key in the select list. Use
SQL Profiler to see what's being sent to the server. You'll see.

Best practice in ASP* is to use DML (INSERT,UPDATE and DELETE) statements
to perform data modifications, not cursors (recordsets). Recordsets should
be readonly and used to retrieve data for display only.

More best practices:
Use the native OLE DB provider for SQL Server (SQLOLEDB) instead of the
obsolete ODBC provider. You will find that it is smarter than ODBC about
selecting the row to update when you don't specify the primary key in the
select list. See http://www.aspfaq.com/show.asp?id=2126 for an example of a
sqloledb connection string. Use that string in your test and see the
difference.

Don't use selstar in production code: http://www.aspfaq.com/show.asp?id=2096

Don't use dynamic sql, which can leave you vulnerable to sql injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Instead, use stored procedures:
http://groups.google.com/group/microsoft.public.inetserver.a sp.db/msg/563f6ac9b41090f9?hl=en&lr=&c2coff=1

or, if you are dead set against using procedures, at least use a Command
object to parameterize your sql strings:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Use a nonambiguous date format (#yyyy/mm/dd# in vbscript) when supplying
dates.



* This best practice does not apply to desktop applications, where it makes
more sense to use recordsets for data modification due to their builtin
concurrency-handling, somehting which is not needed in ASP due to the
shortness of time a page is executing on the server.

HTH,
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.