Trouble getting a returned value (string) from Stored Procedure.

[jbailey268]

To being with; there exists a Stored Procedure in a Visual FoxPro Database: I have had success retrieving data using OLEdb.
Now the procedure is not a query per se; the procedure is very string intensive and returns the credentials of a Dept Head with License Number information etc. It does not return an interger. Someone said there should be an ExecNonQuery() call.
I modified this from another source but it has issues.
-------------------

Starts as follows
Dim strConnectionStr As String = _
"Driver={Microsoftt Visual FoxPro Driver};Source=DBC;SourceDB=c:\develop\xmatch21\databases\sa_tables;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO;"

Dim objConnection As New OleDbConnection(strConnectionStr)
objConnection.Open()

Dim myCommand As New SqlCommand("store_GetCredentials", objconnection)
(this doesn’t work – it says Value of Type System.Data.OleDB. OleDBConnection can not be converted to System.Data.SqlClient.SqlConnection. (blue squiggle under word objconnection.)

- the rest of these command do not show a visible sytax error – haven’t tried it yet because it gets stuck at this point.

myCommand.CommandType = CommandType.StoredProcedure

'Create a SqlParameter object to hold the output parameter value

Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Char)

'IMPORTANT - must set Direction as ReturnValue

retValParam.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection

myCommand.Parameters.Add(retValParam)
'Call the sproc...
Dim reader As SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim retValParam2 As Char = retValParam.Value

Probably something else is wrong because I can't figure out where to place the 8 parameters the Foxpro Stored Procedure is expecting.

Any help is very appreciated?
Eventually the tables will be ported to SQL but for the short term they will remain in VFP.

Thanks




Gerard Bailey

Senior Programmer

J. Knipper & Company, Inc.

One Healthcare Way

Lakewood, NJ 08701

732-905-7878 x 267



Quality First!



This material is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, please note that you are strictly prohibited from disseminating or distributing this material (other than to the intended recipient) or copying this material. If you have received this communication in error, please notify us immediately by telephone and delete this email from your system. Thank you.

 

[jebenson]

If you are using an OleDbConnection, you need to use and OleDbCommand, not a SqlCommand (which is for SQL Server):

Dim myCommand As New OleDbCommand("store_GetCredentials", objconnection)

The same holds true for your parameters:

Dim retValParam As New OleDbParameter("@RETURN_VALUE", SqlDbType.Char)

and the DataReader:

Dim reader As OleDbDataReader = myCommand.ExecuteReader()



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[jbailey268]

Thanks. I did eventually figure that out. Namely, the OleDb stuff. But now I can't get it to execute.
Trouble at cmd.ExecuteReader

I get the ugly window OleDBException at runtime occurs.
Its error says Variable 'Q0P1' is not found.
I put try catch around it and it just says Variable 'Q0P1'
is not found.

Where did it pull that out of the hat? I have nothing in my program or stored procedure with Q0P1?

My Code:
dReader = cmd.ExecuteReader() --- this fails

Tried cmd.ExecuteNonQuery -- this gave Variable 'Q1P1' does not exist. What gives?

Then, this fails too.
dReader.Close()
...even tho I have Dim dReader As OleDbDataReader ..

Now I should tell you... the stored procedure has nothing to do with databases (except that it resides in one). If you will picture a procedure that turns Farenheit to Centigrade by the age old formula and returns the value (as a character). Since there's no Select, Update, Insert, Delete going on does that make a difference?