Link to home
Start Free TrialLog in
Avatar of RekhaShah

asked on

Iterate through Dataset inside a big For loop in

Hi  Everyone,
I am working on a desktop App in 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
        while not rsconditons.eof
                 'Do some verification
                  sFieldName= rsConditions.fieldname
                  if datagridrow.row(sFieldName)<> rsConditions("FieldName).value then
                  End If
  Next I    

How do i do this in 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

Open in new window

Avatar of John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou
Flag of Greece image

<<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

Make A correction

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

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
                    End If
            End If

Open in new window

Where NumDataset Your Dataset
Avatar of RekhaShah


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
        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
sorry, i left out
 DS.MoveFirst in the beginning of For Loop
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 ....
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"

                      End if 
                    End if
            End If

Open in new window

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.

Avatar of John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou
Flag of Greece image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your help.
 I just wanted to know if you ca n use movefirst, movelast etc in .
 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.
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

Open in new window