经典ASP SQL查询返回两列，十个 [英] Classic ASP SQL Query Returns Two Columns Out Of Ten

问题描述

我希望有人可以用一​​个问题的帮助......我不涉及任何编程工作，但我们需要一些资产跟踪pretty厉害，所以在我的业余时间（不是非常多，我们平均10小时天），用手头的工具（600MHz的一堆运行WinXP）我建立了一个小传统的ASP网站处理所有的跟踪和订单。一切都很好，直到我决定我有一些空闲时间，并决定一点点提升。整个事情是pretty凌乱，但最差的神可怕缓慢。所以我决定认输我的Access数据库和JET驱动程序和MS SQL Server 2000的前preSS去（不要忘了，600MHz的并没有太大的RAM）。我开始重建，一切似乎是伟大的工作（和更快），直到我开始变得错误。我想通了，对一些querys，只有一对夫妇列将返回任何数据。例如：我有一个shopitems表，与GID（唯一ID），iDesc（介绍），iLength，iQty，colID（交叉引用）。我做一个查询以获得在某个产品类别的一切：SELECT * FROM shopitems那里CATID = 22的GID递增秩序。然后，我开始通过加强和显示这一切。问题是，只有GID和colID领域有数据，一切是什么。我打开了Web数据管理应用程序，并在那里运行查询，并返回一切好的和罚款。于是我决定有错误的地方，所以我写了一个快速测试页和同样的问题（但这次其GID和iLength返回的数据）。它的所有数据类型的混合，iDesc是n文字和iLength为int。我已经经历的一切，我能找到了，但我到想它我丢失的东西在SQL ...任何人有什么想法？

另外，我复制这对我扔掉的电脑在家里，这实际上拥有现代化的处理器和体面的RAM，所以它不是机器都没有。

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.

感谢您的帮助...我听播客，现在好像它是的服务器/ SQL星期了一会儿，想我会问这里有人...

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

---- ----编辑
改变了code ...一个版本的code为热第二工作过，现在没有那么多...如果我注释掉

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

然后我得到iDesc的价值，而不是其他。

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

<%@ Language=vbscript %>

该死的结果
＆LT;％

Damnit
<%

设置DB1 =的Server.CreateObject（ADODB.Connection）
db1.Open提供程序= MSDASQL;驱动程序= {SQL服务器};服务器= Phsion;数据库=高手;
db1.Open提供程序= MSDASQL; DSN = SHOPWEB;
'SQLTXT =从shopitems选择GID，iLength，iDesc那里CATID = 45 ORDER BY CAST（idesc为varchar）

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 =由GID递增选择iDesc，GID从ShopItems令
设置RS1 = db1.execute（SQLTXT）
rs1.movefirst
做，直到rs1.eof
  GID = RS1（GID）
  iLength = RS1（iLength）

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）

iDesc=rs1("iDesc")

的Response.Write（结果GID：＆放大器; GID和放大器;结果）

response.write("
gID: " & gid & "
")

的Response.Write（结果iLength：＆放大器; iLength＆安培;结果）

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

的Response.Write（结果iDesc：＆放大器; iDesc＆安培;结果）
  rs1.movenext
循环
rs1.close
设置RS1 =什么
db1.close
设置DB1 =什么
％>

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

推荐答案

这是一个令人困惑的问题。就像提到，一定要打开选项明确，以帮助找到任何变量名的问题。如果您已经使用在错误恢复下一步，那么你可以使用对错误转到0 ，以确保你是不是燮pressing误差

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

这篇关于经典ASP SQL查询返回两列，十个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！