Solved

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

Posted on 2010-09-06
12
1,032 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now