vb.netreplacedatatabledatagridviewconcatenation

Concatenate value of 2 columns datatable with space and replace "-" with empty in vb.net


I'm trying to Concatenate value of 2 columns datatable with space and replace "-" with empty in vb.net.

I have the code below, but this is still wrong.

please guide me

Thanks

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Example - Expression Column
        Dim dt = New DataTable()
        dt.Columns.Add("A", GetType(String))
        dt.Columns.Add("B", GetType(String))
        dt.Columns.Add("C", GetType(String), "CONVERT(A, System.String)+ B")
        dt.Rows.Add("BALOTELLY", "OLD WARDAH (02)")
        dt.Rows.Add("TOYOBO FODU THOMPSON", "LIGHT BLACK (12)")
        dt.Rows.Add("MCR", "-")
        dt.Rows.Add("WALLY COTTON CREAP", "-")
        dt.Rows.Add("WALLY COTTON CREAP", "BLACK (23)")
        DataGridView1.DataSource = dt

    End Sub
End Class

RESULT IN DATAGRIDVIEW

DESIRED RESULT

A B C
BALOTELLY OLD WARDAH (02) BALOTELLY OLD WARDAH (02)
TOYOBO FODU THOMPSON LIGHT BLACK (12) TOYOBO FODU THOMPSON LIGHT BLACK (12)
MCR - MCR
WALLY COTTON CREAP - WALLY COTTON CREAP
WALLY COTTON CREAP BLACK (23) WALLY COTTON CREAP BLACK (23)

Solution

  • You can use this expression to append B value if any and skip the - values.

    "A + IIF(B Is Null Or SUBSTRING(B, 1, 1) = '-', '', ' ' + B)"
    

    To apply that to A and B:

    "TRIM(IIF(A Is Null Or SUBSTRING(A, 1, 1) = '-', '', A) + IIF(B Is Null Or SUBSTRING(B, 1, 1) = '-', '', ' ' + B))"