?
Solved

Compare system time to an Access table field

Posted on 2009-04-12
27
Medium Priority
?
217 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:Richard Kreidl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 12
27 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24128510
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:Richard Kreidl
ID: 24129764
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
ID: 24129943
Why DISTINCT or UNIQUE ? You want ALL events for that period.
And cmd variable do have created a new instance on an SqlCommand ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24130000
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
ID: 24130030
Where you define the variable "cmd" ?
 
Do you use:
Dim (or private) cmd As New SqlCommand
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24130091
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
ID: 24130303
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:Richard Kreidl
ID: 24130425
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
ID: 24130494
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:Richard Kreidl
ID: 24130555
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:Richard Kreidl
ID: 24130577
I also use it on the formload routine to fill the datagrid with the database.
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24130669
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
ID: 24130756
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
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24130818
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
ID: 24130827
That's because its OleDbType.Date
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24130865
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
ID: 24130877
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:Richard Kreidl
ID: 24130929
Ok, no errors now. But it's not working for some reason???
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24131010
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:Richard Kreidl
ID: 24131343
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
ID: 24132172
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
ID: 24132189
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:Richard Kreidl
ID: 24132237
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 2000 total points
ID: 24132289
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:Richard Kreidl
ID: 24132408
Great!!! It works!! thanks for all your help....
0
 
LVL 2

Author Closing Comment

by:Richard Kreidl
ID: 31569336
thanks again!!
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24132464
Glad I could help! :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

719 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