Start Free Trial

asked on

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

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

ASKER

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!!

Why DISTINCT or UNIQUE ? You want ALL events for that period.
And cmd variable do have created a new instance on an SqlCommand ?

ASKER

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

Where you define the variable "cmd" ?

Do you use:
Dim (or private) cmd As New SqlCommand

ASKER

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.

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

ASKER

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?

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

ASKER

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)

ASKER

I also use it on the formload routine to fill the datagrid with the database.

ASKER

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??

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

ASKER

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

That's because its OleDbType.Date

ASKER

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

Remove this line "Dim cmd1 As OleDbCommand = New OleDbCommand(strSQL1, conn)"
You don't needed because you're aready declaring with the using method

ASKER

Ok, no errors now. But it's not working for some reason???

ASKER

I define Time_Compare as the following, is this correct:

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

ASKER

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??

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

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Great!!! It works!! thanks for all your help....

ASKER

thanks again!!

Glad I could help! :)