Solved

Compare system time to an Access table field

Posted on 2009-04-12
27
209 Views
Last Modified: 2012-05-06
This is an application to remind someone to do things at a certain time of the day. There are sometimes more than one thing the person has to do at a particular time. You can see that from the example data from the field: Time_To_Check which has a cooresponding field called: Item_To_Check.

So, basically I'm  try to alert the user with a popup message box that  it's a certain time and to do required things now.

I'm trying to compare a field in an Access database to the current system time using the control Timer.

I'm getting the error: Object reference not set to an instance of an object
on this line of my code:   Dim sdr As SqlDataReader = cmd.ExecuteReader

I've attached the code below.

Example of data in the Access field called: Time_To_Check
8:00:00
8:00:00
8:00:00
8:30:00
9:00:00
9:00:00
10:00:00

The data type is: Date/Time and the format is:  hh:nn:ss

I'm using seconds so the popup will only happen once...follow my process??

thanks!!




Dim Time_Compare as String = Format(Now, "H:mm:ss")

 Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        Try

            If conn.State = ConnectionState.Open Then

                conn.Close()

            Else

                conn.Open()

            End If

            strSQL1 = "SELECT DISTINCT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = '" & Time_Compare & "'"

            Dim sdr As SqlDataReader = cmd.ExecuteReader

            If (sdr.Read()) Then

                MsgBox("Do checkouts")

            End If

            sdr.Close()

            conn.Close()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub

Open in new window

0
Comment
Question by:rkckjk
  • 15
  • 12
27 Comments
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
You should use parameters because it's easy and you don't have to worry about the format type. But if you want to show only one event for minute you should do >= 8:00:00 and <= 8:00:59.
You just need to change the T-SQL command and include one more parameter.
 
I'm using seconds so the popup will only happen once...follow my process??
 
What interval are you using ?

            strSQL1 = "SELECT DISTINCT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"

            cmd.Parameters.Add("@Time_Compare", SqlDbType.DateTime).Value = Time_Compare

            Dim sdr As SqlDataReader = cmd.ExecuteReader

Open in new window

0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I'm not following where you're going with your response??

First, should I be using the SQL parameter: DISTINCT or UNIQUE?

I'm still getting the same error:
Object reference not set to an instance of an object
on this line of my code:   Dim sdr As SqlDataReader = cmd.ExecuteReader

Should I re-code the whole routine differently?

Basically, I need a messagebox to popup when there is a match from the Access table field of: Time_To_Check with the PC CPU clock.

I formatted the Access field and the PC CPU clock in seconds, so it would only match once. Thinking that matching to the minute would cause it to popup many times.

Am I correct for my logic of doing it this way??

Thanks!!


0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Why DISTINCT or UNIQUE ? You want ALL events for that period.
And cmd variable do have created a new instance on an SqlCommand ?
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
Ok, I see what you mean..I changed the SQL to:

strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"


Attached updated code.

Still getting the error about new instance. I'm not sure how to code that??
 Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick 

If conn.State = ConnectionState.Open Then

            conn.Close()

        Else

            conn.Open()

        End If

        Dim sdr As SqlDataReader = cmd.ExecuteReader

        strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"

        cmd.Parameters.Add("@Time_Compare", SqlDbType.DateTime).Value = Time_Compare

        If (sdr.Read()) Then

            MsgBox("Do checkouts")

        End If

        sdr.Close()

        conn.Close()

     End Sub

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Where you define the variable "cmd" ?
 
Do you use:
Dim (or private) cmd As New SqlCommand
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I defined it after the class.

But now it's giving me a new error on the same statement.

ExecuteReader: Connection property has not been initialized.
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
When you create a new SqlCommand you need to define the connection and the T-SQL.
Check this example http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I'm totally confused as what I need to do. That article didn't clear up anything for me. Can you show me exactly what's missing and how to code this?

0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Show me all what you have for the variable "cmd"
You have to do something like this: (not tested)

 Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick 

        If conn.State = ConnectionState.Open Then

            conn.Close()

        Else

            conn.Open()

        End If

        strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"
 

        Using cmd As New SqlCommand(strSQL1,conn)

              cmd.Parameters.Add("@Time_Compare", SqlDbType.DateTime).Value = Time_Compare

              Dim sdr As SqlDataReader = cmd.ExecuteReader

       

              If (sdr.Read()) Then

                  MsgBox("Do checkouts")

              End If

             sdr.Close()
 

        End Using

        conn.Close()

     End Sub

Open in new window

0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
It's in another routine that I use to update the database when doing an INSERT.

 Dim cmd As OleDbCommand = New OleDbCommand(sSQL, conn)


I get the error:
Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'System.Data.SqlClient.SqlConnection'.

