Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1194
  • Last Modified:

doing a search and replace on a datatable?

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
trevoray
Asked:
trevoray
  • 3
  • 2
1 Solution
 
malharoneCommented:
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
 
trevorayAuthor Commented:
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
 
malharoneCommented:
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
 
malharoneCommented:
   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
 
trevorayAuthor Commented:
thanks! that worked, perfect!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now