Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

doing a search and replace on a datatable?

Posted on 2003-11-17
5
Medium Priority
?
1,193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

670 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