[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..