VERY odd classic ASP problem on 2005 upgrade

Discussion in 'ASP General' started by Elliott Roberts, Mar 27, 2007.

  1. 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
     
    Elliott Roberts, Mar 27, 2007
    #1
    1. Advertisements

  2. 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
     
    Bob Barrows [MVP], Mar 28, 2007
    #2
    1. Advertisements

  3. 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
     
    Elliott Roberts, Mar 28, 2007
    #3
  4. 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;

    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
     
    Bob Barrows [MVP], Mar 28, 2007
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.