Re: ADO Recordset Update & SQL Server 2005 ??

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

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

  • Re: Cross Database Join, C++ program set up confusion
    ... Well what kind of data are you returning if it is not in Recordset? ... Recordset is returned (it is fractionally faster as ADO does not waste time ... If the default database is db1 on the ... It is a function of SQL Server. ...
    (microsoft.public.vc.database)
  • Re: Full-Text Results to MS Access
    ... Being new to SQL Server and ADO, ... (My database was started in pre-ADO days so the Access ... like a true/false flag, a value, etc., but if the result is a recordset, I ... based on some full-text search criteria. ...
    (microsoft.public.sqlserver.fulltext)
  • ADO dll
    ... I have been working with VB6 using ADO to access a SQL Server database ... Recordset returned as clsXA.Recset ... Database type, tested with SQL Server 7 and SQL Server 2000, Access ... UserID, Password, Server Name and Authentication are optional. ...
    (microsoft.public.vb.database.ado)
  • Re: CRecordset and sqlserver (identity seed and number)
    ... Identity values are allocated by SQL Server - you can't set the value ... clean sequences without gaps (if you are inserting a record in a transaction ... empty database, don't expect to get the same sequence of values. ... When ii add a new recordset in this table the recordset was ...
    (microsoft.public.vc.mfc)
  • Re: When to connect; when to disconnect?
    ... Each database connection requires about 24K of SQL Server memory. ... > I'm quite new to MS SQL Server programming and I am wondering when should ... > I connect and when should I disconnect. ...
    (microsoft.public.sqlserver.programming)