is my query right? need help...there is always an error

Question

I'm doing some practice in VB 2012, I've already connect it to the database in microsoft access 2013. When I run it there's an error in the Select Trans_date from [Transaction] where Trans_date = SYSDATE

here's my code...

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports System.Data.DataTable

Public Class Form1

Dim provider As String
Dim dataFile As String
Dim connString As String
Dim myConnection As OleDbConnection = New OleDbConnection
Dim ds As DataSet = New DataSet
Dim da As OleDbDataAdapter
Dim tables As DataTableCollection = ds.Tables

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    provider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
    dataFile = "C:\Users\hp-2\Documents\Visual Studio 2012\Projects\Delta\Delta.mdb"

    connString = provider & dataFile
    myConnection.ConnectionString = connString
     da = New OleDbDataAdapter("Select Trans_date from [Transaction] where Trans_date = SYSDATE" 
    & t_date.Text & "'", myConnection)
    da.Fill(ds, "Transaction")

    Dim view1 As New DataView(tables(0))
    Dim source1 As New BindingSource()
    source1.DataSource = view1
    showdata.DataSource = view1
    showdata.Refresh()
   End Sub

  Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

  End Sub
  End Class

and here's my error..

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in string in query expression 'Trans_date = SYSDATE''.

Please explain what are your intentions with that query. There is no SYSDATE function in MSAccess. — Steve, Aug 11 '13 at 09:22
aw... my intention is I want to show all the data that are in the Transaction Table to the datagridview where the data is equal to the date today.... — hPys, Aug 11 '13 at 10:45
another thing, what should be the right equivalent of sysdate to put in it? — hPys, Aug 11 '13 at 11:03

score 2 · Accepted Answer · answered Aug 11 '13 at 12:02

2

Try using Date() instead of SYSDATE

answered Aug 11 '13 at 12:02

hmqcnoesy

4,165
3
31
47

Nothing against your answer, it is correct that DATE() could be used in place of SYSDATE, but if possible, I would stay away from VBA functions. – Steve Aug 11 '13 at 12:20
@Steve, that's interesting - I never thought of Date() as a VBA function, I always thought of it as an Access SQL function. – hmqcnoesy Aug 12 '13 at 00:15

score 1 · Answer 2 · edited May 23 '17 at 12:11

You should use a parameterized query. As an example

Dim query = "Select Trans_date from [Transaction] " & _
            "where Trans_date = ?" 
da = New OleDbDataAdapter(query, myConnection)
da.SelectCommand.Parameters.AddWithValue("@p1", DateTime.Today)
da.Fill(ds, "Transaction")

Here, the placeholder ? in the query text will be used by the framework, along with the parameter added to the SelectCommand collection, to build the correct query to pass to the underlying database engine.

In OleDb the parameters are not recognized by a specific name (as other net database drivers do) but only by their position in the query text. So the first placeholder (?) is associated to the first parameter (@p1) and its value is formatted and passed to the MSAccess engine.

Using parameters to pass text commands is of uttermost importance. You don't have to worry how to format dates, strings and decimal numbers. And (less probable with MSAccess but always a possibility) you don't write code exposed to Sql Injections

is my query right? need help...there is always an error

2 Answers2