Solved

System Tray Icon to say when a new item has been added to a database

Posted on 2004-08-05
15
214 Views
Last Modified: 2010-04-17
Hi I have a database on a webserver. I was wondering if it was possible have an icon on the system tray that would become visible everytime a new item is added to the database.

What i guessed is that once every time period (say 5mins) it would query the databases size and if it had increased by more then 1kb then five minutes previous it would be come visible. The webserver is on a local computer so it will all run over the network.

Does anyone have any idea how this could be possible??

All the compters here are using 2000 or XP
0
Comment
Question by:Cained
  • 5
  • 3
  • 2
  • +2
15 Comments
 
LVL 7

Expert Comment

by:DavidBirch2dotCom
ID: 11724507
which language are you using?
0
 

Author Comment

by:Cained
ID: 11724521
I intended to use C++ but if someone can point me to a good tutorial then I am prepared to use anything. I can also use JAVA and Basic.
0
 
LVL 7

Expert Comment

by:DavidBirch2dotCom
ID: 11724541
have a look at this

http://www.codeproject.com/shell/systemtray.asp

sorry im a bit rushed 4 time....
0
 

Author Comment

by:Cained
ID: 11724559
Yes i saw that but i wasnt sure how to check the size of the database. Thanks
0
 
LVL 3

Expert Comment

by:travisjhall
ID: 11724808
Don't. That's a very unreliable way of determining whether a new record has been added to a database. There's a lot of things that can affect the way databases grow in size.

What sort of database are you using? I can suggest several ways of doing what you want with SQL Server, and some of those techniques would adapt to other DBMSs. Here's one approach...

In the table you wish to watch, add a field called CreateTime, with data type datetime or smalldatetime. Set the default value of CreateTime to GETDATE(). Now, every record in the table is marked with the time at which it was created, and you can simply query the table to see whether there are records created since a certain time. Bear in mind that storing CreateTime takes space, and querying the table may take significant processing power if the table becomes large. If you index CreateTime, especially if the index is clustered, this will reduce the processing necessary to do what you want (but you can only have one clustered index per table, so choose wisely - something else may be more important). This technique is probably only worth it if there is some other reason to track CreateTime.

Or...

For the table you wish to watch, create an INSERT trigger. In the trigger, write the time of the last update into another table. Then, you can query that table to determine whether a record has been added within a certain period.

Or...

Require in your database design that all updates to your table be done via a stored procedure. In said stored procedure, write the time of the last update into another table as per previous.

Or...

If you want to get really fancy, use extended stored procedures to open some form of connection to a waiting process on your client machines and send said client machines some sort of signal that an update has been performed.

As for actually creating the icon, if David hasn't covered that for you, I can probably find you the relevant MSDN article.
0
 

Author Comment

by:Cained
ID: 11725047
The problem is I am using a MS Access database. It is not my database and so i cant easily change it. COuld i query the table to find out the size of the table and so when the table has increased it will findout.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 6

Accepted Solution

by:
SCDMETA earned 168 total points
ID: 11725454
You could do a simple:
SELECT FIELD FROM TABLE
or
SELECT * FROM TABLE
and check the record count.  Compare the previous with the current results and you can determine if any records were added.  This is assuming none have been deleted.

   Dim YourDatabasePath As String
    Dim objConn As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim objRS As ADODB.Recordset
    Dim sConnString As String
    Const adCmdText = &H1
   
    On Error GoTo ErrorTrap
   
    YourDatabasePath = "c:\test.mdb"
                 
    Set objConn = New ADODB.Connection
    Set objCmd = New ADODB.Command
    Set objRS = New ADODB.Recordset
                   
    ' DSNless connection
    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & YourDatabasePath
                   
    objConn.Open sConnString
    Set objCmd.ActiveConnection = objConn

    objCmd.CommandText = "SELECT field FROM table;"
    objCmd.CommandType = adCmdText
                 
    Set objRS = objCmd.Execute

    x = objRS.recordcount

0
 
LVL 3

Assisted Solution

by:travisjhall
travisjhall earned 166 total points
ID: 11732044
SELECT COUNT(*) FROM table
is better than
SELECT * FROM table
Less processing and traffic with the former.

However, neither works particularly well unless you know for sure that records will not be deleted.

Is there a primary key on the table? If there is, you can retrieve a list of record keys from the database, then compare that to another list five minutes later.

