OleDBDataReader: No value given for one or more required parameters

Question

In the following script I convert a .xls to a .csv file. This works so far:

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

And now I want to add a filter to remove empty rows: So I replaced this line

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

With that (also working):

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

And another variation (also working):

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

But this crashes:

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

Why is it that I can't use a OR in this whereclause. Isn't that plain SQL?

I'll get the exception ("no value given foe one or more required parameters") on line.

Dim oleExcelReader As OleDb.OleDbDataReader = oleExcelCommand.ExecuteReader

Answer 1

It might seem trivial, but have you tried using parenthesis

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

Also, by reverse logic you would get equivalent query with AND:

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