[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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

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
Cained
Asked:
Cained
  • 5
  • 3
  • 2
  • +2
3 Solutions
 
DavidBirch2dotComCommented:
which language are you using?
0
 
CainedAuthor Commented:
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
 
DavidBirch2dotComCommented:
have a look at this

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

sorry im a bit rushed 4 time....
0
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!

 
CainedAuthor Commented:
Yes i saw that but i wasnt sure how to check the size of the database. Thanks
0
 
travisjhallCommented:
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
 
CainedAuthor Commented:
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
 
SCDMETACommented:
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
 
travisjhallCommented:
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
 
travisjhallCommented:
Oh, that's VB, and you've said you want to use C++. Well, it demonstrates the principles, anyway.
0
 
codedsolutionsCommented:
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
 
CainedAuthor Commented:
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
 
codedsolutionsCommented:
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
 
CainedAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now