Thanks Naomi, it has no effect on the result. Note that is has no trouble finding another table in the line above.
>I haven't looked too close, but the first thing I would add is NOFILTER in the crsLedger creation.
>
>See, if this would help.
>
>The explanation of why it's important you can see at
>
>
http://berezniker.com/content/pages/visual-foxpro/queries-and-filtered-cursors>
>>>>Say you are running a VFP database via NET.asp. There is a stored procedure that generates a temporary cursor (crsTemp). How would you return this cursor to the webpage and what kind of cleanup is needed in the SP so things aren't left hanging around?
>>>
>>>See SETRESULTSET() function.
>>
>>Thanks Sergey and Naomi,
>>
>>I took your advice and tried to write a NET proc that would use this function but I have not been able to get the routine to run that far yet. I have a SP like this:
>>function PrepareFinancialSummary
>>lparameters lnTipID, lnTipYear
>>
>>set alternate to 'c:\inetpub\wwwroot\nettip\app_data\test.log'
>>set alternate on
>>? "Started"
>>
>>local i, lnOldWorkArea
>>
>>* lnTipID = 9
>>* lnTipYear = 2009
>>lnOldWorkArea = select()
>>
>>select dist fundtype, ;
>> 000000000000.00 as prior, ;
>> 0000000000 as thisyear, ;
>> 0000000000.00 as spent, ;
>> 0000000000.00 as currentbalance, ;
>> 0000000000 as futureprogram, ;
>> 0000000000.00 as total ;
>> from tipdatabase!fundtypes ;
>> order by fundtype ;
>> into cursor crsTemp readwrite
>>? "Step1"
>>
>>select * from tipdatabase!ledger where tip_id = lnTIPID into cursor crsLedger
>>
>>? "Step2"
>>localfield = "lcost_" + transform(lnTipYear)
>>select (localField) from tipdatabase!current where tip_id = lnTipID into array a1
>>replace thisyear with nvl(a1,0) for fundtype = "Local" in crsTemp
>>statefield = "scost_" + transform(lnTipYear)
>>select (stateField) from tipdatabase!current where tip_id = lnTipID into array a1
>>replace thisyear with nvl(a1,0) for fundtype = "State" in crsTemp
>>? "Step3"
>>select * from tipdatabase!current where tip_id = lnTIPID into cursor crsApproved
>>
>>Amt1 = "crsapproved.fed1_" + transform(lnTipYear)
>>Amt2 = "crsapproved.fed2_" + transform(lnTipYear)
>>Amt3 = "crsapproved.fed3_" + transform(lnTipYear)
>>Src1 = "crsapproved.sc1_" + transform(lnTipYear)
>>Src2 = "crsapproved.sc2_" + transform(lnTipYear)
>>Src3 = "crsapproved.sc3_" + transform(lnTipYear)
>>replace thisyear with evaluate(Amt1)*1000 in crsTemp for fundtype = evaluate(Src1)
>>replace thisyear with evaluate(Amt2)*1000 in crsTemp for fundtype = evaluate(Src2)
>>replace thisyear with evaluate(Amt3)*1000 in crsTemp for fundtype = evaluate(Src3)
>>? "Step4"
>>futurelocal = ;
>> evaluate("crsapproved.lcost_" + transform(lntipyear+1)) +;
>> evaluate("crsapproved.lcost_" + transform(lntipyear+2)) +;
>> evaluate("crsapproved.lcost_" + transform(lntipyear+3)) +;
>> evaluate("crsapproved.lcost_" + transform(lntipyear+4))
>>replace futureprogram with futurelocal for fundtype = "Local" in crsTemp
>>futurestate = ;
>> evaluate("crsapproved.scost_" + transform(lntipyear+1)) +;
>> evaluate("crsapproved.scost_" + transform(lntipyear+2)) +;
>> evaluate("crsapproved.scost_" + transform(lntipyear+3)) +;
>> evaluate("crsapproved.scost_" + transform(lntipyear+4))
>>replace futureprogram with futurestate for fundtype = "State" in crsTemp
>>? "Step5"
>>select crsTemp
>>set console off
>>
>>scan
>> select crsLedger
>> sum amount to x for tip_id = lnTipID and fisyear < lntipyear and upper(source) = upper(crsTemp.fundtype)
>> replace prior with x in crsTemp
>>
>> sum amount to x for tip_id = lnTipID and fisyear = lntipyear and upper(source) = upper(crsTemp.fundtype)
>> replace spent with x in crsTemp
>> replace currentbalance with thisyear-spent in crsTemp
>>
>>for i = lnTipyear + 1 to lnTipyear + 4
>> srcfield = "crsapproved.sc1_" + transform(i)
>> amtField = "crsapproved.fed1_" + transform(i)
>> if alltrim(evaluate(srcField)) = alltrim(crsTemp.fundtype)
>> replace futureprogram with futureprogram + evaluate(amtField)*1000 in crsTemp
>> endif
>> srcfield = "crsapproved.sc2_" + transform(i)
>> amtField = "crsapproved.fed2_" + transform(i)
>> if alltrim(evaluate(srcField)) = alltrim(crsTemp.fundtype)
>> replace futureprogram with futureprogram + evaluate(amtField)*1000 in crsTemp
>> endif
>> srcfield = "crsapproved.sc3_" + transform(i)
>> amtField = "crsapproved.fed3_" + transform(i)
>> if alltrim(evaluate(srcField)) = alltrim(crsTemp.fundtype)
>> replace futureprogram with futureprogram + evaluate(amtField)*1000 in crsTemp
>> endif
>>next
>> replace total with prior + thisyear + futureprogram in crsTemp
>>endscan
>>? "Step6"
>>delete for total = 0
>>insert into crsTemp (fundtype) values ("Total")
>>sum crsTemp.prior to x
>>replace prior with x for fundtype = "Total" in crsTemp
>>
>>sum crsTemp.thisyear to x
>>replace thisyear with x for fundtype = "Total" in crsTemp
>>
>>sum crsTemp.spent to x
>>replace spent with x for fundtype = "Total" in crsTemp
>>
>>sum crsTemp.currentbalance to x
>>replace currentbalance with x for fundtype = "Total" in crsTemp
>>
>>sum crsTemp.futureprogram to x
>>replace futureprogram with x for fundtype = "Total" in crsTemp
>>
>>sum crsTemp.total to x
>>replace total with x for fundtype = "Total" in crsTemp
>>
>>set console on
>>go top
>>use in crsapproved
>>use in crsLedger
>>*use in crsTemp
>>select(lnOldWorkArea)
>>? "Step7"
>>set alternate off
>>set alternate to
>>
>>return
>>
>>endfunc
>>>>and a call to run it like this
>>Public Function PrepareFinancialSummary(ByVal lcTIPID As String, ByVal lcTIPYear As String) As String
>> Dim RetVal As String = ""
>> Dim oConn As New OleDbConnection(ConfigurationManager.ConnectionStrings("tip").ConnectionString)
>> Dim command As New OleDbCommand("PrepareFinancialSummary(246,2010)", oConn)
>>
>> command.CommandType = CommandType.StoredProcedure
>>
>> 'command.Parameters.AddWithValue("tipid", lcTIPID)
>> 'command.Parameters.AddWithValue("tipyear", lcTIPYear)
>>
>> oConn.Open()
>> Response.Write(command.CommandText)
>> command.ExecuteNonQuery()
>>
>> 'Dim command2 As New OleDbCommand("SETRESULTSET('crsTemp')", oConn)
>> 'Dim oReader As OleDbDataReader = command2.ExecuteReader
>> 'While oReader.Read
>> ' Response.Write(oReader("fundtype"))
>> ' Response.Write(oReader("prior"))
>> 'End While
>>
>> oConn.Close()
>>
>> Return RetVal
>> End Function>>the SP works well in VFP but produces an error on the first command execute when run from NET. The error says the cursor 'ledger' cannot be found. This puzzles me because the only reference to ledger is in the SELECT right after Step1 AND if I substitute a different table name in that statement, it still tells me 'ledger' cannot be found. So I can't even get to the point of test the setresultset function. Also I can't seem to get the logging to work that I tried to zero in on the error. So, any thoughts on what's going on here AND does the rest of the code (commented out) look like it will work once I get this fixed?
>>Thanks
