FoxPro database connector - Passing parameterised SQL statements to FoxPro OLEDB

Author

Passing parameterised SQL statements to FoxPro OLEDB

Richard

2006-02-02, 9:29 am

Hi,

Does anyone know if the VisualFoxPro 9.0 OLEDB driver supports
parameters? Trying to pass parameterised queries from the SQL Server
2005 SSIS integrated environment results in the error reported below,
which implies that the driver does support parameters, but that SSIS is
not calling the it properly somehow. Strange, as both the driver and the
environment are written by Microsoft!

Regards,

Richard

-----------------------------------------------

Error MsgBox:

TITLE: Microsoft Visual Studio
------------------------------

Parameters cannot be extracted from the SQL command. The provider might
not help to parse parameter information from the command. In that case,
use the "SQL command from variable" access mode, in which the entire SQL
command is stored in a variable.

------------------------------
ADDITIONAL INFORMATION:

Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Visual FoxPro)

Cindy Winegarden

2006-02-02, 8:25 pm

Hi Richard,

Please be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.

Does this Visual Studio example work for you? If it does and your code still
throws errors, try posting the code so we can try to duplicate the problem.

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1
Sub Main()
Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()

Dim cmd1 As New OleDbCommand( _
"Create Table TestMemo (Field1 M)", cn1)
cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand()
cmd2.Connection = cn1
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "Insert Into TestMemo (Field1) Values (?)"

Dim p1 As New OleDbParameter()
cmd2.Parameters.Add(p1)
cmd2.Parameters(0).Value = "Hello World"
cmd2.ExecuteNonQuery()

Dim da1 As New OleDbDataAdapter( _
"Select * From TestMemo", cn1)
Dim ds1 As New DataSet()
da1.Fill(ds1)
Console.WriteLine(ds1.Tables(0).Rows(0).Item(0).ToString())
Console.ReadLine()

Catch e As Exception
MsgBox(e.ToString())

End Try
End Sub
End Module

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Richard" <rrose@nospaminforms.co.uk> wrote in message
news:%23J54ZYAKGHA.2912@tk2msftngp13.phx.gbl...
> Does anyone know if the VisualFoxPro 9.0 OLEDB driver supports parameters?
> ......

Richard

2006-02-03, 7:24 am

Hi Cindy,

I have the latest drivers, and your code runs perfectly from a raw VS
project, so the problem is not FoxPro per se.

Unfortunately SSIS is a heavily integrated environment, all OLEDB
configuration properties are controlled through dialog boxes, the
properties held as XML, and the final executable deployed in some manner
I can't get to. So I can't post the code it is using.
I'll have a hunt and see what I can find.
I've also posted on the SQL Server SSIS forum on forums.Microsoft.com,
but no-one has replied as yet.

Just to check the obvious-
Am I right in using the normal syntax for the parameter:
"SELECT * FROM tbl_Stuff WHERE DateChanged > ? "

Thanks for your help,

Richard,

