Solved

Search for a values under 0 in a datatable

Posted on 2004-08-20
4
165 Views
Last Modified: 2010-04-23
Hello..

Need some help..

I want to search a datatable or dataset.. i have a datatable with data.. and i want to find
all the "cells" that has a value under 0 and place the "cell location" in a array..
so i can find that "postion" later

i want to use this for set the color to  red on that cell in a datagrid later.

plz help.. my last points i going now..... :(

thx.. :)
0
Comment
Question by:NissePPP
  • 3
4 Comments
 
LVL 27

Accepted Solution

by:
planocz earned 70 total points
ID: 11852756
here is a demo, I ADD the data for looking for < 0 but it is remarked out so you can see the regular demo run..

'Create NEW FORM1

Option Explicit On

Imports Microsoft.VisualBasic
Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Windows.Forms
Public Class DataGridColor
    Inherits Form
    Private WithEvents dataGrid1 As DataGrid
    Private components As Container
    'Fields
    'Constructors
    'Events
    'Methods

    Public Sub New()
        MyBase.New()
        '
        ' Required for Windows Form Designer support
        '
        InitializeComponent()

    End Sub
    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
    Private Sub InitializeComponent()

        Me.dataGrid1 = New System.Windows.Forms.DataGrid
        CType(Me.dataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        ' dataGrid1
        '
        Me.dataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right)
        Me.dataGrid1.DataMember = ""
        Me.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dataGrid1.Location = New System.Drawing.Point(24, 24)
        Me.dataGrid1.Name = "dataGrid1"
        Me.dataGrid1.Size = New System.Drawing.Size(448, 280)
        Me.dataGrid1.TabIndex = 0
        '
        ' DataGridColor   Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(504, 341)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.dataGrid1})
        Me.Name = "DataGridColor"
        Me.Text = "DataGridColor"
        AddHandler Me.Load, New System.EventHandler(AddressOf Form1_Load)
        CType(Me.dataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
    <STAThread()> _
    Public Shared Sub Main()

        Application.Run(New DataGridColor)

    End Sub
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)

        ' Set the connection and sql strings
        ' assumes your mdb file is in your root
        Dim connString As String
        connString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Northwind.mdb"
        Dim sqlString As String
        sqlString = "SELECT * FROM customers"
        Dim dataAdapter As OleDbDataAdapter
        dataAdapter = Nothing
        Dim _dataSet As DataSet
        _dataSet = Nothing
        ' Create a table style that will hold the new column style
        ' that we set and also tie it to our customer's table from our DB
        Try
            ' Connection object
            Dim connection As OleDbConnection
            connection = New OleDbConnection(connString)
            ' Create data adapter object
            dataAdapter = New OleDbDataAdapter(sqlString, connection)
            ' Create a dataset object and fill with data using data adapter's Fill method
            _dataSet = New DataSet
            dataAdapter.Fill(_dataSet, "customers")
            connection.Close()
        Catch ex As Exception
            MessageBox.Show(((((("Problem with DB access-" & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(10) & "   connection: " + connString) _
                            + "" & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & "            query: ") _
                            + sqlString) _
                            + "" & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10)) _
                            + ex.ToString))
            Me.Close()
            Return
        End Try
        Dim tableStyle As DataGridTableStyle
        tableStyle = New DataGridTableStyle
        tableStyle.MappingName = "customers"
        ' since the dataset has things like field name and number of columns,
        ' we will use those to create new columnstyles for the columns in our DB table
        Dim numCols As Integer
        numCols = _dataSet.Tables("customers").Columns.Count
        Dim aColumnTextColumn As DataGridColoredTextBoxColumn
        Dim i As Integer
        i = 0

        Do While (i < numCols)
            aColumnTextColumn = New DataGridColoredTextBoxColumn
            aColumnTextColumn.HeaderText = _dataSet.Tables("customers").Columns(i).ColumnName
            aColumnTextColumn.MappingName = _dataSet.Tables("customers").Columns(i).ColumnName
            tableStyle.GridColumnStyles.Add(aColumnTextColumn)
            i = (i + 1)

        Loop
        ' make the dataGrid use our new tablestyle and bind it to our table
        dataGrid1.TableStyles.Clear()
        dataGrid1.TableStyles.Add(tableStyle)
        dataGrid1.DataSource = _dataSet.Tables("customers")

    End Sub
End Class


'FORM CLASS

Option Explicit On

Imports Microsoft.VisualBasic
Imports System
Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Windows.Forms


Public Class DataGridColoredTextBoxColumn
    Inherits DataGridTextBoxColumn
    'Fields
    'Constructors
    'Events
    'Methods
    Public Sub New()
        'Warning: Implementation not found
    End Sub
    Protected Overloads Overrides Sub Paint(ByVal g As Graphics, ByVal bounds As Rectangle, ByVal source As CurrencyManager, ByVal rowNum As Integer, ByVal backBrush As Brush, ByVal foreBrush As Brush, ByVal alignToRight As Boolean)

        ' the idea is to conditionally set the foreBrush and/or backbrush
        ' depending upon some crireria on the cell value
        ' Here, we color anything that begins with a letter higher than 'F'
        Try
            Dim o As Object
            o = Me.GetColumnValueAtRow(source, rowNum)
            If (Not (o) Is Nothing) Then
                Dim c As Char
                c = CType(o, String).Substring(0, 1)
                If (c > "F") Then
                    backBrush = New SolidBrush(Color.Pink)
                    foreBrush = New SolidBrush(Color.White)
                End If
                'Use for numeric
                'o = CInt(o)
                'If (o <= 0) Then
                '    backBrush = New SolidBrush(Color.White)
                '    foreBrush = New SolidBrush(Color.Red)
                'End If
            End If
        Catch ex As Exception
            ' empty catch
        Finally
            ' make sure the base class gets called to do the drawing with
            ' the possibly changed brushes
            MyBase.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight)
        End Try

    End Sub
End Class
0
 
LVL 1

Author Comment

by:NissePPP
ID: 11856079
thx m8.. but..

 i can't understand why it's so slow.. the scrolling and updating the graphics.
when i use numeric... and when i use the demo.. the scrolling is perfect.. i tested with an
other code.. and it was the same problem there.

do you know the solution for that.. hmm

thx :)
0
 
LVL 1

Author Comment

by:NissePPP
ID: 11856148
i think the problem is that you got alot of exceptions everytime you try to convert
a string that has a char in iit.. so thats why i think you to read the datatable before.. get the lockations and set them later..

what do you think??

but how di i solve that problem.. hehe :)
0
 
LVL 1

Author Comment

by:NissePPP
ID: 11856245
i have tried to add this code.. but still i got some exceptions.. not so many as before..

but still... there are exceptions..

For i As Integer = 0 To CType(o, String).Length - 1
                    c = CType(o, String).Substring(i, i + 1)
                    If Not IsNumeric(c) Then
                        Exit Sub
                    End If
 Next i
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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