Question Insert Value Into Unbound Column in DataGridView

[ncastillo]

I would like to insert a value into an unbound column(6) in my databound datagridview. The value will be based on the value in databound column(3). The following For Each Row Statement works fine, but it doesn't work when I apply a filter to the datagridview by using the bindingsource.filter method. I'm filtering the datagridview with a ComboBox (SelectedIndexChanged Event). I would appreciate any help. Thanks!


For Each Row As DataGridViewRow In dgv.Rows


If IsDBNull(row.Cells(3).Value) Then


row.Cells(6).Value = "Available"


Else


row.Cells(6).Value = "Unavailable"


End If
Next



Filter:
Shared Sub StandardToolStripComboBox_SelectedIndexChanged(ByVal Sender As Object, ByVal e As EventArgs)


LLBindingSource.Filter = "Code =" & "'" & CodeToolStripComboBox.Text & "'"


End Sub

 

[IanRyder]

Hi,

You can achieve what you are after by using an Expression column in your DataTable. A column with an expression applied is basically a virtual column in the DataTable that obtains its value based on whatever you apply to the expression. Here is an example:-

Public Class Form1
  Private dtEmployees As New DataTable
  Private bsEmployees As New BindingSource
 
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Dim daEmployees As New SqlDataAdapter("Select * From Employees", sqlConn)
 
    daEmployees.Fill(dtEmployees)
 
    Dim expressionCol As New DataColumn
    With expressionCol
      .DataType = GetType(String)
      .ColumnName = "Availability"
      .Expression = "IIf(ISNULL(LastName,'') ='Something', 'Available','Unavailable')"
    End With
    dtEmployees.Columns.Add(expressionCol)
 
    bsEmployees.DataSource = dtEmployees
    DataGridView1.DataSource = bsEmployees
  End Sub
End Class

Have a look here for more information on creating expressions for this type of column:-

DataColumn.Expression Property (System.Data)

Hope that helps.

Cheers,

Ian

 

[ncastillo]

Thanks! I'll try that.

 

[ncastillo]

I tried adding an expression column, but I get the error 'Cannot find Column [CheckedOutTo] when adding the expression column to the datatable. I would appreciate any help. Thanks!


Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)

Dim cmdLoanerTransactions As New OleDbCommand("SELECT a.Loaner, a.Code, a.Model," & _
" b.CheckedOutTo, b.DueDate," & _
" b.Comments, b.Analyst" & _
" FROM tblLoaners AS a LEFT JOIN (SELECT CheckedOutTo, DueDate," & _
" Comments, Analyst, Loaner FROM tblTransactions WHERE CheckedInDate IS NULL) AS b" & _
" ON a.ID = b.Loaner WHERE a.Active = 'Active'", conn)

Dim da As New OleDb.OleDbDataAdapter(cmdLoanerTransactions)
Dim cb As New OleDbCommandBuilder(da)

cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.Fill(ds, "dt")

Dim exStatusCol As New DataColumn

With exStatusCol
.DataType = GetType(String)
.ColumnName = "Status"
.Expression = "IIF(ISNULL(CheckedOutTo,''),'Available','Unavailable')"
End With

dt.Columns.Add(exStatusCol)

LoanerLibraryBindingSource.DataSource = ds
LoanerLibraryBindingSource.DataMember = "dt"

 

[ncastillo]

Got it working. Had to change the following lines. Thanks for your help!

Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)


Dim cmdLoanerTransactions As New OleDbCommand("SELECT a.Loaner, a.Code, a.Model," & _
" b.CheckedOutTo, b.DueDate," & _
" b.Comments, b.Analyst" & _
" FROM tblLoaners AS a LEFT JOIN (SELECT CheckedOutTo, DueDate," & _
" Comments, Analyst, Loaner FROM tblTransactions WHERE CheckedInDate IS NULL) AS b" & _
" ON a.ID = b.Loaner WHERE a.Active = 'Active'", conn)


Dim da As New OleDb.OleDbDataAdapter(cmdLoanerTransactions)
Dim cb As New OleDbCommandBuilder(da)


cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.Fill(dt)


Dim exStatusCol As New DataColumn


With exStatusCol
.DataType = GetType(String)
.ColumnName = "Status"
.Expression = "IIF(CheckedOutTo IS NULL,'Available','Unavailable')"
End With


dt.Columns.Add(exStatusCol)


LoanerLibraryBindingSource.DataSource = dt