jbailey268
Programmer
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.
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.