Solved

doing a search and replace on a datatable?

Posted on 2003-11-17
5
1,188 Views
Last Modified: 2012-06-27
is there a way to search a datatable and whereever there is no value for a particular dataitem, to replace it with something, like a "0"?
0
Comment
Question by:trevoray
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:malharone
ID: 9767836
the code below replaces NULLs with 0s. the code i gave you is very generic .. you'll want to customize it to put default values depending on column data type

Public Class Form2
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.Button1 = New System.Windows.Forms.Button
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(16, 8)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(432, 144)
        Me.DataGrid1.TabIndex = 0
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(16, 160)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(112, 24)
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Do Replace"
        '
        'Form2
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(464, 310)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "Form2"
        Me.Text = "Form2"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim dt As DataTable
    Dim dr As DataRow
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dt = New DataTable("Employees")
        With dt
            With .Columns
                .Add("ID", (0).GetType)
                .Add("Salary", (1.1).GetType)
                .Add("Name", "".GetType)
            End With
            dr = .NewRow
            With dr
                .Item(0) = "1"
                .Item(1) = DBNull.Value
                .Item(2) = "Michaels"
            End With
            .Rows.Add(dr)

            dr = .NewRow
            With dr
                .Item(0) = "2"
                .Item(1) = 20
                .Item(2) = DBNull.Value
            End With
            .Rows.Add(dr)

            dr = .NewRow
            With dr
                .Item(0) = "2"
                .Item(1) = 30
                .Item(2) = "Jones"
            End With
            .Rows.Add(dr)
            Me.DataGrid1.DataSource = dt
        End With
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dc As DataColumn
        For Each dr In dt.Rows
            For Each dc In dt.Columns
                If IsDBNull(dr(dc)) Then
                    dr(dc) = 0
                End If
            Next
        Next
    End Sub
End Class
0
 

Author Comment

by:trevoray
ID: 9767900
ok, this is how i set up my datatable...

 Dim dt As New DataTable("duesTable")
        With dt
            .Columns.Add("transid")
            .Columns.Add("nmmst_id")
            .Columns.Add("dues_year")
            .Columns.Add("member_dues")
            .Columns.Add("toxsci")
        End With

and then later i would do...

        di = dt.NewRow()
        di("transid") = strTransID
        di("nmmst_id") = Label6.Text

and then finally...

        dt.Rows.Add(di)


I put this datatable into my datagrid and i can see that the columns that i did not add any values to are blank. (I DO NOT KNOW IF THIS IS NULL BY DEFAULT )

so, with the structure outlined above how could i do your statement? (esp, since i did not do a dim dc as DataColumn)

thanks!



0
 
LVL 9

Accepted Solution

by:
malharone earned 500 total points
ID: 9768022
the code i posted for Private Sub Button1_Click was very generic ...
it'll go through ENTIRE datatable (each cell of each row) and repalce nulls with 0.
but if you have something specific as above, then you'll have to setup validation rules.


the code below has the following set of rules
transID MUST be greater than or 0. NO NULLS -- default 1
nmmst_id CAN be number (positive and negatives allowed). No NULLS -- default -- 0
dues_year should be a four digit year between 1900 and 2100. NO NULLS, default -- current year
toxsci ... string .. NO NULLS .. default: "Arnold"


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim dr As DataRow
        Dim dc As DataColumn
        For Each dc In dt.Columns
            For Each dr In dt.Rows
                Select Case dc.ColumnName
                    Case "transid"
                        dr(dc) = getValue(dr(dc), True, 1, "")
                    Case "nmmst_id"
                        dr(dc) = getValue(dr(dc), True, 0)
                    Case "dues_year"
                        dr(dc) = getValue(dr(dc), True, 0, "")
                    Case "member_dues"
                        Dim val As Long = getValue(dr(dc), True, 0, "")
                        dr(dc) = IIf(val > 1900 AndAlso val < 2100, val, Date.Today.Year)
                    Case "toxsci"
                        dr(dc) = getValue(dr(dc), False, 0, "Arnold")
                End Select
            Next
        Next
end sub
0
 
LVL 9

Expert Comment

by:malharone
ID: 9768023
   Private Function getValue(ByVal currValue As Object, Optional ByVal isNum As Boolean = True, Optional ByVal defaultValue_num As Double = 0, Optional ByVal defaultValue_String As String = "") As Object
        If IsDBNull(currValue) Then
            If isNum Then
                Return defaultValue_num
            Else
                Return defaultValue_String
            End If
        Else
            If isNum Then
                If IsNumeric(currValue) Then
                    Return Val(currValue)
                Else
                    Return defaultValue_num
                End If
            Else
                Return defaultValue_String
            End If
        End If
    End Function
0
 

Author Comment

by:trevoray
ID: 9769750
thanks! that worked, perfect!!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 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