Tutorial :Classic ASP SQL Query Returns Two Columns Out Of Ten



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 off 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 querys, 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.

        

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


Solution:1

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


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


Solution: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  


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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »