none
What is missing from this Select statement RRS feed

  • Question

  • I am connecting to an access table named Table1. I want to fill a datagridview with only data from rows that contain a

    certain year. I need assistance with the select statement. The column which contains the dates is named Date_.

     MyConn = New OleDbConnection
    
            MyConn.ConnectionString = connString
    
            ds = New DataSet
    
            tables = ds.Tables
    
            da = New OleDbDataAdapter("SELECT * FROM Table1 WHERE Date_ >= '01/01/2014' and Date_ <= '12/31/2014' ", MyConn) 
            da.Fill(ds, "Table1")
            Dim view As New DataView(tables(0))
    
            source1.DataSource = view
    
            DataGridView1.DataSource = view




    • Edited by VBShaper Friday, May 19, 2017 1:34 AM
    Friday, May 19, 2017 12:39 AM

Answers

  • Hi VBShaper,

    According to your description, you could need to pay attention to the query, I do one sample that you can refer to.

    select * from Test where Birthday>#1990-01-01#

    Private Sub loaddate() Dim dt As New DataTable() Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb; Persist Security Info=False;" Using conn As New OleDbConnection(strConn) Dim selectSql = "select * from Test where Birthday>#1990-01-01#" Using com As New OleDbCommand(selectSql, conn) Try conn.Open() Dim adapter As New OleDbDataAdapter(com) adapter.Fill(dt) DataGridView1.DataSource = dt conn.Close() Catch ex As Exception MessageBox.Show(ex.ToString()) End Try End Using End Using End Sub

    Here is the Table.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, May 19, 2017 2:39 AM
    Moderator
  • Hi VBShaper,

    According to your description, you could need to pay attention to the query, I do one sample that you can refer to.

    select * from Test where Birthday>#1990-01-01#

    Private Sub loaddate() Dim dt As New DataTable() Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb; Persist Security Info=False;" Using conn As New OleDbConnection(strConn) Dim selectSql = "select * from Test where Birthday>#1990-01-01#" Using com As New OleDbCommand(selectSql, conn) Try conn.Open() Dim adapter As New OleDbDataAdapter(com) adapter.Fill(dt) DataGridView1.DataSource = dt conn.Close() Catch ex As Exception MessageBox.Show(ex.ToString()) End Try End Using End Using End Sub

    Here is the Table.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    I tried the example that you gave me, but it did not work.
    • Marked as answer by VBShaper Friday, May 19, 2017 11:10 AM
    Friday, May 19, 2017 3:27 AM

All replies

  • I am connecting to an access table named Table1. I want to fill a datagridview with only data from rows that contain a certain year.

    You haven't indicated what the field type is for this field, but if it's a date you will need to provide a data that Access can understand (eg, CDATE('2014-01-01 00:00:00') and if it's a string you will have to parse the string.

    Friday, May 19, 2017 1:17 AM
  • I am connecting to an access table named Table1. I want to fill a datagridview with only data from rows that contain a certain year.

    You haven't indicated what the field type is for this field, but if it's a date you will need to provide a data that Access can understand (eg, CDATE('2014-01-01 00:00:00') and if it's a string you will have to parse the string.


    It is a string. Can you give me an example.?
    • Edited by VBShaper Friday, May 19, 2017 1:56 AM
    Friday, May 19, 2017 1:37 AM
  • It is a string. Can you give me an example.?
    That is an access query issue, and not related to VB .Net.  You can consult the format for the WHERE clause at many places, for instance:  https://www.techonthenet.com/access/queries/like.php
    Friday, May 19, 2017 2:25 AM
  • Hi VBShaper,

    According to your description, you could need to pay attention to the query, I do one sample that you can refer to.

    select * from Test where Birthday>#1990-01-01#

    Private Sub loaddate() Dim dt As New DataTable() Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb; Persist Security Info=False;" Using conn As New OleDbConnection(strConn) Dim selectSql = "select * from Test where Birthday>#1990-01-01#" Using com As New OleDbCommand(selectSql, conn) Try conn.Open() Dim adapter As New OleDbDataAdapter(com) adapter.Fill(dt) DataGridView1.DataSource = dt conn.Close() Catch ex As Exception MessageBox.Show(ex.ToString()) End Try End Using End Using End Sub

    Here is the Table.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, May 19, 2017 2:39 AM
    Moderator
  • Hi VBShaper,

    According to your description, you could need to pay attention to the query, I do one sample that you can refer to.

    select * from Test where Birthday>#1990-01-01#

    Private Sub loaddate() Dim dt As New DataTable() Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb; Persist Security Info=False;" Using conn As New OleDbConnection(strConn) Dim selectSql = "select * from Test where Birthday>#1990-01-01#" Using com As New OleDbCommand(selectSql, conn) Try conn.Open() Dim adapter As New OleDbDataAdapter(com) adapter.Fill(dt) DataGridView1.DataSource = dt conn.Close() Catch ex As Exception MessageBox.Show(ex.ToString()) End Try End Using End Using End Sub

    Here is the Table.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    I tried the example that you gave me, but it did not work.
    • Marked as answer by VBShaper Friday, May 19, 2017 11:10 AM
    Friday, May 19, 2017 3:27 AM
  • Date_
    9/9/2015 12:00:00 AM
    9/9/2014 12:00:00 AM

    9/8/2016 12:00:00 AM

    This is what the Date_ column looks like


    • Edited by VBShaper Friday, May 19, 2017 3:47 AM
    Friday, May 19, 2017 3:46 AM
  • I tried the example that you gave me, but it did not work.

    It won't work if the field is not a date field.  Use the reference provided above to handle the field as a string.

    Friday, May 19, 2017 4:18 AM
  • Hi VBShaper,

    What's problem you have when you try my sample, I set Birthday field as Date & Time. And please make sure DataGridView binding no problem.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 19, 2017 4:32 AM
    Moderator
  • Hi VBShaper,

    What's problem you have when you try my sample, I set Birthday field as Date & Time. And please make sure DataGridView binding no problem.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    I changed column property from text to Date/time and it worked. Thank you.
    Friday, May 19, 2017 11:09 AM