Link to home
Start Free TrialLog in
Avatar of EDW_Gideon
EDW_GideonFlag for United States of America

asked on

Issue with List in loop duplicating results

I have a gridview that I've added a checkbox to that lists a DB users current access rights...  I found the following code to see which boxes have checks and which don't and have used some code from this previous question (https://www.experts-exchange.com/questions/28299823/Object-variable-or-with-block-variable-not-set.html) to compile a list of the database names checked in the gridview.  Unfortunately when I try to loop through the list the same name is repeated even when I've confirmed (I added the MsgBox for troubleshooting) the names being added to the list are unique and the increment var is changing.  What am I missing here?  Thanks!



Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Collections.Generic
Imports System.Net
Imports System.Text
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Data.Common
Imports Teradata.Net.Security
Imports Teradata.Client.Provider
Imports Teradata.Client.Provider.TdConnection
Imports System.Data.Common.DbConnectionStringBuilder
Imports System.Data.Common.DbTransaction
Imports System.Security.AccessControl
Imports System.Security.Principal
Imports System.Net.Mail

Partial Class Maintain
  Inherits System.Web.UI.Page
  Dim newUser As userInfo
  Dim lstUsers As List(Of userInfo)
  Dim newUCAR As userCAR
  Dim lstUCAR As List(Of userCAR)
  Dim newAAR As userAAR
  Dim lstAAR As List(Of userAAR)

  <ThemeableAttribute(False)> _
  Public Overridable Property AutoPostBack As Boolean

Protected Sub lbEXE_Click(sender As Object, e As System.EventArgs) Handles lbEXE.Click
    Dim pf As DbProviderFactory = DbProviderFactories.GetFactory("Teradata.Client.Provider")
    Dim con As DbConnection = pf.CreateConnection()
    Dim command As DbCommand = con.CreateCommand
    Dim stringBuilder As New Teradata.Client.Provider.TdConnectionStringBuilder()
    Dim SqlQry As StringBuilder = New StringBuilder
    Dim i As Integer = 0
    lstUCAR = New List(Of userCAR)()
    lstAAR = New List(Of userAAR)()

      If rblGROpt.SelectedIndex = 0 Then
        lstAAR.Clear()
        myop = "Grant "
        mytf = " to "
        Dim newAAR As New userAAR()
        For Each row As GridViewRow In gvAAR.Rows
          If row.RowType = DataControlRowType.DataRow Then
            Dim chkRow As CheckBox = TryCast(row.Cells(0).FindControl("cbSel"), CheckBox)
            If chkRow.Checked Then
              Dim name As String = row.Cells(1).Text
              newAAR.objnm = name
              lstAAR.Add(newAAR)
              MsgBox(newAAR)   ' ADDED THIS TO TROUBLESHOOT (Confirmed Each new item is unique)
            End If
          End If
        Next
        i = lstAAR.Count - 1
      Else
        lstUCAR.Clear()
        myop = "Revoke "
        mytf = " from "
        Dim newUCAR As New userCAR()
        For Each row As GridViewRow In gvUCAR.Rows
          If row.RowType = DataControlRowType.DataRow Then
            Dim chkRow As CheckBox = TryCast(row.Cells(0).FindControl("cbSel"), CheckBox)
            If chkRow.Checked Then
              Dim name As String = row.Cells(1).Text
              Dim tbl As String = row.Cells(2).Text
              Dim Right As String = row.Cells(3).Text
              newUCAR.databasenm = name
              newUCAR.tablenm = tbl
              newUCAR.accessrts = Right
              lstUCAR.Add(newUCAR)
            End If
          End If
        Next
        i = lstUCAR.Count
      End If


      For i = 0 To i - 1
        If rblGROpt.SelectedIndex = 0 Then
          SelStmt = myop & " Select on "
          SelStmt = SelStmt & lstAAR.Item(i).objnm.ToString() & mytf  '  For some reason each "Item".objnm is showing the same value even though 'i' is changing value.
          dbtbl = lstAAR.Item(i).objnm.ToString()
        Else
          If InStr(lstUCAR.Item(i).databasenm, "_QM") = False Then
            SelStmt = myop & lstUCAR.Item(i).accessrts.ToString & " on "
            SelStmt = SelStmt & lstUCAR.Item(i).databasenm.ToString()
            dbtbl = lstUCAR.Item(i).databasenm.ToString()
            If lstUCAR.Item(i).tablenm.ToString <> "All" Then
              SelStmt = SelStmt & "." & lstUCAR.Item(i).tablenm.ToString()
              dbtbl = dbtbl & "." & lstUCAR.Item(i).tablenm.ToString()
            End If
            SelStmt = SelStmt & mytf
          Else
            GoTo NextLine
          End If
        End If

      For i = 0 To i - 1
        If rblGROpt.SelectedIndex = 0 Then
          SelStmt = myop & " Select on "
          SelStmt = SelStmt & lstAAR.Item(i).objnm.ToString() & mytf
          dbtbl = lstAAR.Item(i).objnm.ToString()
        Else
          If InStr(lstUCAR.Item(i).databasenm, "_QM") = False Then
            SelStmt = myop & lstUCAR.Item(i).accessrts.ToString & " on "
            SelStmt = SelStmt & lstUCAR.Item(i).databasenm.ToString()
            dbtbl = lstUCAR.Item(i).databasenm.ToString()
            If lstUCAR.Item(i).tablenm.ToString <> "All" Then
              SelStmt = SelStmt & "." & lstUCAR.Item(i).tablenm.ToString()
              dbtbl = dbtbl & "." & lstUCAR.Item(i).tablenm.ToString()
            End If
            SelStmt = SelStmt & mytf
          Else
            GoTo NextLine
          End If
        End If

        SelStmt = SelStmt & Session.Contents("MUID").ToString & ";"

        command.CommandText = SelStmt
        command.CommandType = CommandType.Text
        'command.CommandTimeout = 600

        Try
          command.ExecuteNonQuery()
        Catch ex As Exception
          MyMsg = "Grant/Revoke Failed:" & vbCrLf & ex.Source & vbCrLf & vbCrLf & ex.Message
          GenerateError(MyMsg)
          con.Close()
          Return
        End Try
      Next i
    End If
  End Sub


  Public Class userCAR
    Public databasenm As String
    Public tablenm As String
    Public accessrts As String
  End Class

  Public Class userAAR
    Public objnm As String
  End Class
End Class

Open in new window

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

What does

MsgBox(newAAR)

show you? Should you not be using

MsgBox(name)
Avatar of EDW_Gideon

ASKER

Sorry I must have deleted some of that line, it should read "newAAR.objnm.ToString" not just new AAR...  I have tried "name" and both name and the newAAR above are displaying a unique value for each loop.
And where do you find out that it contains duplicates?
Line 94 in the snippet above.  Each Item(i) value is the same.
And i does increase in value each loop.
ASKER CERTIFIED SOLUTION
Avatar of Dontmilkthis
Dontmilkthis
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Line 91 in your code

>For i = 0 To i - 1

You need to use another variable

Change it to

For j = 0 To i - 1

and then change all references to i as the loop variable to j including changing line 94 to

SelStmt = SelStmt & lstAAR.Item(j).objnm.ToString() & mytf
Dontmilkthis as I said before, you're a rockstar!  Thanks much!!!