在VB.net中使用vb.net查询？

Question

我有一个带CTE的有效SQL查询，我需要在我的vb.net应用程序中使用该查询，但问题是我得到了一个错误，指出：

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"

代码是这样的：

    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim sqlcmd As String = (";WITH q AS 
                                        (
                                         SELECT a.* 
                                         FROM SampleData AS a
                                         WHERE EXISTS (SELECT 1
                                                       FROM SampleData AS b
                                                       WHERE  a.Name = b.Name 
                                                         AND  a.Account= b.Account 
                                                         AND  a.Amount = -b.Amount)
                                         )
                            DELETE FROM q OUTPUT DELETED.* INTO SampleData2")



    If connDB.State = ConnectionState.Closed Then
        connDB.Open()
    End If

    Try

        ds.Tables.Add(dt)
        da = New OleDbDataAdapter(sqlcmd, connDB)
        da.Fill(dt)

对此有什么帮助吗？我找不到任何解决方案。

Answer 1

这是我的函数，使用一个简单的CTE，使用这个函数来测试你的表达式，只需替换其中的CTE，看看，你可能需要修复你的查询。

Public Sub RecursiveData()
    Dim connectionString As String = "Initial Catalog=DBName;Data Source=ServerName;uid=user;password=mypassword;Connection Timeout=50000"

    Dim cteQuery As String = ";WITH   cte " & vbCrLf &
        " AS(SELECT 1 AS n " & vbCrLf &
        " UNION ALL " & vbCrLf &
        " SELECT n + 1 " & vbCrLf &
        " FROM   cte " & vbCrLf &
        " WHERE  n <50 " & vbCrLf &
        " ) " & vbCrLf &
        " SELECT n " & vbCrLf &
        " FROM cte where n <= 5; "

    Using _con As New SqlConnection(connectionString)
        Using _cmd As New SqlCommand(cteQuery, _con)

            _con.Open()

            Using rdr As SqlDataReader = _cmd.ExecuteReader()
                While rdr.Read()
                    Console.WriteLine(rdr.GetValue(0).ToString())
                End While

                rdr.Close()
            End Using

            _con.Close()
        End Using
    End Using

End Sub