[2005] Comparing Dates

[savior14]

i think there's something wrong with my code..

       txtPayee.Visible = False
        txtAmount.Visible = False
        dtpDate.Visible = False
        objDataAdapter.SelectCommand = New SqlCommand()
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.SelectCommand.CommandText = _
        "SELECT MGL_Payee, MGL_TotalAmount, MGL_ReferenceDate FROM tbl_MGLTransaction"
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        Dim objDataSet As New DataSet()
        objConnection.Open()
        objDataAdapter.Fill(objDataSet, "tbl_MGLTransaction")
        objDataView = New DataView(objDataSet.Tables("tbl_MGLTransaction"))

        'Clear then Bind
        txtPayee.DataBindings.Clear()
        txtAmount.DataBindings.Clear()
        dtpDate.DataBindings.Clear()

        txtPayee.DataBindings.Add("Text", objDataView, "MGL_Payee")
        txtAmount.DataBindings.Add("Text", objDataView, "MGL_TotalAmount")
        dtpDate.DataBindings.Add("Value", objDataView, "MGL_ReferenceDate")
        objConnection.Close()

        Dim tempDate As DateTime = dtpDate.Value
        Dim dateThirty As Date = tempDate.AddDays(30)
        Dim dateSixty As Date = tempDate.AddDays(60)
        Dim dateNinety As Date = tempDate.AddDays(90)

        Dim oneToThirtyAmount As New Integer
        Dim thirtyToSixtyAmount As New Integer
        Dim sixtyToNinetyAmount As New Integer
        Dim moreThanNinetyAmount As New Integer

        If Now >= tempDate And Now <= dateThirty Then

            oneToThirtyAmount = txtAmount.Text
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = 0

        ElseIf Now > dateThirty And Now <= dateSixty Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = txtAmount.Text
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = 0

        ElseIf Now > dateSixty And Now <= dateNinety Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = txtAmount.Text
            moreThanNinetyAmount = 0

        ElseIf Now > dateNinety Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = txtAmount.Text

        End If

        'Bind
        'Insert To Table
        'Retrieve To Table
        'Bind To DataGrid
        'Delete Record From Table

        Dim objConnection2 As SqlConnection = New _
        SqlConnection("Data Source=MORETHANENOUGH\SQLEXPRESS;Initial Catalog=accountingdb;Integrated Security=True")
        Dim objCommand2 As SqlCommand = New SqlCommand()
        objCommand2.Connection = objConnection2
        objCommand2.CommandText = "INSERT INTO tbl_ARAging " & _
        "(Payee, TotalAmount, ReferDate, OneToThirty, ThirtyToSixty, SixtyToNinety, MoreThanNinety) " & _
        "VALUES(@Payee, @TotalAmount, @ReferDate, @OneToThirty, @ThirtyToSixty, @SixtyToNinety, @MoreThanNinety)"
        objCommand2.Parameters.AddWithValue("@Payee", txtPayee.Text)
        objCommand2.Parameters.AddWithValue("@TotalAmount", txtAmount.Text)
        objCommand2.Parameters.AddWithValue("@ReferDate", dtpDate.Value)
        objCommand2.Parameters.AddWithValue("@OneToThirty", oneToThirtyAmount.ToString)
        objCommand2.Parameters.AddWithValue("@ThirtyToSixty", thirtyToSixtyAmount.ToString)
        objCommand2.Parameters.AddWithValue("@SixtyToNinety", sixtyToNinetyAmount.ToString)
        objCommand2.Parameters.AddWithValue("@MoreThanNinety", moreThanNinetyAmount.ToString)
        objConnection2.Open()
        objCommand2.ExecuteNonQuery()
        objConnection2.Close()

        Dim objConnection3 As New SqlConnection _
      ("Data Source=MORETHANENOUGH\SQLEXPRESS;Initial Catalog=accountingdb;Integrated Security=True")
        objDataAdapter3.SelectCommand = New SqlCommand()
        objDataAdapter3.SelectCommand.Connection = objConnection3
        objDataAdapter3.SelectCommand.CommandText = ("select Payee, TotalAmount, ReferDate, OneToThirty, ThirtyToSixty, SixtyToNinety, MoreThanNinety FROM tbl_ARAging")

        objDataAdapter3.SelectCommand.CommandType = CommandType.Text

        Dim objDataSet3 As New DataSet()
        objConnection3.Open()
        objDataAdapter3.Fill(objDataSet3, "tbl_ARAging")
        objDataView3 = New DataView(objDataSet3.Tables("tbl_ARAging"))

        grdAuthorTitles.DataSource = objDataSet3
        grdAuthorTitles.DataMember = "tbl_ARAging"

        grdAuthorTitles.Columns(0).HeaderText = "Payee"
        grdAuthorTitles.Columns(1).HeaderText = "TotalAmount"
        grdAuthorTitles.Columns(2).HeaderText = "Reference Date"
        grdAuthorTitles.Columns(3).HeaderText = "1 - 30"
        grdAuthorTitles.Columns(4).HeaderText = "31 - 60"
        grdAuthorTitles.Columns(5).HeaderText = "61 - 90"
        grdAuthorTitles.Columns(6).HeaderText = ">90"

        objConnection3.Close()
        Dim objConnection4 As New SqlConnection _
      ("Data Source=MORETHANENOUGH\SQLEXPRESS;Initial Catalog=accountingdb;Integrated Security=True")
        Dim objCommand4 As SqlCommand = New SqlCommand()
        objCommand4.Connection = objConnection4
        objCommand4.CommandText = ("DELETE FROM tbl_ARAging")

        objConnection4.Open()
        objCommand4.ExecuteNonQuery()
        objConnection4.Close()

