classic asp ado to stored proc drops fields depending on select order
I have to support a classic asp system (grrr) where it is returning nulls from a stored proc depending on the final select order in the proc. I was managing to cope with this by just jiggling the select order until i got one that worked for every field... Unfortunately now it has decided to work differently on different machines.
fairly basic asp:
' ORDER ITEMS
sSQL = "ProcName " & Order_ID
Dim iRowCount : iRowCount = 0
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("ad开发者_JAVA百科min_ConnectionString")
set rs = objConn.execute(sSQL)
if not rs.eof then
' Just doing this set thing to try and stop stupid ADO dropping fields !!
' The final select in the proc decides whether things will appear or disappear...
set iQtyRequired = rs("QuantityRequired")
do until rs.eof
iQtyOutstanding = iQtyRequired - iQtyFulfilled
SQL has a fair amount of processing WITH's, cursor and a final select:
SELECT DispatchDate, ProposedDispatchQty, ...
FROM @OrderItems
If i change the order of the select, different fields will "disappear" from ado - they are still there in .net and sql management studio. I cant get any consistency as to which one causes it, seems to be many, i'll try 20 different combinations, get 20 different sets of blanks.
i guess ado is blowing up somewhere silently inside (date handling perhaps?). Anyone got any good suggestions for getting some consistency? (apart from rewriting it in c#)
Cheers in advance - Shane :)
I've already faced that kind of issue in the past when working with ntext columns. I had to select the ntext column last and that would do the job. Changing your connection string can also help.
Check out this question, quite the same problem as yours : SQL Server text column affects results returned to classic ASP
精彩评论