开发者

External database path as parameter for parametrized query to Access

开发者 https://www.devze.com 2023-03-12 05:06 出处:网络
I\'m writing small VB.Net app which should build reports based on data gathered from some external MDB-files (Access 2007). It was planned that this app will use parametrized SQL queries to collect da

I'm writing small VB.Net app which should build reports based on data gathered from some external MDB-files (Access 2007). It was planned that this app will use parametrized SQL queries to collect data. One of the parameters for these queries is path to the external MDB-file.

Here goes sample code:

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
                                & "Data Source=C:\Temp\Temp.mdb;")
conn.Open()

Dim cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * INTO Trend FROM TI IN '?' WHERE TI.Id=?;"
With cmd.Parameters
    .Add("@p1", OleDbTy开发者_运维问答pe.VarChar).Value = "C:\Temp\Source.mdb"
    .Add("@p2", OleDbType.Integer).Value = 5
End With

cmd.ExecuteNonQuery()
conn.Close()

Looks simple but it doesn't works. After launch my app throws following exception - System.Data.OleDb.OleDbException: Disk or network error.

Have spent a whole day to make it work with no success. What have I done wrong?


This is a comment that others have suggested is the answer to the question:

Nothing in an Access/Jet/ACE FROM clause is parameterizable (unless it's inside a subquery, of course).

With Access/Jet/ACE your only choice is to use some other method to write the FROM clause on-the-fly.

0

精彩评论

暂无评论...
验证码 换一张
取 消