1st question:
the value i always get in oneToThirtyAmount , oneToThirtyAmount , sixtyToNinetyAmount , moreThanNinetyAmount is always 0..
editted: solved this one too.. tnx to sir .paul. .. below is the solution..

2nd question:
why is it? is there a way not to affect the value with setting its visible property to false? i dont want those 3 boxes to appear.. ive done that because i dont know how to bind a data, retrieve from a table, to a variable only without binding it to a textbox/dtp/etc..
editted: solved this one.. setting its visible property in code and not in the design view should do the trick (TextBox.Visible = False)

3rd question: <---- not yet solved T_T

' INSERT TO TABLE

' RETRIEVE TO TABLE THEN BIND TO DATAGRIDVIEW

how can i bind values straight to a datagridview..? as you can see in my code, before binding it to a datagridview, i insert it then retrieve to table the values i want to bind in my datagridview..

4th question: <---- not yet solved too T_T

before, this are my records in tbl_MGLTransaction (Figure 1)
now, ive added a new record.. (figure 2)
when i run code, the output is the same as before.. (figure 3)
how can i make it to look like figure 4 ?


someone pls check my code.. tnx..

[.paul.]

have another look at your code

If dtpDate.Value >= dateThirty And dtpDate.Value <= dateThirty Then

that'll never be executed. it can't be >= and <= at the same time unless its =

[savior14]

have another look at your code

If dtpDate.Value >= dateThirty And dtpDate.Value <= dateThirty Then

that'll never be executed. it can't be >= and <= at the same time unless its =

still the same output sir..

changed this one:
If dtpDate.Value >= dateThirty And dtpDate.Value <= dateThirty

to:
If dtpDate.Value = dateThirty And dtpDate.Value <= dateThirty

also the rest..

[.paul.]

try this

Dim tempDate As DateTime = dtpDate.Value
Dim dateThirty As Date = tempDate.AddDays(30)
Dim dateSixty As Date = tempDate.AddDays(60)
Dim dateNinety As Date = tempDate.AddDays(90)
 
Dim oneToThirtyAmount As New Integer
Dim thirtyToSixtyAmount As New Integer
Dim sixtyToNinetyAmount As New Integer
Dim moreThanNinetyAmount As New Integer
 
If dtpDate.Value >= tempDate And dtpDate.Value <= dateThirty Then
   oneToThirtyAmount = txtAmount.Text
   thirtyToSixtyAmount = 0
   sixtyToNinetyAmount = 0
   moreThanNinetyAmount = 0
 
ElseIf dtpDate.Value > dateThirty And dtpDate.Value <= dateSixty Then
   oneToThirtyAmount = 0
   thirtyToSixtyAmount = txtAmount.Text
   sixtyToNinetyAmount = 0
   moreThanNinetyAmount = 0
 
ElseIf dtpDate.Value > dateSixty And dtpDate.Value <= dateNinety Then
   oneToThirtyAmount = 0
   thirtyToSixtyAmount = 0
   sixtyToNinetyAmount = txtAmount.Text
   moreThanNinetyAmount = 0
 
