title: OleDbcommand: How to use 'In' - clause with parameters?
body: <p>I have an OleDB connection in my web application that is linked to the MS Access DataBase with a couple of search parameters (<code>OrderID</code>, <code>LineNumber</code>) as the Input. This worked fine in the last years.</p>
  
  <p>Now I was asked to enable the search for multiple <code>LineNumbers</code> (e.g.: 2,3,8).
  If I hardcode this it works of course:</p>
  
  <pre><code>and ([Position] in ('2','3','8') or @LINENUM ='')
  </code></pre>
  
  <p>How can I achieve this dynamically with the given input format as a comma separated string?</p>
  
  <p>I was trying to build a string with the <code>LineNumbers</code> outside the OleDBcommand string with C# and then pass the parameter, but it won't work.</p>
  
  <pre><code>and ([Position] in @LineNumbers or @LINENUM ='')
  </code></pre>
  
  <p>I also was trying to build a table with the <code>LineNumbers</code> inside the OleDBcommand like:</p>
  
  <pre><code>DECLARE @LineNumber TABLE (Value INT)
  INSERT INTO @LineNumber VALUES (2)
  INSERT INTO @LineNumber VALUES (3)
  INSERT INTO @LineNumber VALUES (8)
  
  and ([Position] IN (SELECT Value FROM @LineNumber))
  </code></pre>
  
  <p>but then the error </p>
  
  <blockquote>
    <p>"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'" </p>
  </blockquote>
  
  <p>was thrown.</p>
  
  <p>How can I do this?</p>
  
  <hr>
  
  <p>This is what my code looks like:</p>
  
  <pre><code>string SelectOleDb = @"SELECT SalesID, CertificationID, CalibrationDate, Path
                         FROM Zeugnis WHERE (SalesID = @SALESID or @SALESID = '') 
                                 and ([Position] = @LINENUM or @LINENUM ='')
  
                         UNION ALL
  
                         SELECT SalesID, CertificationID, CalibrationDate, Path
                         FROM Zeugnis_2016 WHERE (SalesID = @SALESID or @SALESID = '') 
                                 and ([Position] = @LINENUM or @LINENUM ='')
  
                         UNION ALL
  
                         SELECT SalesID, CertificationID, CalibrationDate, Path
                         FROM Zeugnis_2015 WHERE (SalesID = @SALESID or @SALESID = '') 
                                 and ([Position] = @LINENUM or @LINENUM ='')
                         ORDER BY CalibrationDate, CertificationID";
  
  OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\XXXXXXXX");
  OleDbCommand cmd = new OleDbCommand();
  OleDbDataAdapter da = new OleDbDataAdapter();
  cmd = new OleDbCommand(SelectOleDb, con);
  cmd.CommandType = CommandType.Text;
  cmd.Parameters.AddWithValue("SALESID", SALESID.Text);
  cmd.Parameters.AddWithValue("LINENUM", LINENUM.Text);
  </code></pre>