Re: ADO Recordset Update & SQL Server 2005 ??

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?

Thanks!

Hung




"Ralph" <nt_consulting64@xxxxxxxxx> wrote in message
news:a8adnfaf8PlLbWPZnZ2dnUVZ_qadnZ2d@xxxxxxxxxxxxxxx

"Hung" <dba247@xxxxxxxxx> wrote in message
news:%236U7Qvc0GHA.5048@xxxxxxxxxxxxxxxxxxxxxxx
I have a line that says:

rs("column_name") = x

this works fine under sql server 2000, but doesn't work with sql server
2005. What's the problem or changes in sql server 2005?

Thanks!

HH


[A little detail on what is not working would have been nice, but...]

Try rs("column_name").Value = x, or the spelling or case.

Look for something in your code. I doubt very much that construct has been
tinkered with. If it was the entire phone system in Redmond would have
been
shutdown long ago. <g>

-ralph




.

Relevant Pages

  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO Recordset Update & SQL Server 2005 ??
    ... I've tried explicit .Update inside the loop and outside the loop as a batch ... There's no 500 error (asp and iis 6) posted on the page. ... This error shows up ONLY when connected to a sql server 2005 server. ... the same database schema/data in sql server 2005, ...
    (microsoft.public.data.ado)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
  • Re: MS Access DAO -> ADO.NET Migration
    ... full SQL Server and I see the logic you explained in a multi user ... allow two users to access the same database file Read/Write at any given ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Access DAO -> ADO.NET Migration
    ... For that it is much harder to handle the incremental identifier, ... database but although they have the data, they are not connected at the same ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... SQL Server Management Studio is nowhere to be found on my ...
    (microsoft.public.dotnet.framework.adonet)