Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse IIF in a SQL SELECT statement to display a warning if a value is too low in VB.NET
DescriptionThis example shows how to use IIF in a SQL SELECT statement to display a warning if a value is too low in VB.NET.
KeywordsDataGrid, binding, bind, bound, VB.NET, IIF, warning
CategoriesVB.NET, Controls, Database
 
IIF takes three arguments: a Boolean expression to evaluate, the result that should be returned if the expression is True, and the result that should be returned if the expression is False. This example uses IIF to display the string WARNING if a test score value is less than 75 or an empty string if the score is not less than 75.

Start by binding a DataGrid control to a DataSet. Create an OleDbDataAdapter. Use the DataAdapter Configuration Wizard to configure the adapter. This builds a database connection for the adapter and defines the SQL query used to select data.

Select the new adapter. Below the Properties window, click the "Generate DataSet" link. This makes a DataSet that can hold the data selected by the adapter.

Add a DataGrid to the form. Set its DataSource property to the DataSet or to one of the tables selected by the DataSet.

When the form loads, use code similar to the following to make the adapter load data into the DataSet.

 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles MyBase.Load
    Dim db_path As String = Application.StartupPath
    db_path = db_path.Substring(0, db_path.LastIndexOf("\"))
    db_path &= "\StudentData.mdb"
    connStudents.ConnectionString = _
        "Data Source=""" & db_path & """;" & _
        "Provider=""Microsoft.Jet.OLEDB.4.0"";" & _
        "persist security info=False"

    Dim warning_query As String = "IIF(Score<75, 'WARNING', " & _
        "'') AS Warning "
    Dim query As String = _
        "SELECT FirstName, LastName, TestNumber, Score, " & _
            _
        warning_query & _
        "FROM Students, TestScores " & _
        "WHERE Students.StudentId = TestScores.StudentId " _
            & _
        "ORDER BY FirstName, LastName, TestNumber"
    daStudents.SelectCommand = New _
        OleDb.OleDbCommand(query, connStudents)

    daStudents.Fill(dsStudents)
End Sub
 
For more information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated