Link to home
Start Free TrialLog in
Avatar of nomar2
nomar2

asked on

Decode function in VB.net function for use in a datagridview

I amusing VB.net where I have a call to 2 functions...


 Public Sub Express()
        Select_String2 = "SELECT tExpressBag.sBagNumber As [Tub Number], tExpress.dtBagReturned as [Status] FROM tExpress INNER JOIN tExpressBag ON tExpress.iBagID = tExpressBag.iID Where(tExpress.iCustomerID)= " & lnCustomerID & ""
            datGrdVwExpress.DataSource = Me.bindingSource2
            Call GetData2(Select_String2)
            datGrdVwExpress.Refresh()
    End Sub

    Private Sub GetData2(ByVal selectCommand As String)
        Try

            con.Open()
            data_adapter2 = New SqlDataAdapter(selectCommand, con)
            Dim commandBuilder As New SqlCommandBuilder(data_adapter2)

            Dim table As New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            data_adapter2.Fill(table)
            Me.bindingSource2.DataSource = table
            datGrdVwExpress.Columns(0).Width = 95
            datGrdVwExpress.Columns(1).Width = 95
            con.Close()
        Catch ex As SqlException
            MessageBox.Show("To run this example, replace the value of the " + _
                "connectionString variable with a connection string that is " + _
                "valid for your system.")
            con.Close()
        End Try
    End Sub

The above code grabs some data from a table and populates a datagridview..no problems here..this all works..the problem is this how do I decode the second column in the datagridview...for example if the date is null in the second column I want the word "No Date" to appear if the value is null and I want the word "Date issued" to appear in the second column is the date is not null.

I come from a Oracle background where we had the DECODE function that was great for such a situation..how do I do it here in VB.NET??

Any help or insight would be much appreciated.

Avatar of Bob Learned
Bob Learned
Flag of United States of America image

What is the back-end for the data source?

Bob
Avatar of nomar2
nomar2

ASKER

I am using sql server for the backend...
SQL Server has the COALESCE function that can translate NULL text to something else:

Should I use COALESCE() or ISNULL()?
http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

Bob
Avatar of nomar2

ASKER

What I want to do is this...

The datagridview has 2 columns...the first column I want to hold the bin Number..the second column I want to show the word "ISSUED"  if tExpress.dtBagReturned returns a date or show thew word "RETURNED" if tExpress.dtBagReturned is null.

How would I code my Select statement to reflect this??
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nomar2

ASKER

What would the syntax be...

I have the following code...

Select_String2 = "SELECT tExpressBag.sBagNumber As [Tub Number], Select Case When tExpress.dtBagReturned Is Not Null 'ISSUED' Else 'RETURNED' End as [Status] FROM tExpress INNER JOIN tExpressBag ON tExpress.iBagID = tExpressBag.iID Where(tExpress.iCustomerID)= " & lnCustomerID & ""

I keep getting syntax error near the word "Select"..


Avatar of nomar2

ASKER

 Select_String2 = "SELECT tExpressBag.sBagNumber As [Tub Number], Case When dtBagReturned Is Not Null 'ISSUED' Else 'RETURNED' End AS [status] FROM tExpress INNER JOIN tExpressBag ON tExpress.iBagID = tExpressBag.iID Where(tExpress.iCustomerID)= " & lnCustomerID & ""