Prash's Blog

Cannot convert lambda expression to type ‘string’ because it is not a delegate type November 7, 2011

Filed under: ADO — prazjain @ 12:34 pm
Tags: , , , , ,

This error happens over and over but only after considerable time when you have forgotten how you fixed it last time.

            foreach (string colName in breakup)
            {
                IEnumerable res = from row in QueryCSVModel.FilteredData.Table
                                  select row[colName];    //error on select : Cannot convert lambda expression to type 'string' because it is not a delegate type
                //do something here
            }

This is the solution specified here  (rewriting here in case it helps you)

  • Add reference to System.Data.DataSetExtensions
  • Add using for System.Data and System.Linq

But this still did not work for me.

What I missed is using AsEnumerable() after DataTable, this code below works

            foreach (string colName in breakup)
            {
                IEnumerable res = from row in QueryCSVModel.FilteredData.Table.AsEnumerable()
                                  select row[colName];
                //do something here
            }
Advertisements
 

How to Select or Project some columns from a Datatable programmatically without SQL

Filed under: ADO — prazjain @ 11:26 am
Tags: , , , , , , , ,

If you want to filter a few rows you a give a conditional expression that will be applied and you get only the resulting rows that satify the conditional expression.

But what if you have around 50 columns and you do not want to see all of them in the result after a select query.

So how do you remove the extra columns? This is how I worked around removing the columns I did not need.

Steps:

  1. Get the rows that satify the filter search criteria
  2. Import them and create a new table
  3. Now all I am doing is, if the the column name is not part of SelectedColumn (string property) then I just remove it from the data table’s column collection. (Of course you cannot remove it in the ‘for’ loop because you cannot edit the collection underlying the enumerator while you are running through the enumerator. So just store it in a temporary collection and remove them later).
  4. Thats it, you have your results!
        private void btnExecute_Click(object sender, RoutedEventArgs e)
        {
            StringBuilder queryBuilder = new StringBuilder();
            //Filter the rows that satisfy the conditional expression
            DataRow[] rows = QueryCSVModel.Data.Table.Select(QueryCSVModel.FilterCriteria);
            //Clone the table to create a new one, and import the eligible rows
            DataTable dt = QueryCSVModel.Data.Table.Clone();
            foreach (DataRow item in rows)
            {
                dt.ImportRow(item);
            }
            List<DataColumn> toRemove = new List<DataColumn>();
            foreach (DataColumn col in dt.Columns)
            {
                //Any column name that is not desired (not in selected columns) is to be removed.
                if (!string.IsNullOrWhiteSpace(QueryCSVModel.SelectedColumns) && !QueryCSVModel.SelectedColumns.Contains(col.ColumnName))
                {
                    toRemove.Add(col);
                }
            }
            // iterate over data column collection and remove the unwanted columns.
            foreach (DataColumn col in toRemove)
	        {
                dt.Columns.Remove(col);
	        }
            //thats it, you have your data with fewer columns
            QueryCSVModel.FilteredData = dt.DefaultView;
        }
     
 

OleDbException is not a valid path Make sure the path name is spelled correctly November 3, 2011

Filed under: ADO — prazjain @ 5:39 pm
Tags: , , , , ,

It has been ages since I did ADO programming. And naturally I hit this common error when loading up my CSV file in memory.

Problem:

      System.Data.OleDb.OleDbException was unhandled

      Message=’C:\CSVTasks\TXT2_RMMTRADE_20101124EOD_20101125_RERUN.CSV’ is not a valid path.

      Make sure that the path name is spelled correctly  and that you are connected to the server on which the file resides.

      Source=Microsoft JET Database Engine ErrorCode=-2147467259

Reason:

The reason this happens is if you give the full file path in the connection string or if you give just the relative file path when creating Data Adapter using select command text.

example:

            // if you have given full file path below, then it will cause this issue
            string connStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + filePathFull + ";Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";
            OleDbConnection conn = new OleDbConnection(connStr);
            conn.Open();

Or if you give just the file name (relative url) when loading file in adapter you will get this exception:

            // if you have given just the file name (not full path) below then it will cause this issue
            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + fileName, conn);
            DataSet ds = new DataSet("QueryCSV");
            adapter.Fill(ds);

Solution:

To avoid this issue

  1. You need to give the full path to the directory when your CSV or Text file resides, in the connection string.
  2. You need to give the full file path in the select command text when creating data adapter.

Code sample

    public class CSVReader
    {
        public static DataView GetData(string fileFullPath,string directoryFullPath)
        {
            // give full path to DIR here
            string connStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + directoryFullPath + ";Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";
            OleDbConnection conn = new OleDbConnection(connStr);
            conn.Open();
            // give full path to the file here
            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + fileFullPath, conn);
            DataSet ds = new DataSet("QueryCSV");
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            conn.Dispose();
            return dt.AsDataView();
        }
    }