Working with recordcounts

[jtrumb]

Hello All,

I have a project im working on which is causing me to have to check a SQL database for any new records comming into a table in the last 2 minutes, and using that record count to activate a timer control in my application which will cause an item to blink in the system tray. I have the following code in which the SQL statement is working fine what i need to know is after i run this statement how i can check the recordcount and if its > 0 enable a timer on a form. All help is much appreciated :

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim myCommand As New SqlCommand
            Dim myConnection As SqlConnection

            myConnection = New SqlConnection(csNK1)
            myConnection.Open()
            With myCommand
                .CommandType = CommandType.Text
                .Connection = myConnection
                .CommandText = "select count(*) from cnEventLog WHERE IncidentTime BETWEEN DATEADD(MI, -2, GETDATE()) AND GETDATE()"
            End With
            myCommand.ExecuteScalar()

        Catch ex As Exception
            MsgBox(ex.Message)
            Return
        End Try
    End Sub

 

[MattP]

Dim count As Int32 = Convert.ToInt32(myCommand.ExecuteScalar())

Return count

If you're not interested in the number of records you could also do

If myCommand.ExecuteScalar() <> 0 Then
   Timer1.Enabled = True
...

 

[jtrumb]

works fantastic, unfortunately it has prompted another question. in the timer control i have to find a way to create a blinking icon in the system try to alert a user to a new event comming in. My idea was to use 2 timer controls the first one being enabled when count > 0 that timer will then put an icon in the sys tray say a smile face icon or something, the 2nd timer will enable timer 1 and disable itself and put a sad face icon thus simulating blinking of an icon in the system tray. I know in vb 6 i had to use an annoying API to do all of this... first is there a better way i should be doing this, and if not how do i interface with the systray and accomplish something like this?

 

[cjard]

Why would you need 2 timers?

Just make one, with an interval of 1 second. Use a background worker to do the records count. DO NOT DO THE COUNT ON THE UI THREAD. Every 120 ticks of the timer (use a variable to count), look for new records. As to the tray icon, take a look at NotifyIcon Class (System.Windows.Forms)

 

[jtrumb]

Why would you need 2 timers?

Just make one, with an interval of 1 second. Use a background worker to do the records count. DO NOT DO THE COUNT ON THE UI THREAD. Every 120 ticks of the timer (use a variable to count), look for new records. As to the tray icon, take a look at NotifyIcon Class (System.Windows.Forms)

Since I am using the timers purly to simulate the flashing of the icon which i don't know if that is completely genuis or dumb as hell, i will assume the latter. It was my thought 2 would be necessary to swap the images every 1 second to simulate blinking. The timer code so you get an idea of what im doing is: say we had 2 labels same text in them 2 different coloros and wanted to simulate a blinking to the eye i would make 2 timer controls and do something like:

Timer1.Timer
Timer1.Enabled = False
Timer2.Enables = True
Label1.Visible = False
Label2.Visible = True

then reverse the true and falses in timer 2 so in essence the timers would flip back and forth on N intreval simulating the blinking of the label. It was my thought i would have to do the same to make an icon in the systray blink?

 

[cjard]

Er

Timers are a finite, exhaustible resource. It's something of programming bad manners to use more than you need. COnsider a timer that ticks every second and incrememnts an Integer from 0, 1, 2, 3, 4 ...

Every time there is an Odd Number (read about the Mod operator) show one image, otherwise, show another image.

Further, every time the counter reaches 120, check for new records

 

[jtrumb]

Ok so basically what you are saying is set my timer intreval to 1000 (from what i gather they run in milliseconds?) then inside the timer control itself have something like

dim iCnt as int32 = timer1.timer
if iCnt = 120 then
'check db
if newrecord found then
swap images based on odd or even number
end if
end if

ok i think what your saying is wayyyy beyond me and sample would help. Also a question that comes up is at some point in time i would need to zero out the timer to make sure it gets to 120 again so im guessing at the bottom of my ifs i would need something like

timer1.timer = 0

sorry for all the questions as im sure you can already tell im very new to vb.net

 

[cjard]

The timer doesnt maintain memory of how many ticks it has performed, afaik. You must do this.

Public Class MyForm Extends Form

  Private _tickCount as Integer = 0
  Private _newData as Boolean = False

  Private Sub TimerX_Tick(...) Handles _timerX.Tick

    _tickCount += 1

    If _tickCount > 119 AndAlso Not _dbCheckerBackgroundWorker.IsBusy Then 'always use greater or less than. Don't use = 120
      _dbCheckerBackgroundWorker.RunWorkerAsync() 'do operations on a background thread
      _tickCount = 0
    End If

    If _newData Then
      If _tickCount Mod 2 = 0 Then
        'show one image
      Else
        'show other image
      End If
    End If
  End Sub

  Private Sub BGW_DoWork(...) Handles _dbCheckBackgroundWorker.DoWork

    'look for new data

    'if no new data exists
    _dbCheckBackgroundWorker.ReportProgress(0)
    'else
    _dbCheckBackgroundWorker.ReportProgress(0)
  End Sub

  Private Sub BGW_ReportProgress(...) Handles ....

    If e.ProgressPercentage = 0 Then
      _newData = False
    Else
      _newData = True
    End If
  End Sub
End Class

notes:
dont put the flash code inside the check of every 120 seconds, otherwise your icon only flashes once every 120 seconds

use a background worker to do the db check, report a progress and in there, set the values of the relevant threads. this is so you dont get multi threading problems

i typed this by hand, do not assume it is working compilable code. you will have to investigate the BackgroundWorker component yourself etc and the parameters to the event handlers (...)

if you're very new to programming, you may well be biting off more than you can chew here. Most people who are at the "use a class level variable" level would not be doing multithreaded database access. While I don't mean to discourage questions, I also don't want to do your work for you (for free) because netiher of us gains anything from such a course. Your task will involve a lot of outside reading etc, so make heavy use of google where possible.

database access is a lot easier if you read the guides in the DW2 link in my signature, start with Creating A Simple Data App

make extensive use of pencil and paper, or write logical, high lvel comments in your code, and THEN translate it to real code. Even pro developers write complex algorithms in high level terms before they code the low down. It helps avoid bugs like your If nesting being wrong