Solved

doing a search and replace on a datatable?

Posted on 2003-11-17
5
1,187 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

15 Experts available now in Live!

Get 1:1 Help Now