Retrieving data between two dates from Access database using VB.NET

Question

I want to display data from a database in a datagridview. I am getting error on da.Fill(ds, "SAMPLE") with "data type mismatch error". Please see screenshot. My date formats are 'short date' both datetimepicker and database values.

Imports System.Data.OleDb
Public Class Form1
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Recto D Sanchez Jr\Documents\sample.accdb"
Dim MyConn As OleDbConnection
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim tables As DataTableCollection
Dim source1 As New BindingSource

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    MyConn = New OleDbConnection
    MyConn.ConnectionString = connString
    ds = New DataSet
    tables = ds.Tables
    da = New OleDbDataAdapter("Select * from SAMPLE where [LOGDATE] between '" & DateTimePicker1.Text & "' And '" & DateTimePicker2.Text & "'", MyConn)
    da.Fill(ds, "SAMPLE")
    Dim view As New DataView(tables(0))
    source1.DataSource = view
    DataGridView1.DataSource = view
End Sub

End Class

error screenshot

have you tried removing "SAMPLE" ?

Ren Mark Almojera Cerdan
– Ren Mark Almojera Cerdan

2016-06-06 03:48:32 +00:00
Commented Jun 6, 2016 at 3:48 — Ren Mark Almojera Cerdan
– Ren Mark Almojera Cerdan, Commented Jun 6, 2016 at 3:48
Tried that. Same error. :(

jaysonpogi
– jaysonpogi

2016-06-07 01:02:09 +00:00
Commented Jun 7, 2016 at 1:02 — jaysonpogi
– jaysonpogi, Commented Jun 7, 2016 at 1:02

Gord Thompson · Accepted Answer · 2016-06-06 12:22:07Z

1

Since your DateTimePicker controls already deal with proper DateTime values you might have better luck using a parameterized query like this:

MyConn = New OleDbConnection(connString)
Dim cmd As New OleDbCommand("SELECT * FROM [SAMPLE] WHERE [LOGDATE] Between ? And ?", MyConn)
cmd.Parameters.AddWithValue("?", DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("?", DateTimePicker2.Value.Date)
da = New OleDbDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "SAMPLE")

It would save you the trouble of dealing with different date formats, delimiting date literals, and other potential pitfalls.

edited Jun 6, 2016 at 12:22

answered Jun 6, 2016 at 4:52

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jaysonpogi Over a year ago

You nailed it! Thank you very much! :D

F0r3v3r-A-N00b · Accepted Answer · 2016-06-06 04:01:19Z

0

Try

da = New OleDbDataAdapter("Select * from SAMPLE where [LOGDATE] between #" & DateTimePicker1.Value.ToString("MM/dd/yyyy HH:mm:ss") & "# And #" & DateTimePicker2.Value.ToString("MM/dd/yyyy HH:mm:ss") & "#", MyConn)

answered Jun 6, 2016 at 4:01

F0r3v3r-A-N00b

3,0234 gold badges31 silver badges40 bronze badges

Collectives™ on Stack Overflow

Retrieving data between two dates from Access database using VB.NET

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related