Solved

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

Posted on 2004-08-05
15
217 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Plain Text Editor for iPad 6 96
ASP/VB email question 4 46
Selenium docs api java index 3 48
Eclipse Java import and method not resolved 4 50
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

831 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