Get Difference of two dates retrieve from MS Access database using visual basic-CodePudding

Dim da As New OleDb.OleDbDataAdapter("SELECT ITemID, ItemName, ItemDescription, ItemQuantity, ItemBorrowedDate, ItemReturnDate FROM BorrowedItem order by ID DESC", conn)
Dim dt As New DataTable
da.Fill(dt)

OverDueList.DataSource = dt.DefaultView

Dim ItemReturenedDate As New Date
Dim DateToday As Integer
Dim DateDiff As New Date

ItemReturenedDate = dt.Rows(0)("ItemReturnDate")

DateDiff = DateDiff(DateInterval.Day, ItemReturenedDate, DateTimePicker1.Value)`

I tried that code to generate output but my knowledge was not that good. i need help, it could be a great help if someone would notice it

CodePudding user response：

Try something like this:

Dim ItemReturenedDate As Date
Dim Days As Long

ItemReturenedDate = dt.Rows(0)("ItemReturnDate")
Days = DateTimePicker1.Value.Subtract(ItemReturenedDate).TotalDays

CodePudding user response：

And if you looking to send all rows to say a grid?

then this:

Dim strSQL As String =
  "Select ITemID, ItemName, ItemDescription, ItemQuantity, ItemBorrowedDate, ItemReturnDate,
  (ItemReturnDate - ItemBorroedDate) as MyDays
  FROM BorrowedItem order by ID DESC"

dim dt as DataTable = MyRst(strSQL)

So, you can return MyDays for each row, and then say send the data table to a data grid view.

And I get VERY tired very fast typing over confection string and command objects, so you can use this routine (over and over).

Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable
    Using conn As New OleDbConnection(My.Settings.AccessDB)
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function