DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Need SQL Query Help

  1. #1
    Join Date
    Mar 2005
    Location
    USA
    Posts
    9

    Need SQL Query Help

    I have a ADODB connection to a SQL server over a LAN. It has a specific database with five Tables that contain data from 50 automated testcells that send a 26 Field record for each completed test cycle over an an OPC Server/Router based connection. So each Table has 26 Fields. I need to construct a simple tool that will connect to the table of my choosing and using a timer loop periodically update a Label control caption with the lastest value of Field number 3.

    I can do all of this except construct the SQL query that pulls the latest value for Field number 3.

    Something like:

    SELECT * Field3, ORDER BY Field3... something, something

  2. #2
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    It would be more like ...

    Code:
    Dim oRs As ADODB.Recordset
    
    Combo1.AddItem "Table1"
    Combo1.AddItem "Table2"
    Combo1.AddItem "Table3"
    Combo1.AddItem "Table4"
    Combo1.AddItem "Table5"
    
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT TOP 1 Field3 FROM [" & Combo1.Text & "] ORDER BY YourDateTimeStampField DESC", oConn
    
    If oRs.BOF = False And oRs.EOF = False Then
        Label1.Caption = Trim$(oRs.Fields("Field3").Value & vbNullString)
    Else
        MsgBox "No Records Returned"
    End If
    Last edited by RobDog888; 04-25-2007 at 07:28 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  3. #3
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    SELECT Field3 FROM TABLE1 ORDER BY Field3 Desc

    Note this will only work if the value of Field3 is always increasing, since you are sorting in descending order the first record will be the record you want. Now if the values can be any amount then you will need another field to sort by like a date time field that shows when the entry was made, or if you have a field that automatically increments you can sort it in descending order and that will make the latest entry the first record.

  4. #4
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Correct and which is why I'm ordering by a datetime stamp field: YourDateTimeStampField
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  5. #5
    Join Date
    Mar 2005
    Location
    USA
    Posts
    9

    Yes!

    Yes the values in the field contain the date-time stamp for each completed test so they are constantly updating. The problem we have is that the server is not monitored for activity on a regular basis so i need a desktop app with a timer that will check the DB periodically and compare the latest value to the last value and set off an audible alert if it isn't updating regularly. The system is very stable overall, but the OPC Server/Router is extremely sensitive to device collisions between IP addresses and I can't rely on my IS department not to step on my subnet. The data collection process is too critical to let lapse.

  6. #6
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    What db is it running on? Access, SQL Server, MySQL, etc?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  7. #7
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    SELECT Field3 FROM Table1 ORDER BY Field4 Desc

    Here Field3 is the value you need and Field4 is the date-time stamp field. What are you creating this monitoring program in? VB, or Access, ..?
    Right now I am using Access as a front end for an Oracle database. It's working well so far.

  8. #8
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    SELECT Field3, "Table1" As TableName FROM Table1 WHERE Field4 = (SELECT Max(Field4) FROM Table1)
    UNION SELECT Field3, "Table2" As TableName FROM Table2 WHERE Field4 = (SELECT Max(Field4) FROM Table2)
    UNION SELECT Field3, "Table3" As TableName FROM Table3 WHERE Field4 = (SELECT Max(Field4) FROM Table3)
    UNION SELECT Field3 , "Table4" As TableName FROM Table4 WHERE Field4 = (SELECT Max(Field4) FROM Table4)
    UNION SELECT Field3, "Table5" As TableName FROM Table5 WHERE Field4 = (SELECT Max(Field4) FROM Table5)
    ORDER BY TableName

    This is a union query that will produce a single recordset with exactly 5 records where each record has the latest Field3 value for each table, as well as the table name it came from.

  9. #9
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Just a little test app I threw together in Access.
    If you open the database in Access the form automatically opens and starts checking the server.
    All of the tables are actually local, not on a server, but all you have to do is delete or rename the test tables and link in the Sql Server tables. Then if needed open the unuion query and change the table names and field names to match what is in your tables. I used Alias's for all of the field names so the form should work no mater what field names your tables use.
    The timer is set to check the server every 30 seconds. The Form Load Event is where I set the Interval, so you can change it there. The blinking colons in the time display is so the form does not look dead, like it might be locked up or something.
    Last edited by Ron Weller; 02-11-2009 at 08:00 PM.

Similar Threads

  1. Replies: 0
    Last Post: 11-30-2006, 06:58 AM
  2. SQL2000 remote SQL query
    By lightningtechie in forum Database
    Replies: 1
    Last Post: 02-07-2006, 09:34 AM
  3. Return Query Estimate for SQL Server 2000
    By Daniel Reber in forum VB Classic
    Replies: 0
    Last Post: 09-16-2002, 02:50 PM
  4. SQL Query Fails on NT System
    By Ken D. in forum VB Classic
    Replies: 2
    Last Post: 09-03-2002, 11:25 PM
  5. IIF in SQL Query Analyzer
    By Jeff Johnson in forum Database
    Replies: 3
    Last Post: 04-30-2001, 02:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center