with this line:
Using cmd As New SqlCommand(strSQL1, conn)      
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I also use it on the formload routine to fill the datagrid with the database.
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
Is this error:
Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'System.Data.SqlClient.SqlConnection'.
 because I'm using an Access database rather than a SQL server database??
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Two things
1st - You cannot reuse commands. You have to create a new one ofr each Transact SQL command.
2nd -  If you are using an Access database you should use Oledb class and not SqlClient. Change everything from qlDataReader  to OleDbDataReade, SqlConnection to OleDbConnection, etc
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:rkckjk
Comment Utility
Updated code attached.

It doesn't like the word "sqltype" 'in this statement:
cmd.Parameters.Add("@Time_Compare", sqlType.DateTime).Value = Time_Compare  

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        If conn.State = ConnectionState.Open Then

            conn.Close()

        Else

            conn.Open()

        End If

        strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"
 

        Using cmd As New OleDbCommand(strSQL1, conn)

            cmd.Parameters.Add("@Time_Compare", sqlType.DateTime).Value = Time_Compare  

            Dim sdr As OleDbDataReader = cmd.ExecuteReader

            If (sdr.Read()) Then

                MsgBox("Do checkouts")

            End If

            sdr.Close()

        End Using

        conn.Close()

    End Sub

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
That's because its OleDbType.Date
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
Ok, that worked.

Now getting new error on this line:
Using cmd1 As New OleDbCommand(strSQL1, conn)

 variablel cmd1 hides a variable in an enclosing block


Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Dim cmd1 As OleDbCommand = New OleDbCommand(strSQL1, conn)
        If conn.State = ConnectionState.Open Then
            conn.Close()
        Else
            conn.Open()
        End If
        strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"

        Using cmd1 As New OleDbCommand(strSQL1, conn)
            cmd1.Parameters.Add("@Time_Compare", OleDbType.Date).Value = Time_Compare
            Dim sdr As OleDbDataReader = cmd1.ExecuteReader

            If (sdr.Read()) Then
                MsgBox("Do checkouts")
            End If
            sdr.Close()

        End Using
        conn.Close()
    End Sub
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Remove this line "Dim cmd1 As OleDbCommand = New OleDbCommand(strSQL1, conn)"
You don't needed because you're aready declaring with the using method
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
Ok, no errors now. But it's not working for some reason???
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I define Time_Compare as the following, is this correct:

Dim Time_Compare As String = Format(Now, "H:mm:ss")
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I don't know if this is anything but I wanted to see what this value is so used a messagebox:

MsgBox(cmd1.Parameters.Add("@Time_Compare", OleDbType.Date).Value = Time_Compare)

It displays as false..Is that correct??
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Sorry I had to leave for some time!
Two things once again :
1st - Now you're using OleDb and not SqlClient and OleDb doesn't use parameters that way. It works but you should change change to:
strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = ?"
 
2nd - You need to use a valid time, and you can use a timespan for that. Here's what I have done and what you can use for you

        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb;"

        Using conn As New OleDbConnection(connString)

            conn.Open()
 

            strSQL1 = "SELECT * FROM Events WHERE [time] = ?"

            Using cmd1 As New OleDbCommand(strSQL1, conn)

                cmd1.Parameters.Add("@TimeValue", OleDbType.Date).Value = New DateTime(New TimeSpan(10, 0, 0).Ticks)

                Dim sdr As OleDbDataReader = cmd1.ExecuteReader
 

                If sdr.Read() Then

                    MsgBox("Do checkouts")

                End If
 

            End Using

        End Using

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Maybe this way:
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

       If conn.State = ConnectionState.Open Then

            conn.Close()

        Else

            conn.Open()

        End If

        strSQL1 = "SELECT Time_To_Check FROM FirstShift_Checklist WHERE Time_To_Check = @Time_Compare"
 

        Using cmd1 As New OleDbCommand(strSQL1, conn)

            cmd1.Parameters.Add("@Time_Compare", OleDbType.Date).Value = New DateTime(New TimeSpan(Now.Hour, Now.Minute, Now.Second).Ticks)

            Dim sdr As OleDbDataReader = cmd1.ExecuteReader
 

            If sdr.Read() Then

                MsgBox("Do checkouts")

            End If

            sdr.Close()
 

        End Using

        conn.Close()

    End Sub

Open in new window

0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
I think we're close.

I got  the following error:
ExecuteReader requires an open and available Connection. The connection's current state is closed.

on this statement:
 Dim sdr As OleDbDataReader = cmd1.ExecuteReader

But I did get a popup messagebox but it was blank, it should have said: "Do checkouts".
0
 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
Comment Utility
Do like I have showed.
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb;"
        Using conn As New OleDbConnection(connString)
            conn.Open()

End Using
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
Great!!! It works!! thanks for all your help....
0
 
LVL 2

Author Closing Comment

by:rkckjk
Comment Utility
thanks again!!
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
Glad I could help! :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

13 Experts available now in Live!

Get 1:1 Help Now