OleDBDataReader:没有给出一个或多个必需参数的值

Question

在下面的脚本中，我将。xls文件转换为。csv文件。

Const cDelimiter As Char = "~"c 'Delimiter for the new CSV File
    Const fileHasHeader As String = "YES"
    Dim sSheetName As String = String.Empty
    Dim sConnection As String =
    String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR={1};IMEX=1""", fullPathSource, fileHasHeader)
    '
    Try
        Using oleExcelConnection As New OleDb.OleDbConnection(sConnection)
            oleExcelConnection.Open()
            Using dtTablesList = oleExcelConnection.GetSchema("Tables")
                If dtTablesList.Rows.Count > 0 Then
                    sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
                End If
                ' Check if the data sheet has a worksheet(table)
                If sSheetName <> "" Then
                    Dim oleExcelCommand As OleDb.OleDbCommand = oleExcelConnection.CreateCommand()
                    oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
                    oleExcelCommand.CommandType = CommandType.Text
                    Dim oleExcelReader As OleDb.OleDbDataReader = oleExcelCommand.ExecuteReader
                    Dim dtCols As DataTable = oleExcelConnection.GetOleDbSchemaTable()
                    For Each elem In dtCols.Columns
                        Dim string123 As String = (elem.ToString)
                    Next
                    'Dim command As New OleDb.OleDbCommand(insertSQL)
                    'Date.Now.ToString().Replace(":", "-").Replace(".", "-"))
                    Using objStreamWriter As New IO.StreamWriter(String.Format("{0}.csv", fullPathTarget))
                        'Row Count not in use so far
                        Dim countRow As Integer = 0
                        Dim strToRow As String = Nothing
                        'Read the XLS(x) file until end
                        While oleExcelReader.Read()
                            'Empty String for next run
                            Dim rowString As String = ""
                            'Check dynamically for length of rows
                            For i As Integer = 0 To oleExcelReader.FieldCount - 1 Step 1
                                'Here would be the place tó remove any unwanted delimiters within the data
                                'If oleExcelReader.GetValue(i).ToString.Contains(",") Then
                                'End If
                                'Append String to write end the end of each row
                                rowString = rowString & oleExcelReader.GetValue(i).ToString() & cDelimiter
                            Next
                            'Write data to file
                            objStreamWriter.WriteLine(rowString)
                            countRow += countRow
                        End While
                    End Using
                    'End using will close all open connections
                End If
            End Using
            'End using will close all open connections
        End Using
        'End using will close all open connections

现在我想添加一个过滤器来删除空行:所以我替换了这一行

oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"

用这个(也有效):

oleExcelCommand.CommandText = "Select * From [" & sSheetName & "] WHERE [APPLICATION] <> ''"

和另一个变体(也有效):

oleExcelCommand.CommandText = "Select * From [" & sSheetName & "] WHERE [APPLICATION] <> '' AND [USERNAME] <> ''"

但是这个崩溃了:

oleExcelCommand.CommandText = "Select * From [" & sSheetName & "] WHERE [APPLICATION] <> '' OR [USERNAME] <> ''"

为什么我不能在这个where子句中使用OR ?这不是普通的SQL吗?

我将得到异常("一个或多个必需参数没有给定值")。

Dim oleExcelReader As OleDb.OleDbDataReader = oleExcelCommand.ExecuteReader

Answer 1

这可能看起来微不足道，但是您是否尝试过使用括号

oleExcelCommand.CommandText = "Select * From [" & sSheetName & "] WHERE ([APPLICATION] <> '') OR ([USERNAME] <> '')"

同样，通过反向逻辑，您将获得与AND:

等效的查询。

oleExcelCommand.CommandText = "Select * From [" & sSheetName & "] WHERE NOT ([APPLICATION] = '' AND [USERNAME] = '')"