Results 1 to 4 of 4

Thread: [2005] CAST/CONVERT Function in DataView Rowfilter

  1. #1

    Thread Starter
    Hyperactive Member Jonny1409's Avatar
    Join Date
    Mar 2005
    Posts
    308

    [2005] CAST/CONVERT Function in DataView Rowfilter

    Hello,

    Can anyone help me with the syntax for this please ?

    I have a Datagridview which is populated by data in a dataview.
    I want to filter the dataview on the Date Column using the RowFilter option, so the dates in the column will only be those later than today.

    I don't really know how to do this, and additionally as the Date in SQL Server is held as Char(10) and not a datetime, when I bring the data in I also need to Cast/Convert it first.

    I don't know the syntax for this though, so can anyone please help me ?

    My code is as follows, and it's the syntax for 'xxx' I'm looking for :
    VB Code:
    1. dv = New DataView(ds.Tables(0))
    2. dv.RowFilter = xxx
    3. dgvDays.DataSource = dv
    Last edited by Jonny1409; Jan 25th, 2007 at 05:12 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [2005] CAST/CONVERT Function in DataView Rowfilter

    Assuming that your dates are stored in the format "MM/dd/yyyy" then your exp0ression would be:
    VB Code:
    1. String.Format("Convert([Date], 'System.DateTime') > {0:M/dd/yyyy}", Date.Today)
    Also, as you're using VB 2005 I'd not recommend creating a DataView and binding it to the grid. I'd stick with the recommended binding method and bind your DataTable to a BindingSource and bind that to the grid, then set the Filter property of the BindingSource.

  3. #3

    Thread Starter
    Hyperactive Member Jonny1409's Avatar
    Join Date
    Mar 2005
    Posts
    308

    Re: [2005] CAST/CONVERT Function in DataView Rowfilter

    Thanks jmcilhinney,

    My Date format is dd/MM/yy so I've changed the code to :

    VB Code:
    1. String.Format("Convert(myDate, 'System.DateTime') > {dd/MM/yyyy}", Date.Today)

    However, I now get an error saying : "Input String was not in correct format"
    Do you know what this is referring to ?

    Is it something to do with the following part of your code, as I don't know what this means :

    "0:M"

    Cheers.

    PS - I'm not really sure why we are using Dataviews in this way, but I'll certainly look into your advice - thanks for that.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [2005] CAST/CONVERT Function in DataView Rowfilter

    It is simply a bad idea to store data in columns of inappropriate types. SQL date literals must ALWAYS be in the M/dd/yyyy format so that change you made is simply illegal. You've also removed the format place holder so today's date won't be inserted at all. Your big problem though is that your field values are stored in an incompatible format so they simply cannot be converted into dates using the limited functionality available to you in the RowFilter property. Only strings in the M/dd/yyyy format can be converted so you're out of luck. Dates should be stored in date columns, plain and simple.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width