ElseIf dtpDate.Value > dateNinety Then
   oneToThirtyAmount = 0
   thirtyToSixtyAmount = 0
   sixtyToNinetyAmount = 0
   moreThanNinetyAmount = txtAmount.Text
 
End If

[.paul.]

but, i just noticed

Dim tempDate As DateTime = dtpDate.Value

if dateThirty = dtpDate.Value + 30 days & dateSixty = dtpDate.Value + 60 then your if statements are incorrect. dtpDate.Value is never going to be anything but tempDate

[savior14]

what ive done is this..

        Dim tempDate As DateTime = dtpDate.Value
        Dim dateThirty As Date = tempDate.AddDays(30)
        Dim dateSixty As Date = tempDate.AddDays(60)
        Dim dateNinety As Date = tempDate.AddDays(90)

        Dim oneToThirtyAmount As New Integer
        Dim thirtyToSixtyAmount As New Integer
        Dim sixtyToNinetyAmount As New Integer
        Dim moreThanNinetyAmount As New Integer

        If tempDate <= dateThirty Then
            oneToThirtyAmount = txtAmount.Text
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = 0

        ElseIf tempDate > dateThirty And tempDate <= dateSixty Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = txtAmount.Text
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = 0

        ElseIf tempDate > dateSixty And tempDate <= dateNinety Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = txtAmount.Text
            moreThanNinetyAmount = 0

        ElseIf tempDate > dateNinety Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = txtAmount.Text

        End If

it seems the same to your code.. here is the ouput..

seems like there's something wrong with the code..(but there's a little improvement lol xD)

[.paul.]

Dim tempDate As DateTime = dtpDate.Value
Dim dateThirty As Date = tempDate.AddDays(30)

tempDate is never going to be greater than dateThirty because dateThirty = tempDate.AddDays(30)

[savior14]

yeah right!! i got your point!! tnx to that..(currently thinking what to replace)
*swt* silly me.. hmmm.. what must be.. uhmm, errrr, a little help sir

[.paul.]

if you want to know if 'now' is between tempDate + dateThirty

If now >= tempDate and now <= dateThirty Then

[savior14]

if you want to know if 'now' is between tempDate + dateThirty

If now >= tempDate and now <= dateThirty Then

you know what? THANK YOU!! i mean THANK YOU THANK YOU!!! that solves my problem!!! tnx again!!(rated you lol )

[savior14]

