EDW_Gideon
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
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?
ASKER
Line 94 in the snippet above. Each Item(i) value is the same.
ASKER
And i does increase in value each loop.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.ToStr ing() & mytf
>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.ToStr
ASKER
Dontmilkthis as I said before, you're a rockstar! Thanks much!!!
MsgBox(newAAR)
show you? Should you not be using
MsgBox(name)