Solved

doing a search and replace on a datatable?

Posted on 2003-11-17
5
1,186 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
   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
Comment Utility
thanks! that worked, perfect!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

10 Experts available now in Live!

Get 1:1 Help Now