Datepart function in SQL query not working with OLEDB connection in VB.NET

Question

I am trying to fetch Date, Month and Year separately from date column in Access Database.

I am using following code for it.

I don't know what is the problem with this, but either it shows me error or no data is returned.

I am new to OLEDB so I don't know if it is possible or not.

Please help.

And please show me alternatives if this way is incorrect.

    conn_string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MHV\Documents\Visual Studio 2012\Projects\UTS\UTS.mdb"

    conn = New OleDbConnection(conn_string)
    conn.Open()

    Grid_string = "SELECT datepart(mm,T_Date) from Transactions"

    Grid_cmd = New OleDbCommand(Grid_string, conn)
    RW_AD = New OleDbDataAdapter(Grid_cmd)
    Grid_DS = New DataSet

    Grid_cmd.Connection = conn
    Grid_cmd.CommandText = Grid_string

    RW_AD.Fill(Grid_DS, "Transactions")

    Grid_cmd.ExecuteNonQuery() 

    DataGridView1.DataSource = Grid_DS.Tables("Transactions").DefaultView

P.S. : Connection and other things are working fine. It only shows me error when I use datepart().

The error which I keep on getting is.-> No value given for one or more required parameters. — user3762349
– user3762349, Commented Jun 29, 2014 at 4:07

Kiran Hegde · Accepted Answer · 2014-06-29 06:04:05Z

1

Can you please try to making datepart interval in quotation?

Grid_string = "SELECT datepart(\"mm\",T_Date) from Transactions"

answered Jun 29, 2014 at 6:04

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

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

Comments

Shikkediel · Accepted Answer · 2016-02-01 15:59:42Z

1

Using ' instead of " will fix the problem:

Grid_string = "SELECT datepart('mm',T_Date) from Transactions"

edited Feb 1, 2016 at 15:59

Shikkediel

5,20516 gold badges50 silver badges80 bronze badges

answered Jan 29, 2016 at 11:34

stefan de cock

111 bronze badge

Comments

loki · Accepted Answer · 2016-09-14 11:06:50Z

0

Try using m instead of mm

Grid_string = "SELECT datepart('m',T_Date) from Transactions"

edited Sep 14, 2016 at 11:06

loki

10.4k7 gold badges63 silver badges86 bronze badges

answered Sep 14, 2016 at 9:44

user6830274

1

Comments

Gianluca Demarinis · Accepted Answer · 2016-09-28 11:57:37Z

0

Data Type returned is Int16. Cast to Int16 or you'll get error

answered Sep 28, 2016 at 11:57

Gianluca Demarinis

2,1973 gold badges18 silver badges23 bronze badges

Collectives™ on Stack Overflow

Datepart function in SQL query not working with OLEDB connection in VB.NET

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related