(Modifying SCDMETA's code, so that you can compare with less confusion...)

Function IsRecordAdded() As Boolean
IsRecordAdded = False
Dim YourDatabasePath As String
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRSnew As ADODB.Recordset
' Static so that it is retained from one run to the next
Static objRSinitial AS ADODB.Recordset
Dim sConnString As String
   
YourDatabasePath = "c:\test.mdb"
                 
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command
'Removed Set objRS... entirely. You set this again further down, so setting it here is just a waste of resources.
                   
' DSNless connection
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & YourDatabasePath
                   
objConn.Open sConnString
Set objCmd.ActiveConnection = objConn
objConn.CursorLocation = adUseClient

objCmd.CommandText = "SELECT keyfield FROM table ORDER BY keyfield"
objCmd.CommandType = adCmdText

Set objRSnew = objCmd.Execute

If Not IsEmpty(objRSinitial) Then
    ' this is a quicker test, so include it to reduce processing
    If objRSnew.RecordCount > objRSinitial.RecordCount Then
        IsRecordAdded = True
    Else
        objRSinitial.MoveFirst
        Do Until objRSnew.EOF
            Do Until objRSinitial.Fields("keyfield").Value >= objRSinitial.Fields("keyfield").Value OR objRSinitial.EOF
                objRSinitial.Fields("keyfield").MoveNext
            Loop
            If objRSinitial.EOF Then
                IsRecordAdded = True
                Exit Do
            End If
            If objRSinitial.Fields("keyfield").Value > objRSinitial.Fields("keyfield").Value Then
                IsRecordAdded = True
                Exit Do
            End If
            objRSinitial.MoveNext
            objRSnew.MoveNext
        Loop
    End If

End If
Set objRSinitial = objRSnew
Set objRSinitial.ActiveConnection = Nothing

End Function

You had better check on whether you get a forward-only recordset out of that use of the command object. If you do, you've have to use the Open method of the recordset object to get your data rather than using a command object. I don't remember this off the top of my head. Remember to add appropriate error handling.
0
 
LVL 3

Expert Comment

by:travisjhall
ID: 11732051
Oh, that's VB, and you've said you want to use C++. Well, it demonstrates the principles, anyway.
0
 

Assisted Solution

by:codedsolutions
codedsolutions earned 166 total points
ID: 11732056
in VB.net here is some code that should work.  All you need to do is start a new windows application, drag a timer and a notifyicon onto the form and paste this code.  Make sure the timer name and the icon name match.  You will also need to figure out wha ticon to use.

Code:


Imports System.Data
Imports System.Data.oledb
Public Class frmMain
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Timer1 As System.Windows.Forms.Timer
    Friend WithEvents Icon1 As System.Windows.Forms.NotifyIcon
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmMain))
        Me.Icon1 = New System.Windows.Forms.NotifyIcon(Me.components)
        Me.Timer1 = New System.Windows.Forms.Timer(Me.components)
        '
        'Icon1
        '
        Me.Icon1.Icon = CType(resources.GetObject("Icon1.Icon"), System.Drawing.Icon)
        Me.Icon1.Text = "Icon1"
        '
        'Timer1
        '
        Me.Timer1.Interval = 1000
        '
        'frmMain
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.Name = "frmMain"
        Me.Text = "Main"

    End Sub

#End Region

    Private cnn As OleDbConnection = New OleDbConnection
    Private cmd As OleDbCommand = New OleDbCommand
    Private iRowcount As Integer
    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Timer1.Interval = 60000
        Timer1.Enabled = True
        iRowcount = 0

        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\net\db1.mdb;User Id=admin;Password=;"

    End Sub

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

        Timer1.Enabled = False
        If CheckForNewrecord() Then
            Icon1.Visible = True
        End If
        Timer1.Enabled = True
    End Sub
    Private Function CheckForNewrecord() As Boolean
        Dim newrow As Integer
        cmd.CommandText = "select count(*) as c1 from customers"
        cmd.CommandType = CommandType.Text
        cnn.Open()
        newrow = cmd.ExecuteScalar

        If newrow <> iRowcount Then
            CheckForNewrecord = True
            iRowcount = newrow
        End If

        cnn.Close()
    End Function
End Class
0
 

Author Comment

by:Cained
ID: 11750424
Cheers this VB code looks great

Is the link i need to chang to work with my DB these lines?
 
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\net\db1.mdb;User Id=admin;Password=;"
 cmd.CommandText = "select count(*) as c1 from customers"

if the DB is called News and the table is called Headline.


cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\location\Nerws.mdb;User Id=admin;Password=;"
 cmd.CommandText = "select count(*) as c1 from Headline"
0
 

Expert Comment

by:codedsolutions
ID: 11751805
The connection string and SQL text look correct.
You will need to adjust the timer interval to whatever you need, just remember it is in milliseconds.

-codedsolutions
0
 

Author Comment

by:Cained
ID: 11751811
Cheers I just need to find my copy of vb now. I dont use it for a few weeks and then I can  never find it.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
bunnyEars challenge 6 62
word0 challenge 3 58
oracle query help 18 77
c++ reading data from file into two dimensional array 3 25
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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…

708 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

14 Experts available now in Live!

Get 1:1 Help Now