Solved

Change data in datatable

Posted on 2016-11-17
8
16 Views
Last Modified: 2016-11-17
hi,
I am binding my datagridview to datatable.
1 column in datatable has data like 0 or 1 .
Need to change 0 to null in the datatable.

Any way of doing this?
0
Comment
Question by:RIAS
  • 4
  • 3
8 Comments
 
LVL 14

Assisted Solution

by:Lokesh B R
Lokesh B R earned 100 total points
ID: 41891315
Hi,

Check this code


Dim dt As New DataTable()

dt.Columns.Add("IsChecked", GetType(Integer))
dt.Columns.Add("Name", GetType(String))

dt.Rows.Add(True, "Scott")
dt.Rows.Add(False, "Tom")
dt.Rows.Add(False, "John")
dt.Rows.Add(True, "Wilson")


For Each row As DataRow In dt.Rows
	If CInt(row("IsChecked")) = 0 Then
		row("IsChecked") = Nothing
	End If
Next

Open in new window

1
 

Author Comment

by:RIAS
ID: 41891342
Any suggestions on how to highlight the rows having value null in the column 'checked'

Cheers
0
 
LVL 14

Expert Comment

by:Lokesh B R
ID: 41891349
You can only do that while displaying in html by checking the null.
0
 

Author Comment

by:RIAS
ID: 41891352
I am usng vb.net app
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:Lokesh B R
ID: 41891355
windows application?
0
 

Author Comment

by:RIAS
ID: 41891356
yup!
0
 
LVL 32

Accepted Solution

by:
it_saige earned 400 total points
ID: 41891582
Use the CellFormatting event; e.g. -

Form1.vb -
Imports System.Runtime.CompilerServices
Imports System.ComponentModel

Public Class Form1
	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		DataGridView1.DataSource = (From i In Enumerable.Range(0, 20) Select New With {.ID = i, .FirstName = String.Format("FirstName{0}", i), .MiddleName = String.Format("MiddleName{0}", i), .LastName = String.Format("LastName{0}", i), .BirthDate = DateTime.Now.AddYears(-(i * 9)), .Age = DateTime.Now.Year - .BirthDate.Year, .IsWorking = i Mod 2}).ConvertToDataTable()
	End Sub

	Private Sub OnCellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
		If TypeOf sender Is DataGridView Then
			Dim dgv = DirectCast(sender, DataGridView)
			Dim row = dgv.Rows(e.RowIndex)
			Dim column = dgv.Columns(e.ColumnIndex)

			If column.Equals(dgv.Columns("IsWorking")) Then
				If row.Cells(column.Index).Value IsNot Nothing AndAlso Not IsDBNull(row.Cells(column.Index).Value) Then
					If (CType(row.Cells(column.Index).Value, Integer) = 0) Then
						row.DefaultCellStyle.BackColor = Color.Yellow
						Exit Sub
					End If
				End If
				row.DefaultCellStyle.BackColor = Color.White
			End If
		End If
	End Sub
End Class

Module Extensions
	<Extension()> _
	Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
		Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
		Dim table As DataTable = New DataTable()

		For i As Integer = 0 To properties.Count - 1
			Dim [property] As PropertyDescriptor = properties(i)
			If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
				table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
			Else
				table.Columns.Add([property].Name, [property].PropertyType)
			End If
		Next

		Dim values(properties.Count - 1) As Object
		For Each item As T In source
			For i As Integer = 0 To properties.Count - 1
				values(i) = properties(i).GetValue(item)
			Next
			table.Rows.Add(values)
		Next

		Return table
	End Function
End Module

Open in new window

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
		Me.DataGridView1 = New System.Windows.Forms.DataGridView()
		CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		'
		'DataGridView1
		'
		Me.DataGridView1.AllowUserToAddRows = False
		Me.DataGridView1.AllowUserToDeleteRows = False
		Me.DataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill
		Me.DataGridView1.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells
		Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
		Me.DataGridView1.Dock = System.Windows.Forms.DockStyle.Fill
		Me.DataGridView1.Location = New System.Drawing.Point(0, 0)
		Me.DataGridView1.Name = "DataGridView1"
		Me.DataGridView1.ReadOnly = True
		Me.DataGridView1.RowHeadersVisible = False
		Me.DataGridView1.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
		Me.DataGridView1.Size = New System.Drawing.Size(689, 328)
		Me.DataGridView1.TabIndex = 0
		'
		'Form1
		'
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(689, 328)
		Me.Controls.Add(Me.DataGridView1)
		Me.Name = "Form1"
		Me.Text = "Form1"
		CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)

	End Sub
	Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView

End Class

Open in new window

Produces the following output -Capture.JPG
-saige-
0
 

Author Closing Comment

by:RIAS
ID: 41891587
Thank you !
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now