Re: ADO Recordset Update & SQL Server 2005 ??

Tech-Archive recommends: Fix windows errors by optimizing your registry

I'm using ASP/VBScript over IIS 6.

As for connection string and provider, there's no change when I'm doing
tests. I have a separate function that would randomly select a database
machine on every refresh. When it hits the sql server 2005 box, the results
are different from others. I of course tested with just one static 2005
machine vs. 2000 machine to get consistent results.

So, the codes inside the asp page are always exactly the same. The
connection strings are identical, except for IP Number to each machine.

0 values are defaults of column2. So, they stay at zero. I even tried to
assign a constant to rs("column2") = 2 and that didn't work either under
2005.

All exact codes and settings work fine if connected to a sql server 2000
box.

Hung

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

"Hung" <dba247@xxxxxxxxx> wrote in message
news:uRKe3qe0GHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
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

<snipped>

Well, I'm floor'd. (Sorry bad pun. <g>)

At the moment I only have SQL2000 to play with. And of course my quick
test
worked fine.

Are you using VB6?
Now a few inane suggestions. [Yeah, like you haven't already done this.
<g>]
Are you using the same provider for both?
Check the CursorType.
Are you sure the connection is really gone?
(perhaps the defaults are different for another provider)

Have you tried to expand the various statements ...
nJunk = rs("column2").Value
nJunk = floor(nJunk)
rs("column1").Value = nJunk
... &etc
To see where '0' shows up?

Your floor function doesn't show a return. This is a typo, correct?

hth
-ralph




.

Relevant Pages

  • Re: Newbie question on SQL connection
    ... database on my developer machine. ... > to develop databases away from the live SQL server - that way in the ... > domain SQL Server. ... Does your connection string contain a typo? ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: .NET and SQL Server on Separate Machines
    ... add this domain account as a valid login to your sql server. ... Everything was running correctly when the database ... > The connection string in our web.config file originally looked like this: ...
    (microsoft.public.sqlserver.connect)
  • Re: .NET and SQL Server on Separate Machines
    ... add this domain account as a valid login to your sql server. ... Everything was running correctly when the database ... > The connection string in our web.config file originally looked like this: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: FP2003 fails to validate data connection
    ... The connection string is not correct or DSN are not correct. ... The machine is running XP Pro with IIS 5.1 and SQL Server 2000 both running. ... > The FP2003 Database Results Wizard and Database Interface Wizard have no problem locating the active DSNs on the machine, ... Other apps I have written have no problem using these DSNs. ...
    (microsoft.public.frontpage.programming)
  • Re: One Connection String for Multiple Users (SQL)
    ... Hitchhiker’s Guide to Visual Studio and SQL Server ... "William Vaughn" wrote: ... This uses the> same connection string for all instances of the application. ...
    (microsoft.public.sqlserver.connect)