How can I get a value from a VFP Stored Procedure remotely from .NET

[jbailey268]

I first tried posting this to the ASP .NET tek-tips and made only small syntactical progress. It didn’t solve my problem. I guess I was writing to .NET minded individuals.

The difficult part was explaining that my stored procedure in the DBC has nothing to do with the data tables contained therein. There is no INSERT, SELECT or UPDATE.

This stored procedure makes an Alpha-Sort of a string. “STRING” becomes “GINRST” and “MAIN STREET” becomes “ AEEIMNRSTT” It is a function that takes a parameter and returns a value. Why so difficult?
Here is the code for the OleDb call:

strConnectionString = _
"Provider=vfpoledb.1;Data Source=c:\develop\databases\sa_tables.dbc;Collating Sequence = machine"
con = New OleDbConnection(strConnectionString)
con.Open()
cmd = New OleDbCommand("scrabblesort", con)
cmd.CommandType = CommandType.StoredProcedure

(The first (and only) parameter is called tStrgVal in the procedure)
cmd.Parameters.Add(New OleDbParameter(“@tStrgVal”, OleDbType.Char, 12))
cmd.Parameters("@tStrgVal ").Value = “Report1”
cmd.Parameters("@tStrgVal ").Direction = ParameterDirection.Input
cmd.Parameters.Add("@retval", OleDbType.Char, 12)
cmd.Parameters("@retval").Direction = ParameterDirection.Output
Dim objDataReader As OleDbDataReader
‘Execute the Stored Procedure
(Everything executes well up to here)
objDataReader = cmd.ExecuteReader()
(This last line triggers an error)
OleDbException was Unhandled
A try catch didn’t help
The error was Variable 'Q0P1' is not found
As a side bar objDataReader = cmd.ExecuteScaler() returns the error message Variable 'Q1P1' is not found. Have no clue where this is from.

I subsequently modified my VFP stored procedure to just simply return “Hello World” yet that didn’t fire either. So it’s nothing in the code.

I am using VFP 8.0 and the .NET Visual Studio 2005

All the help I’ve received on web sites have code similar to this but they all seem to read as if data with rows are returned. A data reader that reads row(0), then row(1) etc. as if I did some kind of Select statement. Like I said up front it’s simply a VFP function that is returning a value. I would have thought this would be a common thing.

From within the VFP Command window:
open DATABASE C:\Develop\Databases\SA_Tables.dbc
? scrabblesort('MIKE')
This shows EIKM (as it should). Works fine here in VFP.
The VFP helps I've seen don't seem to help; the .NET help I've seen doesn't seem to help either. I've compiled the DBS - is there something more I need do from VFP side?
Am I thinking too inside or too outside the box?

Thanks for your assistance. Very Much appreciate any help or suggestion.

 

[Koen Piller]

Hi,
please give the coding for your cmd.ExecuteReader() and did you already use the Codereference tool to locate for QiP1 and lastly I tend to test a stored procedure 1st as a function and only when this test shows no errors the thing is transfered as STP did you do that also?
Regards,
Jockey2

 

[Olaf Doschke]

you don't need to use an oledbcommand and the parameterrs collection, just use

scrabblestring = Con.Execute("Exec createscrabblestring('string')")

Bye, Olaf.

 

[Olaf Doschke]

what you can't do is pass parameters by reference. But it's easy to modify the stored proc to RETURN the result, isn't it?

eg change

local lnResult
sum(1,2,@lnResult)
?lnResult

proc sum(tnSummand1, tnSummand2, tnResult)
   tnResult = tnSummand1+tnSummand2
endproc

to

local lnResult
lnResult = sum(1,2,lnResult)
?lnResult

proc sum(tnSummand1, tnSummand2, tnResult)
   tnResult = tnSummand1+tnSummand2
   Return tnResult
endproc