title: Reading data from a CSV file with mixing data types
body: <p>This works as long as every 'item' is numeric OR every item is alpha numeric.  When i have 'items' that are numeric AND alpha numeric it does not fill in the values correctly.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>public void updateInventory()
  {
      try
      {
          if (File.Exists(inventoryUpdateDirectory + "inventoryUpdate.csv"))
          {
              csvReader csv = new csvReader();
              DataTable inventory = csv.read(inventoryUpdateDirectory + "inventoryUpdate.csv");
              //int test = inventory.Rows.Count;
              string sql = "";
  
              foreach (DataRow inventoryItem in inventory.Rows)
              {
                  try
                  {
                      sql = " Update Inventory set OnHand = " + inventoryItem[1] + " WHERE Sku = '" + inventoryItem[0].ToString().Trim() + "'";
                      //executeSQL(sql);
                  }
                  catch { }
              }
  
              File.Delete(inventoryUpdateDirectory + "inventoryUpdate.csv");
          }
          else
          {
              writeToFile("fileDoesntExist", inventoryUpdateDirectory + "error.txt");
          }
      }
      catch { }
  }
  </code></pre>
  
  <p>Here is the file it reads:</p>
  
  <pre><code>ALB001,0
  ALB002,66
  10001,0
  10016,348
  </code></pre>
  
  <p>This will work:</p>
  
  <pre><code>ALB001,0
  ALB002,66
  </code></pre>
  
  <p>This will work:</p>
  
  <pre><code>10001,0
  10016,348
  </code></pre>
  
  <p>This will not work:</p>
  
  <pre><code>ALB001,0
  ALB002,66
  10001,0
  10016,348
  </code></pre>
  
  <p>It fills out the <code>inventoryItem</code> array as an empty <code>{}</code></p>
  
  <pre><code>+       inventoryItem[0]    {}  object {System.DBNull}
  </code></pre>
  
  <p>Which should have the value of <code>ALB001</code></p>
  
  <p>The first 'column' in the CSV should always be treated as a string as it can contain numbers of letters, the second 'column' will always be numbers.</p>
  
  <p>Anyone able to help me figure this out?</p>
  
  <p>I think I just need to edit the sql query to cast them as a string but I'm not sure.</p>
  
  <p>CSV READER EDIT:</p>
  
  <pre><code>namespace CSV
  {
      public class csvReader
      {
          public DataTable read(string strFileName)
          {
              OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=NO;FMT=Delimited\"");
              conn.Open();
              string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
              OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
              DataSet ds = new DataSet("CSV File");
              adapter.Fill(ds);
              return ds.Tables[0];
          }
  
          public DataTable read(string strFileName, bool firstRowHeaders)
          {
              string hdr = "NO";
              if (firstRowHeaders) { hdr = "YES"; }
  
              OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=" + hdr + ";FMT=Delimited\"");
              conn.Open();
              string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
              OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
              DataSet ds = new DataSet("CSV File");
              adapter.Fill(ds);
              return ds.Tables[0];
          }
      }
  }
  </code></pre>