Compare system time to an Access table field

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

LVL 2
Richard KreidlSoftware DeveloperAsked:
Who is Participating?
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jorge PaulinoIT Pro/DeveloperCommented:
Why DISTINCT or UNIQUE ? You want ALL events for that period.
And cmd variable do have created a new instance on an SqlCommand ?
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
Where you define the variable "cmd" ?
 
Do you use:
Dim (or private) cmd As New SqlCommand
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
I also use it on the formload routine to fill the datagrid with the database.
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
That's because its OleDbType.Date
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
Ok, no errors now. But it's not working for some reason???
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
I define Time_Compare as the following, is this correct:

Dim Time_Compare As String = Format(Now, "H:mm:ss")
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
Great!!! It works!! thanks for all your help....
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
thanks again!!
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help! :)
0
All Courses

From novice to tech pro — start learning today.