How to compare values from database with a current date

Posted on 2006-03-28
Last Modified: 2012-05-05

I have a form with a button that when the user clicks on a button it should check - if a report already exist or not.
If it exists it should show a message telling the user that report already exist or display another message telling the user to enter data for a new report and then inserting default values like 0.

Could someone please looks at my code because I am not sure what I am doing wrong.

Public Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click

        Dim strConnString As String = "server=***;database=***;uid=***;pwd=***"
        Dim objConn As New SqlConnection(strConnString)
        Dim strInsert As New SqlCommand
        Dim TodayDate As DateTime = DateTime.Now

        Dim strSQL As String
        strSQL = "SELECT * FROM StudentsTable WHERE ReportDate >= DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)"

        Dim cmd As New SqlCommand(strSQL, objConn)
        Dim reader As SqlDataReader
        reader = cmd.ExecuteReader

        While (reader.Read())
            If (String.Compare(reader.Itme("ReportDate").ToString(), TodayDate)  = 0) Then  

                MessageBox("Report for this date already exists." & _


            ElseIf (String.Compare(reader.Item("ReportDate").ToString, TodayDate) < 0) Then

                strInsert.CommandText = "Insert StudentsTable(ReportName, ReportDate, ClassA, ClassB, ClassC, CFlag) " & _

                strInsert.Connection = objConn

                MessageBox("Enter data for a new report." & _

            End If
          End While

    End Sub
Question by:avi4
    LVL 12

    Expert Comment

    If (String.Compare(reader.Itme("ReportDate").ToString(), TodayDate)  = 0) Then  

    Typo  ''' Itme ""
    LVL 16

    Accepted Solution

    It seems to me that the StudentsTable contains only one record (or none) record for each ReportDate.  If that is true, your loop may be doing the job right.

    Because you first selected all records with ReportDate >= current date, i.e. records with current ReportDate and future ReportDate

    In the first If, you checked if the ReportDate equals the current date.  If it does, you pop a message but the loop continues.  That means any records with a future ReportDate will get executed in the ElseIf part and you insert a new record for each row with a future ReportDate.

    Perphaps you can try a better way of doing this.

            Dim strSQL As String
            strSQL = String.Format("SELECT ReportName FROM StudentsTable WHERE ReportDate = '{0:dd MMM yyyy}'", DateTime.Today)

            Dim cmd As New System.Data.SqlClient.SqlCommand(strSQL, objConn)
            Dim obj As Object = cmd.ExecuteScalar()
             If (obj = vbNull) Then
                'insert new report here
                'report alreay exist, show your message
            End If

    Hope that helps


    Author Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now