Classic ASP SQL Query Returns Two Columns Out Of Ten...

Answer 1

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 2

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.

Answer 3

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 4

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