?
Solved

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

Posted on 2004-08-05
15
Medium Priority
?
220 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
[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
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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
 
LVL 6

Accepted Solution

by:
SCDMETA earned 672 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 664 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 664 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…
Suggested Courses

777 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