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

x
?
Solved

VB.NET: IF Else Query

Posted on 2008-11-10
3
Medium Priority
?
412 Views
Last Modified: 2012-05-05
Hi All,

I have a code below for VB.NET.

now is that i need to change my query that look like this.

if table mo_assembly = curdate() then 'Updated' Else 'Not-Up-To-Date'
if table mo_component = curdate() then 'Updated' Else 'Not-Up-To-Date'
if table mo_location = curdate() then 'Updated' Else 'Not-Up-To-Date'

same goes to all the other table

then from there i need to send a notification to admin that look like this

Table                                  Status
mo_assembly                     Updated
mo_component                   Not-Up-To-Date
mo_location                        Updated
mo_po_status                    Updated
mo_wo                               Updated
selling_price                      Not-Up-To-Date
Public Sub Checkdata()
        Dim sConString As String = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=206.209.241.43; DATABASE=DUS; UID=root; PASSWORD=newpwd; OPTION=16834"
        Dim Conn As New ADODB.Connection
        Conn.Open(sConString)
 
        Dim qryGetStatus As String = "select Name,DATE_FORMAT(last_update, '%Y-%m-%d') from backup where (name='mo_assembly' and name='mo_component' and name = 'mo_location' and name = 'mo_po_status' and name = 'mo_wo' and name='selling_price_financial_information_report') and DATE_FORMAT(last_update, '%Y-%m-%d')= curdate()"
        Dim rsGetStatus As New ADODB.Recordset
        rsGetStatus.Open(qryGetStatus, sConString, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
 
        Conn.Close()
 
        If Not rsGetStatus.EOF Then
            Process()
        Else
            sendemailnotuptodate()
        End If
    End Sub
 
    Public Sub sendemailnotuptodate()
 
        'Dim send_email As String
        Dim mymail
 
        mymail = CreateObject("CDO.Message")
        mymail.Subject = "Waterfall & Loading Scheduler Report"
        mymail.From = "Item_Branch_Scheduler"
        mymail.To = "email@email.com"
        
        'mymail.TextBody = "The Item_Branch data is not up-to-date. Please check from DUS/JDE database"
        mymail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        'Name or IP of remote SMTP server
        mymail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "206.209.242.146"
        'Server port
        mymail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        mymail.Configuration.Fields.Update()
        mymail.send()
        mymail = Nothing
 
        mymail.TextBody = "The Item_Branch data is not up-to-date. Please check from DUS/JDE database"
 
 
 
 
    End Sub

Open in new window

0
Comment
Question by:bosscat
3 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22925645
>>if table mo_assembly = curdate() then 'Updated' Else 'Not-Up-To-Date'

I don't understand.  The table can't equal the current date.  A field in a record in a table can ... what field are you looking to compare to the current date?
0
 
LVL 1

Author Comment

by:bosscat
ID: 22926527
the field in the table is call last_update
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1500 total points
ID: 22927436
SQL Server and MySQL use the CASE statement.

CASE Statement
http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

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