Solved

Iterate through Dataset inside a big For loop in vb.net

Posted on 2010-09-06
12
1,042 Views
Last Modified: 2012-05-10
Hi  Everyone,
I am working on a desktop App in VB.net with SQL Server as back end.
 I have a smaller dataset from a Conditions Table that has condition name and condition values.
I have a datagridview that has my main table data. Before loading,  I want to verify each row against this bunch of conditions and set the flag value in my main table. And then I will load the grid.
In vb6 I would do the following,

Create a recordset from the Conditions field called rsConditions
Populate Datagrid with main table data

For I = 0 to Datagrid.rows.count - 1
         rsConditions.MOVEFIRST
        while not rsconditons.eof
                 'Do some verification
                  sFieldName= rsConditions.fieldname
                  if datagridrow.row(sFieldName)<> rsConditions("FieldName).value then
                      bFlag=false
                  End If
                   rsConditons.MoveNext
        wend
  Next I    

How do i do this in VB.net. I tried using Dataset so that I can iterate thru, but how do i retrieve field name and field value of my dataset in an easier and simpler way? Can you user dataset just like a recordset and move pointers back and forth?  I will appreciate if some one provides me with a sample code .I have a project due this week and I am stuck here. Appreciate your help. Thank you.

Dim nCount As Integer = dt.Columns.Count
Dim dataViewOrg As New DataView(dt)
For i = 0 To dataViewOrg.Count - 1
  'This is where I want to insert my smnaller while loop to check each column of this row agains the conditions
Next

Open in new window

0
Comment
Question by:RekhaShah
  • 7
  • 5
12 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33612427
<<how do i retrieve field name and field value of my dataset >>
You can reffer to field names from a dataset like this:

Dim MyDataSet As New DataSet
Dim ColValue As String = MyDataSet.Tables(0).Rows(0)("YourColumnName") 

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33612485
Make A correction

Dim ds As New YourDataSet
Dim ColValue As String = ds.Tables(0).Rows(0)("YourColumnName")

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33612581
You can make the loopas below

 For Each row As DataGridViewRow In Me.MYDataGridView.Rows
            If Not row.IsNewRow Then
                For Each drow As DataRow In Me.NumDataSet.Tables(0).Rows
                    If row.Cells("YourColumn").FormattedValue <> drow.Item("YouTableColumn") Then
                    bFlag=false
                    End If
                Next
            End If
        Next

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33612583
Where NumDataset Your Dataset
0
 

Author Comment

by:RekhaShah
ID: 33612617
I need to read column name as well as its value. My table has three fields, "Id", "FieldName", and "FieldMaxSize , FieldMinSize ,FieldMaxValue, FieldMinValue, I need to read 1st column to get the field name (this name is one of the columns in my datagridview), So I check the value of Datagridview's column(FieldName)  against the the rest of the properties like max val,min val etc.

" YourColumnName " are dynamic and read from the "FieldName" of the conditons table.
So the pseudocode is like this:

For i = 1 to datagridview Row Count
      WHILE NOT DS.EOF
        if row.column("FieldName") .value > (ds("FieldMaxVal" ).value then
            bInvalidData = true"
        end if
       if row.column("FieldName") .value < (ds("FieldMinVal" ).value then
             bInvalidData = true"
         end if
      if len(row.column("FieldName").ToString ) > (ds("FieldMaxSize" ).value then
             bInvalidData = true"
       end if
     'Get the next record from the Conditions table and start over(condition for a different            column of the grid.
    DS.move next
Next I
0
 

Author Comment

by:RekhaShah
ID: 33612623
sorry, i left out
 DS.MoveFirst in the beginning of For Loop
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33612673
question .......
<<table has three fields, "Id", "FieldName", and "FieldMaxSize , FieldMinSize ,FieldMaxValue, FieldMinValue>> are 3 or 6...
if you know exactly the position of the fieldNAme you can reffer to columnIndex...
so only 1 column of the datagridview needs to be checked ??
Also another question ...
you data in you datagridview has the same number of rows with the table you want to check ....
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33612802
try this
For Each row As DataGridViewRow In Me.MYDataGridView.Rows
            If Not row.IsNewRow Then
                For Each drow As DataRow In Me.NumDataSet.Tables(0).Rows
                    If Me.MYDataGridView.Columns(0).Name = Me.NumDataSet.Tables(0).Columns("FieldName").ColumnName Then
                      If row.Cells("FieldName") .value > drow.Item("FieldMaxVal") then 
                       bInvalidData = true"
                      ElseIf........                                     

                      End if 
                    Else
                      .
                      .
                    End if
                                                      
                Next
            End If
        Next

Open in new window

0
 

Author Comment

by:RekhaShah
ID: 33613186
To answer your questions,
sory, it was a typo. My Conditions table has 8 fields. I mentioned a few.
 My Datagridview has more then 50 columns. But only a handful of columns can have conditions (users define these conditon s in Condition table.
So for example, I only want to check if CustName, Max size is 50 and minimum size is 5, and can have null. So my  FieldName="CustName",FleMaxSIze=50,fldMinSize=5,FldAllowNull=True. I read these conditions, goto my datagridview, find that column and check its value against these conditions.  Then I want to pick up another column eg. NumOfOrders, It's FldMinVal=0, fldMaxVal=30 and can not allow null... and so on.
After i check all the conditions, i move my cursor to 1st condition in the dataset and move my cursot to the next row in datagrid.

Can you do
Me.NumDataSet.MoveFirst or Me.NumDataSet.MoveNext?
I have figured out alternate way to this solution : is to populate a  enum array with these values and iterate thru that array.
But I would like to know how you iterate thru datasets.
Thanks

0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 250 total points
ID: 33613936
There is no direct method in vb.net that you an navigate like MoveFirst,MoveLast e.t.c there is a similar method named CurrencyManager you may use...
The CurrencyManager depends on BindingContext that a relation with a dataset ...
read this ...http://msdn.microsoft.com/en-us/library/system.windows.forms.currencymanager.aspx
0
 

Author Closing Comment

by:RekhaShah
ID: 33614416
Thank you for your help.
 I just wanted to know if you ca n use movefirst, movelast etc in vb.net .
 It is good to know the currencymanager method. Although, I have already implemented the enum array method for my current project, I will look into it in future.
0
 

Author Comment

by:RekhaShah
ID: 33614427
I am posting my code here , just in case anyone else is interested....


in the module, I have following code to declare the enum array:
Public g_Conditions() As OConditions
    Public Class OConditions
        Public FieldName As String
        Public FldMaxSize As Integer
        Public FldMinSize As Integer
        Public FldMaxVal As Integer
        Public FldMinVal As Integer
        Public FldCanBeNull As Boolean
        Public FldAllowedVal As String
        Public FldType As Integer

        Public Sub New(ByVal sFieldName, ByVal nFldMaxSize, ByVal nFldMinSize, ByVal nFldMaxVal, ByVal nFldMinVal, ByVal bFldCanBeNull, ByVal nFldType, ByVal sFldAllowedVal)
            FieldName = sFieldName
            nFldMaxSize = FldMaxSize
            FldMinSize = nFldMinSize
            FldMaxVal = nFldMaxVal
            FldMinVal = nFldMinVal
            FldCanBeNull = bFldCanBeNull
            FldAllowedVal = sFldAllowedVal
            FldType = nFldType
        End Sub
    End Class

In my form I have the following code to populate the Enum array.


With cmd
                .Connection = cn
                .CommandType = CommandType.StoredProcedure
                .CommandText = "sp_Edits_GetFieldConditions"
                .Parameters.Add(New SqlClient.SqlParameter("OVID", SqlDbType.SmallInt, 4, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, Me.AcctID))
                drCond = .ExecuteReader()
            End With

            Dim J As Integer
            Dim nC As OConditions

            While drCond.Read()
                Dim sFldName As String
                Dim nFldMaxSize As String
                Dim nFldMinSize As Integer
                Dim nFldMaxVal As Integer
                Dim nFldMinVal As Integer
                Dim bFldCanBeNull As Boolean
                Dim nFldType As Integer
                Dim sFldAllowedVal As String

                If Not IsDBNull(drCond("FieldName")) Then sFldName = drCond("FieldName")
                If Not IsDBNull(drCond("FieldSizeMax")) Then nFldMaxSize = drCond("FieldSizeMax")
                If Not IsDBNull(drCond("FieldSizeMin")) Then nFldMinSize = drCond("FieldSizeMin")
                If Not IsDBNull(drCond("FieldValueMin")) Then nFldMinVal = drCond("FieldValueMin")
                If Not IsDBNull(drCond("FieldValueMax")) Then nFldMaxVal = drCond("FieldValueMax")
                If Not IsDBNull(drCond("FieldCanBeNull")) Then bFldCanBeNull = drCond("FieldCanBeNull")
                If Not IsDBNull(drCond("FieldType")) Then nFldType = drCond("FieldType")
                If Not IsDBNull(drCond("FieldValAllowed")) Then sFldAllowedVal = drCond("FieldValAllowed")

                nC = New OConditions(sFldName, nFldMaxSize, nFldMinSize, nFldMaxVal, nFldMinVal, bFldCanBeNull, nFldType, sFldAllowedVal)
                i = i + 1
                ReDim Preserve g_Conditions(i)
                g_Conditions(i) = nC
            End While
            drCond.Close()

Open in new window

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
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…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

861 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