title: Microsoft Access Parameter Query Fails Occasionally
body: <p>I have a query that, basically, says this:</p>
  
  <pre><code>SELECT DISTINCT [DB_ID]
  FROM [TableX]
  WHERE ([ForeignKey]=@ForeignKey);
  </code></pre>
  
  <p>Once I have this, I return the first DB_ID (there should be only one).</p>
  
  <p>The routine I've written for calling this is in C# so that I can get the database id for any table name, no matter what the DB_ID is named.</p>
  
  <p>Most of the ForeignKey parameters are Integers, but some are String values. To add the parameter value, then, I use</p>
  
  <pre><code>Parameter.AddWithValue(ForeignKeyName, objValue);
  </code></pre>
  
  <p>This routine works fantastic with existing database ids; however, when a ForeignKey is not found, it returns 3 instead of a null.</p>
  
  <p>I realize I could use the brute force technique and simply write two styles of SQL statements (one to accept an Integer, the other to accept a String) and avoid the parameter altogether, but I want to know and understand why this Parameterized routine is not working.</p>
  
  <p>Could someone please explain to me why this does not work?</p>
  
  <pre><code>/// &lt;summary&gt;
  /// Locates the Primary Key Value for a Table using the Table's Foreign Key
  /// &lt;/summary&gt;
  /// &lt;param name="tableName"&gt;Name of the Table to Delete from&lt;/param&gt;
  /// &lt;param name="primaryKey"&gt;Name of the Primary Key in the Table&lt;/param&gt;
  /// &lt;param name="foreignKey"&gt;Name of the Foreign Key in the Table&lt;/param&gt;
  /// &lt;param name="id"&gt;Foreign Key Value to Search for in the Table&lt;/param&gt;
  /// &lt;returns&gt;Primary Key Database ID for this Table Record&lt;/returns&gt;
  List&lt;int&gt; tableSelectId(string tableName, string primaryKey, string foreignKey, object id) {
    string sqlText = string.Format("SELECT DISTINCT [{0}] " +
      "FROM [{1}] WHERE ([{2}]=@id);", primaryKey, tableName, foreignKey);
    DataTable table = new DataTable("IDs");
    OleDbDataAdapter da = new OleDbDataAdapter(sqlText, AccessConnection);
    da.SelectCommand.Parameters.AddWithValue("@id", id);
    try {
      da.Fill(table);
    } catch (OleDbException err) {
      clsLogger.LogException((Exception)err);
    }
    List&lt;int&gt; vals = new List&lt;int&gt;(table.Rows.Count);
    foreach (DataRow row in table.Rows) {
      vals.Add((int)row[0]);
    }
    return vals;
  }
  </code></pre>
  
  <p>eof</p>