ASP and VFP Stored Procedure

[ToddWW]

I have been using Microsoft ASP with VFP v8 for a few years now and it works great. I use ASP's RecordSet object to get information from the database and I have written about 10 stored procedures that I call from ASP to perform various tasks. Everything works great.

Now, for the first time, I am interested in a stored procedure returning a single value. Such as an integer, or .T. or .F.

Here is my ASP Code:

<%
dim Conn
dim strCommand
dim objCommand
dim v1
dim i

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Session("strProvider")

strCommand = "getAlertCount(alertId)"

Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = Conn
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = strCommand

v1 = objCommand.Execute

Set objCommand = Nothing
Conn.Close
Set Conn = Nothing
%>

Here is my stored procedure in VFP

PROCEDURE getAlertCount(alert)
  
  LOCAL alertCount
  alertCount = 0  

  USE Alerttable ORDER alert_id
  IF SEEK(alert)
    SCAN WHILE ALLTRIM(alert_id) == alert
      alertCount = alertCount + 1
    ENDSCAN
  ENDIF
  
  CLOSE ALL
  
  RETURN alertCount

ENDPROC

The procedure runs fine. When I use both examples above, I don't get any errors. But then, I try to display the results back in ASP using the following examples.

Response.Write v1

RETURNS ERROR (Default Property Not Found For This Object)

Thinking that v1 is now an object I try

FOR EACH i IN v1
  Response.Write "Element: "
  Response.Write i
NEXT

That didn't display anything at all so the FOR statement did not even iterate once, but that did not return an error.

Another object call

Response.Write v1(0)

ADO FIELD ERROR (Item cannot be found in the collection)


I know that stored procedures can return values to other VFP calling programs. But I've never tried it in ASP. Remember, I use the above method successfully to execute stored procedures in VFP from ASP. But never returning a value back to ASP until now.

I also tried FUNCTION / ENDFUNC. Same response as PROCEDURE / ENDPROC

Thanks

ToddWW

 

[Dan Freeman]

Been a while since I did any classic ASP but I think Response.Write wants a string.

Try this:

Response.Write Cstr(v1)