Re: ADO Recordset Update & SQL Server 2005 ??

I've tried explicit .Update inside the loop and outside the loop as a batch
..Update.

There's no 500 error (asp and iis 6) posted on the page. I just added

if err.number <> 0 then response.write err.description inside the loop,
right after rs("column2") = floor(rs("column1")) and this was what showed up
on the page without posting a 500 error:

Multiple-step operation generated errors. Check each status value

This error shows up ONLY when connected to a sql server 2005 server. I found
a few articles on this error and are going through them now. It has to do
with OLEDB and ADO and datatype.

Hung

"Daniel Crichton" <msnews@xxxxxxxxxxxxxxxx> wrote in message
news:O8SWaCm0GHA.4016@xxxxxxxxxxxxxxxxxxxxxxx
Hung wrote on Wed, 6 Sep 2006 14:27:00 -0500:

Provider=sqloledb;Data Source=192.168.1.1;

Set RS = Server.CreateObject("ADODB.Recordset")
RS.CursorLocation = 3 'adUseClient
RS.LockType = 4 'adLockBatchOptimistic
RS.cursortype = 3 'adOpenStatic

query = "select column1, column2 from table order by column1, column2"
data sample: column1 = 99.50, column2 = 0
column1 = 93.83, column2 = 0
I have a loop:

rs.activeconnection = nothing 'disconnect the conn

do while not rs.eof
rs("column2") = floor(rs("column1")) <-- this works with sqlserver
2000, but not sqlserver 2005
rs.movenext
loop
rs.sort = "column1, column2" <-- re-sort

function floor(numeric_value)
'return a floor INT of a float or number
end function

--------

I need to do client-side recordset update and sort like that, but not
updating the data back to the table (disconnected recordset). And
changing
the query to SELECT column1, ROUND(column2,0) isn't an option.

the IIS box and SQL Server box are separated. The exact same code, if
connected to a sql server 2000 database, would update the value of
rs("column2") correctly. But if I change the connection string to point
to
the same database schema/data in sql server 2005, rs("column2") values
always remain 0 (default to 0). That means the rs("column2") =
floor(rs("column1")) statement doesn't work. The only change in the whote
equation would be pointing to either sql server 2000 database or sql
server 2005 database, on differnet machines. There's no change in IIS
box.
Both connection strings are exactly the same, except for IP number to
point to a different box.

There's no error. It just doesn't do the update as expected.

Any idea? could it be OS patches, if not sql server 2005 is the culprit?

Any error messages? Where is your .Update method on the recordset after
you change the column value? .Update stores the changes into the
recordset, it won't post back to the database unless you reconnect it and
perform the appropriate steps to get those changes back into SQL Server.

Dan



.

Relevant Pages

  • Transfering data between External SQL and MS Database - using VBA in EXCEL
    ... I have to loop through 150 + MS Acces databases to combine them into one ... I am using VBA Code to find and import each table. ... is only one table within each database that I need to obtain. ... If I input the data through the SQL Server ...
    (microsoft.public.access.externaldata)
  • Re: DB connection whoas...
    ... up SQL Server and IIS for connectiong to a database. ... IIS, that will access a database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Update 300 DBs with .sql file
    ... Senior Database Administrator ... I support the Professional Association for SQL Server ... > I used to enclose everything into a long string, then loop through each ... > database, use that database, and execute the long string statement. ...
    (microsoft.public.sqlserver.server)
  • Re: IIS (asp.net 2.0):: logon failure
    ... I'm able to connect to the database using the sa logon via the SQL Server ... My best guess it is somewhere in the IIS setup, and the way it is trying to ... >> I'm trying to install a program that builds a database via IIs. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: DB connection whoas...
    ... up SQL Server and IIS for connectiong to a database. ... IIS, that will access a database. ...
    (microsoft.public.sqlserver.programming)