Cindy Winegarden wrote:
> Hi Richard,
>
> Please be sure you have the latest FoxPro and Visual FoxPro OLE DB data
> provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
>
> Does this Visual Studio example work for you? If it does and your code still
> throws errors, try posting the code so we can try to duplicate the problem.
>
> Imports System
> Imports System.Data
> Imports System.Data.OleDb
>
> Module Module1
> Sub Main()
> Try
>
> Dim cn1 As New OleDbConnection( _
> "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
> cn1.Open()
>
> Dim cmd1 As New OleDbCommand( _
> "Create Table TestMemo (Field1 M)", cn1)
> cmd1.ExecuteNonQuery()
>
> Dim cmd2 As New OleDbCommand()
> cmd2.Connection = cn1
> cmd2.CommandType = CommandType.Text
> cmd2.CommandText = "Insert Into TestMemo (Field1) Values (?)"
>
> Dim p1 As New OleDbParameter()
> cmd2.Parameters.Add(p1)
> cmd2.Parameters(0).Value = "Hello World"
> cmd2.ExecuteNonQuery()
>
> Dim da1 As New OleDbDataAdapter( _
> "Select * From TestMemo", cn1)
> Dim ds1 As New DataSet()
> da1.Fill(ds1)
> Console.WriteLine(ds1.Tables(0).Rows(0).Item(0).ToString())
> Console.ReadLine()
>
> Catch e As Exception
> MsgBox(e.ToString())
>
> End Try
> End Sub
> End Module
>
>

Cindy Winegarden

2006-02-04, 1:24 pm

Hi Richard,

This works for me:

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()

Dim cmd1 As New OleDbCommand( _
"Create Table Test (Field1 C(10))", cn1)
cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand( _
"Insert Into Test Values ('HelloWorld')", cn1)
cmd2.ExecuteNonQuery()

Dim cmd3 As New OleDb.OleDbCommand( _
"Select * From Test Where Field1 = ?", cn1)
Dim p1 As New OleDbParameter()
p1.Value = "HelloWorld"
cmd3.Parameters.Add(p1)

Dim da1 As New OleDbDataAdapter(cmd
3)
Dim ds1 As New DataSet()
da1.Fill(ds1)
MsgBox(ds1.Tables(0).Rows(0).Item(0).ToString())

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Richard" <rrose@nospaminforms.co.uk> wrote in message
news:uWorntKKGHA.500@TK2MSFTNGP15.phx.gbl...
> Just to check the obvious-
> Am I right in using the normal syntax for the parameter:
> "SELECT * FROM tbl_Stuff WHERE DateChanged > ? "

Richard

2006-02-05, 8:24 pm

Hi Cindy,

Works for me to - which implies there is a BUG in the SQL Server
Integration Services UI, as it clearly does not parse the string in such
a way that the OLEDB driver can pick up the parameter. Reading the SSIS
forums, at least one other chap has seen similar behaviour from the UI,
and that was with an OLECB driver into a SQL database.

I've worked round the problem by creating the entire SQL query as a
string, and passing that to the OLEDB driver in one go. This works fine,
it is just less transparent when someeome else comes to maintain the code.

Thanks for all your help on this one - I'm off to annoy the SQL team ;-)

Best Regards,

Richard

Cindy Winegarden wrote:
> Hi Richard,
>
> This works for me:
>
> Dim cn1 As New OleDbConnection( _
> "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
> cn1.Open()
>
> Dim cmd1 As New OleDbCommand( _
> "Create Table Test (Field1 C(10))", cn1)
> cmd1.ExecuteNonQuery()
>
> Dim cmd2 As New OleDbCommand( _
> "Insert Into Test Values ('HelloWorld')", cn1)
> cmd2.ExecuteNonQuery()
>
> Dim cmd3 As New OleDb.OleDbCommand( _
> "Select * From Test Where Field1 = ?", cn1)
> Dim p1 As New OleDbParameter()
> p1.Value = "HelloWorld"
> cmd3.Parameters.Add(p1)
>
> Dim da1 As New OleDbDataAdapter(cmd
3)
> Dim ds1 As New DataSet()
> da1.Fill(ds1)
> MsgBox(ds1.Tables(0).Rows(0).Item(0).ToString())
>

Cindy Winegarden

2006-02-06, 11:24 am

Hi Richard,

I'm glad it wasn't the FoxPro OLE DB data provider, otherwise I'd be off to
bug the Fox team!

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Richard" <rrose@nospaminforms.co.uk> wrote in message
news:eh%23r9NpKGHA.952@TK2MSFTNGP10.phx.gbl...

> Thanks for all your help on this one - I'm off to annoy the SQL team ;-)

Liska Station

2006-02-09, 9:24 am

For transparency, you could write the whole thing into a function, pass
your parameters to it, and make it look a little neater.
Although, I'm of the school of thought that says if it works, don't fix it.
Plus, there's the added job-security factor. (kidding) ; )

Seriously, if it's a program maintained by a lot of different people,
squirreling things away in functions, classes, and programs is a way to
keep everything neat, as long as each one consistently gives the correct
output.

Cheers!

Richard <rrose@nospaminforms.co.uk> wrote in news:eh#r9NpKGHA.952
@TK2MSFTNGP10.phx.gbl:

> Hi Cindy,
>
> Works for me to - which implies there is a BUG in the SQL Server
> Integration Services UI, as it clearly does not parse the string in such
> a way that the OLEDB driver can pick up the parameter. Reading the SSIS
> forums, at least one other chap has seen similar behaviour from the UI,
> and that was with an OLECB driver into a SQL database.
>
> I've worked round the problem by creating the entire SQL query as a
> string, and passing that to the OLEDB driver in one go. This works fine,
> it is just less transparent when someeome else comes to maintain the
code.
>
> Thanks for all your help on this one - I'm off to annoy the SQL team ;-)
>
>
> Best Regards,
>
> Richard
>
>
>
> Cindy Winegarden wrote:
>

Sponsored Links