Classic ASP SQL Query Returns Two Columns Out Of T

Question

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.

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.

For Example: 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: Select * from shopitems where catID=22 order by gID asc. Then i start stepping through and displaying it all.

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?
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.

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...

----EDIT----
Changed the code...a version of this code worked for a hot second, now not so much...if i comment out the

gID=rs1("gID")

then i get the value of iDesc, but not otherwise.

<%@ Language=vbscript %>

Damnit

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("<br>gID: " & gid & "<br>")

  response.write("<br>iLength: " & iLength & "<br>")

  response.write("<br>iDesc: " & iDesc & "<br>")
  rs1.movenext
loop
rs1.close
set rs1=nothing
db1.close
set db1=nothing

Answer 1

I don't know if you've fixed this yet, but if not then you should try changing the order you select your columns so that the nText field goes at the end. There is a technical explanation for this (something to do with the maximum byte-length of a record, iirc).

Answer 2

This is a confusing problem. Like mentioned, be sure to turn on option explicit to help find any variable name issues. If you have used On Error Resume Next then you can use On Error Goto 0 to make sure you aren't suppressing errors

Here is my take. Break this into it's own file if you can for debugging. If my loop produces output we can track things down.

<%
Option Explicit
On Error Goto 0

Dim db1, sqltxt, rs1
Dim gID, iLength, iDesc, arrRS

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("gID: " & gid & "")
'       response.write("iLength: " & iLength & "")
'       response.write("iDesc: " & iDesc & "")
'       rs1.movenext
'   loop
'   rs1.close : set rs1=nothing

sqltxt="select iDesc, gID from ShopItems order by gID asc"
set rs1=db1.execute(sqltxt)
'//Dump the recordset into an array
arrRS = rs1.GetRows()
'//We can close the rs now since we don't need it anymore
rs1.close : set rs1=nothing

'//We are going to basically dump a HTML table that should look like you SQL viewer
Response.Write("<table>")
'//Loop through all the rows
For i = 0 To UBound(arrRS,2)
    Response.Write("<tr>")
    '//Loop through all the columns
    For j = 0 To UBound(arrRS,1)
            '//write out each column in the row
            Response.Write("<td>" & arrRS(j,i) & "</td>")
            '//Look I will be honest, I can't test this so your might have to swap the i and the j to get a full output
    Next
    Response.Write("</tr>")
Next
Response.Write("</table>")

db1.close : set db1=nothing
%>

Answer 3

If you, instead of selecting everything (*), selects the required columns - do you still have the same problem? I.e:

SELECT gID, iDesc, iLength, iQty 
FROM shopitems
WHERE catID = 22

Answer 4

Add Option Explicit at the top (within <% and %>) to make variable declaration required, then declare your variables using Dim variable-name-goes-here for all your variables, and voila, you will see the problem right away.