VERY odd classic ASP problem on 2005 upgrade

[Elliott Roberts]

I'm seeing a VERY odd issue after moving my data from a SQL2000 DB to
a SQL2005 DB. About half the time, when I request a value (using
ors.fields("pagedata") for instance), I get nothing returned. I know
there's data in the field, and I'm on the correct RS object.

Sometimes, when I move the call to another portion of the page, I get
the data (while on the same RS object). I need to move to a new server
running 2005, and folks are starting to chomp at me. Help! I've never
seen anything vaguely like this.

Thanks,

Elliott Roberts

 

[Bob Barrows [MVP]]

I'm seeing a VERY odd issue after moving my data from a SQL2000 DB to
a SQL2005 DB. About half the time, when I request a value (using
ors.fields("pagedata") for instance), I get nothing returned. I know
there's data in the field, and I'm on the correct RS object.

Sometimes, when I move the call to another portion of the page, I get
the data (while on the same RS object). I need to move to a new server
running 2005, and folks are starting to chomp at me. Help! I've never
seen anything vaguely like this.

I don't know how you expect us to help you. Show us some code to allow us to
reproduce your problem.

As a guess, could it be this old ODBC bug?
http://www.aspfaq.com/show.asp?id=2188

 

[Elliott Roberts]

I don't know how you expect us to help you. Show us some code to allow us to
reproduce your problem.

As a guess, could it be this old ODBC bug?http://www.aspfaq.com/show.asp?id=2188

Sorry for the lack of detail - I thought it might be a common
weirdness with an upgrade like this. Guess not. The code ran like a
Swiss watch against a 2000 server (and still does). It is very long,
and well, not all that well written. Sadly, I need this to work
against 2005 without re-coding the whole thing. Here's a sample that
contains relevant parts:

connectionstring = "Driver={SQL Native
Client};Server=x.x.x.x;Database=distweb;Uid=xxx;Pwd=xxx;MARS_Connection=yes;"
Set oC = Server.CreateObject("ADODB.Connection")
oC.Open connectionstring
Set oRS = Server.CreateObject("ADODB.Recordset")
sqlStr = "SELECT * FROM pagedata WHERE pageid = " & int(pageid) & "
and pid = " & int(pid) & " AND folder = '" & application("varfolder")
& "' order by rank, id"
set oRS = oC.execute(sqlSTR)

' If there are no paragraphs created, then display an under
construction notice
IF ors.EOF THEN
Response.Write("<CENTER><B>This section is not yet complete.</B></
CENTER><BR>")
ELSE
' Loop through the available paragraphs
DO WHILE not ORS.EOF

' If the pagedata is a header, print it...else, go to the paragraph
section
IF ors.fields("header") = true THEN
BuildHeader(ors.fields("pagedata"))
ELSE
'Image
IF ors.fields("image") <> "" THEN
%>
<IMG SRC="/<%=ors.fields("image")%>" ALIGN=<
%=ors.fields("imagealign")%>>

<img alt="" src="spacer.gif" height="0" align="left" width="1">
<%
END IF
'Check to see if this is a title
vt = ors.fields("title")
IF vt <> "" THEN
%>
<B><%=vt%></B><BR>
<%
END IF
Response.write(ors.fields("pagedata") & "<BR><BR>")
if ors.fields("imgcap") <> "" then
%>
<div style="width:100%; text-align:<
%=ors.fields("imagealign")%>"><table align="<%=ors.fields("imagealign")
%>"><tr><td align="left" style="font-size:7pt;"><%=ors.fields("imgcap")
%></td></tr></table></div>
<%
end if
END IF 'IF header

ors.MoveNext
loop
END IF

oRS.Close
set oRS = nothing
oC.close
Set oC = nothing

 

[Bob Barrows [MVP]]

Sorry for the lack of detail - I thought it might be a common
weirdness with an upgrade like this. Guess not. The code ran like a
Swiss watch against a 2000 server (and still does). It is very long,
and well, not all that well written. Sadly, I need this to work
against 2005 without re-coding the whole thing. Here's a sample that
contains relevant parts:

connectionstring = "Driver={SQL Native

Client};Server=x.x.x.x;Database=distweb;Uid=xxx;Pwd=xxx;MARS_Connection=
yes;"

OK, so you are using ODBC. Your first step should be to switch to the
native OLE DB provider (from www.connectionstrings.com):
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUserna
me;Pwd=myPassword;

Set oC = Server.CreateObject("ADODB.Connection")
oC.Open connectionstring
Set oRS = Server.CreateObject("ADODB.Recordset")
sqlStr = "SELECT * FROM pagedata WHERE pageid = " & int(pageid) & "

From the link I provided, you should see that it's a bad idea to use
selstar ... especially when you are planning to use only a few of the
columns from the table. do yourself (and whoever has to maintain this
code after you) a favor and replace the * with the list of columns you
are retrieving from the table ... even if you want all the columns that
are currently in the table. Who's to say that somebody isn't going to
come along at some point and add another column to the table?
<snip>
Without details such as column datatypes, I have to stick with my
original guess. Again, see http://www.aspfaq.com/show.asp?id=2188