title: Classic ASP SQL Query Returns Two Columns Out Of Ten
body: <p>I'm hoping someone can help with a problem...I don't work in anything related to programming, but we needed some asset tracking pretty badly, so in my spare time (not very much, we average 10hrs days) and with the tools at hand (a 600mhz pile running winXP) i built a little classic ASP site to handle all the tracking and orders.  </p>
  
  <p>Everything was fine until I decided I had some more spare time and decided to upgrade a little.  The whole thing is pretty messy, but worst of its god awful slow.  So i decided to chuck my access db and JET drivers and go with MS SQL Server 2000 Express (don't forget, 600mhz and not much RAM).  I started rebuilding, and everything seemed to be working great (and much faster) until I started getting errors.  I figured out that for some queries, only a couple of the Columns would return any data.  </p>
  
  <p><strong>For Example</strong>:  I have a shopitems table, with gID(uniqueID), iDesc(description), iLength, iQty, colID (cross reference).  I do a query to get everything in a certain catagory: <code>Select * from shopitems where catID=22 order by gID asc</code>.  Then i start stepping through and displaying it all. </p>
  
  <p>The problem is, only gID and colID fields have data, everything else is nothing. I opened up the Web Data Administrator app and run the query in there, and it returns everything nice and fine.  So then i decided there is a bug somewhere, so I write a quick test page and same problem (except this time its gID and iLength that return data).  Its all a mix of data types, iDesc is nText and iLength is int.  I've gone through everything i can find, but i'm down to thinking its something i'm missing in SQL...anyone have any ideas?<br>
  Also, I replicated this on my throw away computer at home, which actually has a modern processor and decent amounts of RAM, so its not the machine at all.</p>
  
  <p>Thanks for the help...i listen to the podcast and it seems like its been server/sql week for a while now, figured i'd ask someone here...</p>
  
  <p>----EDIT----<br>
  Changed the code...a version of this code worked for a hot second, now not so much...if i comment out the </p>
  
  <pre><code>gID=rs1("gID")
  </code></pre>
  
  <p>then i get the value of iDesc, but not otherwise.</p>
  
  <pre><code>&lt;%@ Language=vbscript %&gt;
  </code></pre>
  
  <p>Damnit</p>
  
  <pre><code>Set db1 = Server.CreateObject("ADODB.Connection")
  'db1.Open "Provider=MSDASQL;Driver={SQL Server};Server=Phsion;Database=master;"
  db1.Open "Provider=MSDASQL;DSN=SHOPWEB;"
  'sqltxt="select gID, iLength, iDesc from shopitems where catID=45 order by CAST(idesc as varchar)"
  
  sqltxt="select iDesc, gID from ShopItems order by gID asc"
  set rs1=db1.execute(sqltxt)
  rs1.movefirst
  do until rs1.eof
    gID=rs1("gID")
    'iLength=rs1("iLength")
  
    iDesc=rs1("iDesc")
  
    response.write("&lt;br&gt;gID: " &amp; gid &amp; "&lt;br&gt;")
  
    response.write("&lt;br&gt;iLength: " &amp; iLength &amp; "&lt;br&gt;")
  
    response.write("&lt;br&gt;iDesc: " &amp; iDesc &amp; "&lt;br&gt;")
    rs1.movenext
  loop
  rs1.close
  set rs1=nothing
  db1.close
  set db1=nothing
  </code></pre>