Click here to Skip to main content
15,666,947 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
Hey Guys,
So here is my issue. I want to take data from a csv file and populate it into a Datagridview in my program via OLE using 2010 in the .net 3.5 framework. Sounds easy, right?
Well, The text file I have also has unwanted data I want to filter out, a simple where clause should work, i thought. When I don't add the WHERE clause, it all works fine (the text file goes into the datagridview smoothly) but when i try to run the WHERE clause (Simple clause WHERE Date = '2011-04-11' )on csv file through the OLE connection, I get errors.

1 - does anyone know if what i am trying to do is possible?
2 - What is the proper syntax for adding a WHERE clause to an ole SQL string that querys a text file?

Private Function EditingConnectionString(ByVal file As String)
        EditingConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= " & Filepath_Parse(file, 0, "\") & ";" & _
            "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""

    End Function

private sub gettextdata
      ' Retrieve the text data
        Dim objConnection As OleDbConnection
        objConnection = New OleDbConnection(EditingConnectionString(txtFilePath.Text))

        Dim objAdapter As OleDbDataAdapter
        Dim objDataSet As New DataSet
            objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\"), objConnection) '
'---- The above line is where i want to add my WHERE syntax.  Already tried WHERE Date > '4/11/2011' , WHERE Date > "4/11/2011", WHERE Date > #4/11/2011# ----            
Filepath_Parse(OpenFileDialog1.FileName, 1, "\"))
            objAdapter.Fill(objDataSet, "Insitu")
            DataGridView1.DataSource = objDataSet
            DataGridView1.DataMember = "Insitu"

        Catch ex As Exception
            MsgBox("Did You use the correct Column names? " & vbCrLf & ex.Message.ToString)
        End Try
    End Sub
Updated 2-May-11 7:09am
HimanshuJoshi 29-Apr-11 19:41pm    
Edited to add pre tags around code.
R. Hoffmann 29-Apr-11 19:45pm    
Can you post the errors you're getting?

It's certainly possible to have a WHERE clause in the SELECT being used as the adapter's SelectCommand (see, the Examples section).

Perhaps the dates are not in the correct format, and using placeholders and providing your dates as DateTime objects (thereby allowing the system to take care of the format) would solve the issue.
OriginalGriff 30-Apr-11 2:21am    
It would help to have the following:
Error message if any, and the line it is complaining on (if any)
Header row from your CSV file
A few sample rows from your CSV file.
reseburg 2-May-11 13:12pm    
Hey guys,
thanks for the responses.

I tried using a datetime object for my WHERE clause, I still got the error.

This is the extent of the error message i am getting:
"Syntax Error in WHERE Clause"

Below are a few lines from my csv data file

12/10/2010 11:00,1.718,11.483
12/10/2010 11:15,1.69,11.511
12/10/2010 11:30,1.658,11.539
12/10/2010 11:45,1.631,11.592
12/10/2010 12:00,1.663,11.645
12/10/2010 12:15,1.721,11.661
Umair Feroze 2-May-11 13:49pm    
I believe by replacing the following line:

objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\"), objConnection)


objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\") & " WHERE Sample_Date = '2011-01-01'", objConnection)

will resolve your issue.

If not, please verify the selectcommand property of objAdapter and paste here

WHERE Date > 
wouldn't work, as "Date" doesn't represent one of your column-names.
WHERE Sample_Date > 
might work.

Adding a schema[^] might help too.
Share this answer
The way I'd do it is *without* any of that sql stuff...

0) Create an object that holds the data:

public class SampleItem
    public DateTime SampleDate { get; set; }
    public decimal DepthFeet { get; set; }
    public decimal WaterTemp { get; set; }
    public SampleItem() 
        SampleDate = new DateTime(0);
        DepthFeet  = 0M;
        WaterTemp  = 0M;
    public SampleItem(dateTime date, decimal depth, decimal temp) 
        SampleDate = date;
        DepthFeet  = depth;
        WaterTemp  = temp;
    public SampleItem(string data) 
        string[] parts = data.Split(',');
        SampleDate = DateTime.Parse(parts[0]);
        DepthFeet  = decimal.Parse(parts[1]);
        WaterTemp  = decimal.Parse(parts[2]);

1) Read the text file one line at a time, and instaniate a new SampleItem with the string

You might also want to take some precautions to ensure that the data is otherwise valid as well (in the constructors).
Share this answer
Try this syntax also WHERE [Sample_Date] > #01/25/2011# . It works and specific for Access DB.
Sergey Chepurin.
Share this answer
Sergey's comment was the key - Try this syntax also WHERE [Sample_Date] > #01/25/2011#
The syntax i was looking for was the "#" sign. thanks everyone for your help.
Share this answer
Sergey Chepurin 4-May-11 17:42pm    
I double-checked it and the correct syntax is: WHERE Month(Sample_Date) = 4 And Sample_Date > #04/21/2011#
- outputs dates only in April after 21-th.
The previous example will output data after certain date in every month.
Sergey Chepurin.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900