Solved

Change data in datatable

Posted on 2016-11-17
8
41 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:RIAS
ID: 41891352
I am usng vb.net app
0
 
LVL 14

Expert Comment

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

Author Comment

by:RIAS
ID: 41891356
yup!
0
 
LVL 33

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…

830 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