Here's the updated code.

        txtPayee.Visible = False
        txtAmount.Visible = False
        dtpDate.Visible = False
        objDataAdapter.SelectCommand = New SqlCommand()
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.SelectCommand.CommandText = _
        "SELECT MGL_Payee, MGL_TotalAmount, MGL_ReferenceDate FROM tbl_MGLTransaction"
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        Dim objDataSet As New DataSet()
        objConnection.Open()
        objDataAdapter.Fill(objDataSet, "tbl_MGLTransaction")
        objDataView = New DataView(objDataSet.Tables("tbl_MGLTransaction"))

        'Clear then Bind
        txtPayee.DataBindings.Clear()
        txtAmount.DataBindings.Clear()
        dtpDate.DataBindings.Clear()

        txtPayee.DataBindings.Add("Text", objDataView, "MGL_Payee")
        txtAmount.DataBindings.Add("Text", objDataView, "MGL_TotalAmount")
        dtpDate.DataBindings.Add("Value", objDataView, "MGL_ReferenceDate")
        objConnection.Close()

        Dim tempDate As DateTime = dtpDate.Value
        Dim dateThirty As Date = tempDate.AddDays(30)
        Dim dateSixty As Date = tempDate.AddDays(60)
        Dim dateNinety As Date = tempDate.AddDays(90)

        Dim oneToThirtyAmount As New Integer
        Dim thirtyToSixtyAmount As New Integer
        Dim sixtyToNinetyAmount As New Integer
        Dim moreThanNinetyAmount As New Integer

        If Now >= tempDate And Now <= dateThirty Then

            oneToThirtyAmount = txtAmount.Text
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = 0

        ElseIf Now > dateThirty And Now <= dateSixty Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = txtAmount.Text
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = 0

        ElseIf Now > dateSixty And Now <= dateNinety Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = txtAmount.Text
            moreThanNinetyAmount = 0

        ElseIf Now > dateNinety Then
            oneToThirtyAmount = 0
            thirtyToSixtyAmount = 0
            sixtyToNinetyAmount = 0
            moreThanNinetyAmount = txtAmount.Text

        End If

        'Bind
        'Insert To Table
        'Retrieve To Table
        'Bind To DataGrid
        'Delete Record From Table

        Dim objConnection2 As SqlConnection = New _
        SqlConnection("Data Source=MORETHANENOUGH\SQLEXPRESS;Initial Catalog=accountingdb;Integrated Security=True")
        Dim objCommand2 As SqlCommand = New SqlCommand()
        objCommand2.Connection = objConnection2
        objCommand2.CommandText = "INSERT INTO tbl_ARAging " & _
        "(Payee, TotalAmount, ReferDate, OneToThirty, ThirtyToSixty, SixtyToNinety, MoreThanNinety) " & _
        "VALUES(@Payee, @TotalAmount, @ReferDate, @OneToThirty, @ThirtyToSixty, @SixtyToNinety, @MoreThanNinety)"
        objCommand2.Parameters.AddWithValue("@Payee", txtPayee.Text)
        objCommand2.Parameters.AddWithValue("@TotalAmount", txtAmount.Text)
        objCommand2.Parameters.AddWithValue("@ReferDate", dtpDate.Value)
        objCommand2.Parameters.AddWithValue("@OneToThirty", oneToThirtyAmount.ToString)
        objCommand2.Parameters.AddWithValue("@ThirtyToSixty", thirtyToSixtyAmount.ToString)
        objCommand2.Parameters.AddWithValue("@SixtyToNinety", sixtyToNinetyAmount.ToString)
        objCommand2.Parameters.AddWithValue("@MoreThanNinety", moreThanNinetyAmount.ToString)
        objConnection2.Open()
        objCommand2.ExecuteNonQuery()
        objConnection2.Close()

        Dim objConnection3 As New SqlConnection _
      ("Data Source=MORETHANENOUGH\SQLEXPRESS;Initial Catalog=accountingdb;Integrated Security=True")
        objDataAdapter3.SelectCommand = New SqlCommand()
        objDataAdapter3.SelectCommand.Connection = objConnection3
        objDataAdapter3.SelectCommand.CommandText = ("select Payee, TotalAmount, ReferDate, OneToThirty, ThirtyToSixty, SixtyToNinety, MoreThanNinety FROM tbl_ARAging")

        objDataAdapter3.SelectCommand.CommandType = CommandType.Text

        Dim objDataSet3 As New DataSet()
        objConnection3.Open()
        objDataAdapter3.Fill(objDataSet3, "tbl_ARAging")
        objDataView3 = New DataView(objDataSet3.Tables("tbl_ARAging"))

        grdAuthorTitles.DataSource = objDataSet3
        grdAuthorTitles.DataMember = "tbl_ARAging"

        grdAuthorTitles.Columns(0).HeaderText = "Payee"
        grdAuthorTitles.Columns(1).HeaderText = "TotalAmount"
        grdAuthorTitles.Columns(2).HeaderText = "Reference Date"
        grdAuthorTitles.Columns(3).HeaderText = "1 - 30"
        grdAuthorTitles.Columns(4).HeaderText = "31 - 60"
        grdAuthorTitles.Columns(5).HeaderText = "61 - 90"
        grdAuthorTitles.Columns(6).HeaderText = ">90"

        objConnection3.Close()
        Dim objConnection4 As New SqlConnection _
      ("Data Source=MORETHANENOUGH\SQLEXPRESS;Initial Catalog=accountingdb;Integrated Security=True")
        Dim objCommand4 As SqlCommand = New SqlCommand()
        objCommand4.Connection = objConnection4
        objCommand4.CommandText = ("DELETE FROM tbl_ARAging")

        objConnection4.Open()
        objCommand4.ExecuteNonQuery()
        objConnection4.Close()

last problem..
before, this are my records in tbl_MGLTransaction (Figure 1)
now, ive added a new record.. (figure 2)
when i run code, the output is the same as before.. (figure 3)
how can i make it to look like figure 4 ?

it didnt add my 2nd record to datagridview..

[.paul.]

i'm not sure what you're asking.
i'm not an expert with databases. you could try the database development forum. ask a moderator to move this thread

[savior14]

ooohhh.. i seee.. btw, tnx again... you've been a big help to me hope a moderator would see this..

[savior14]

updated 1st post.. someone pls.. need help.. pls see my 3rd and 4th question.. tnx..