Problems accessing data from recordset (SQL Server 2000)

[CrazyAtlantaGuy]

I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.

This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.

When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)

For example:

set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")

Outputs "AAA//////" whereas:

set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")

Outputs "DDD/////"

Any ideas? Thanks for your help - it is much appreciated!

Lee

 

[Bob Barrows [MVP]]

When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)

For example:

set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096

response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
rs("FieldC") & "//" & rs("FieldD")

Outputs "AAA//////" whereas:

set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
rs("FieldB") & "//" & rs("FieldA")

Outputs "DDD/////"

OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188

Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126

Bob Barrows

 

[CrazyAtlantaGuy]

Thanks Bob!

I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.

The quick response is much appreciated,

Lee