Classic ASP SQL query returns two out of ten columns

Question

I hope someone can help with the problem ... I do not work in anything related to programming, but we need some asset tracking pretty bad, so in our free time (not very much, we average 10 hours) and with with the tools at hand (600 MHz winXP crashes) I built a small classic ASP site to handle all the tracking and orders. Everything was fine until I decided that I had free time, and decided to upgrade a bit. All this is pretty dirty, but worst of all, his god is terribly slow. Therefore, I decided to pull out my db and JET access drivers and switch from MS SQL Server 2000 Express (don't forget 600 MHz and not a lot of RAM). I started rebuilding, and everything seemed to work fine (and much faster) until I started getting errors. I found out,that for some queries, only a couple of columns will return any data. For example: I have a shopitems table, with gID (uniqueID), iDesc (description), iLength, iQty, colID (cross-reference). I make a request to get everything in a certain category: "Choose * from shopitems, where catID = 22 order by gID asc". Then I start moving on and showing everything. The problem is that only the gID and colID fields have data, everything else is nothing. I opened the Web Data Administrator application and run the request there, and it returns everything beautiful and accurate. So, I decided there was an error somewhere, so I am writing a short test page and the same problem (except that this time its gID and iLength return data). Its all a mix of data types, iDesc is nText, and iLength is int. I went through everything I can findbut I don’t understand that I am missing something in SQL ... does anyone have any ideas?
In addition, I reproduced this on my ejection computer at home, which actually has a modern processor and a decent amount of RAM, so it is not a machine at all.

Thanks for the help ... I am listening to the podcast, and it seems like it has been a server / sql week for a while, thought that I would ask someone here ...

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

gID=rs1("gID")

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

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

response.write("
iLength: " & iLength & "
")

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

Answer 1

. , , . . "", "" , . 0, , .

. , , . , .

<%
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 2

Option Explicit ( <% % > ), , , Dim variable-name-goes-here , voila, .

Answer 3

, (*), - ? :

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

Answer 4

I don't know if you fixed it, but if not, try changing the order in which you select your columns so that the nText field ends. This is a technical explanation (something related to the maximum length of the write byte, iirc).