Solved

Object variable or with block variable not set

Posted on 2013-11-20
13
488 Views
Last Modified: 2014-06-17
I'm trying to loop through a data set using the reader to assign strings to a few array's but I'm getting "Object variable or with block variable not set".  I am apparently not doing something right and based off stepping through the code it has to do with assigning my variables to an array. (commented below towards the bottom)   Any assistance would be greatly appreciated!
   Function MasterListLD()
        Dim SelStmt, rsUserName, rsCommentString, rsOwnerName, rsCreateTimeStamp, rsED_User_Status, rsED_UserSuspendDate, currentuser As String
        Dim arEmpId, arCreateTS, arUserStatus, arUserSuspendDate
        Dim pf As DbProviderFactory = DbProviderFactories.GetFactory("Teradata.Client.Provider")
        Dim con As DbConnection = pf.CreateConnection()
        'Dim sDataSource As String
        Dim stringBuilder As New Teradata.Client.Provider.TdConnectionStringBuilder()
        'Dim sConnection As [String]
        Dim SqlQuery As StringBuilder = New StringBuilder


        Dim arElements, arPtr As Integer

        stringBuilder.DataSource = Session.Contents("DataSource")
        stringBuilder.UserId = Session.Contents("AdminLogin")
        stringBuilder.Password = Session.Contents("AdminPasswd")
        stringBuilder.PersistSecurityInfo = True
        stringBuilder.SessionMode = "ANSI"

        SqlQuery.Append("Sel * from TABLE")
        SelStmt = SqlQuery.ToString()

        con.ConnectionString = stringBuilder.ConnectionString

        Dim command As DbCommand = con.CreateCommand
        command.CommandText = SelStmt
        command.CommandType = CommandType.Text

        arElements = 0
        arPtr = 0

        Try
            command.CommandTimeout = 600
            con.Open()
            Dim reader As DbDataReader = command.ExecuteReader()


            Do While reader.Read()
                If reader.HasRows Then
                    rsUserName = reader("username").ToString
                    rsCommentString = reader("commentstring").ToString
                    rsOwnerName = reader("ownername").ToString
                    rsCreateTimeStamp = reader("createtimestamp").ToString
                    rsED_User_Status = reader("user_status").ToString
                    rsED_UserSuspendDate = reader("user_suspend_date").ToString

                    If InStr(rsCommentString, "~") <> 0 And _
                           InStr(rsCommentString, "#") <> 0 And _
                           InStr(rsCommentString, "$") <> 0 And _
                           InStr(rsCommentString, "*") <> 0 And _
                           InStr(rsCommentString, "^") <> 0 And _
                           InStr(rsCommentString, "+") <> 0 Then

                        arUser_Id(arPtr) = UCase(rsUserName)  ' ******** FAILS HERE ******** '
                        arCommentString(arPtr) = rsCommentString
                        arOwnerDatabase(arPtr) = rsOwnerName

                        arPtr = arPtr + 1
                    End If
                    reader.NextResult()
                Else
                    reader.NextResult()
                End If
            Loop
            arElements = arPtr - 1
        Catch ex As Exception
            'MsgBox(ex.Message)
            Exit Function
        Finally
            con.Close()
        End Try

Open in new window

0
Comment
Question by:EDW_Gideon
13 Comments
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 39664150
Where do you declare and instantiate arUser_Id ?
0
 

Author Comment

by:EDW_Gideon
ID: 39664159
Sorry did that for the class, I'm new to VB.Net so learning on the fly.

Dim arUser_Id, arCommentString, arOwnerDatabase As Array
0
 
LVL 5

Expert Comment

by:Dontmilkthis
ID: 39664160
edit --- beat me to it

where are you initialising arUser_Id?
have you actually created this array elsewhere?
0
 
LVL 5

Expert Comment

by:Dontmilkthis
ID: 39664209
in the interest of simplicity I'd suggest using a strongly typed list (either int or string).

you can find this class in the System.Collections.Generic namespace.

Dim arUser_Id As Collections.Generic.List(Of String)
        arUser_Id = New Collections.Generic.List(Of String)

Open in new window


or

Dim arUser_Id As Collections.Generic.List(Of Int32)
        arUser_Id = New Collections.Generic.List(Of Int32)

Open in new window



This gives you some simple to use methods like add, remove, removeAt etc. you can also convert the whole thing to an array if you really choose to.

iterating through the whole list is also easy with a for each loop

For Each item As Int32 In arUser_Id
                      'do stuff
        Next

Open in new window

0
 
LVL 7

Expert Comment

by:Ironhoofs
ID: 39664240
Your array declaration is incorrect (see MSDN Creating an Array). I enclose a short example of array and alternative storage objects to give you an idea. Hope this helps!
Dim arPtr As Integer

'Array
Dim arUser_Id As String()
For arPtr = 0 To 9
	ReDim Preserve arUser_Id(arPtr)
	arUser_Id(arPtr) = "user " + arPtr.ToString()
Next

Dim arUser_IdA As New ArrayList
For arPtr = 0 To 9
	arUser_IdA.Add("user " + arPtr.ToString())
Next


Dim lstUserId As New List(Of String)
For arPtr = 0 To 9
	lstUserId.Add("user " + arPtr.ToString())
Next

Open in new window

0
 
LVL 5

Expert Comment

by:Dontmilkthis
ID: 39664358
To discuss your issue more specifically, the Array class is only used to work with existing Arrays. It's not meant to be instantiated as a variable.

It also serves as the base class for some other Array type classes.

closer to what you were trying to do would be the ArrayList class, which acts much the same was as the strongly type lists I mentioned above, however it works with objects in their base form. meaning you have to cast them to the expected type before working with them later.

you also could have created a String() variable, which would look something like
dim arUser_Id() = String

Open in new window


this creates an array of Strings, however it doesn't have any size. so it's only useful for assigning an existing String array to it, or you can ReDim it to a set size... this involves a bit of manual work to constantly resize the array each time you wanted to add a new item.

if you know up front the exact number of items to be in the array you can assign this value at the time it's instantiated.

while typing this, Ironhoofs has provided more examples, so refer to that.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:EDW_Gideon
ID: 39666721
Ironhoofs,
"For arPtr = 0 To 9" the 9 says there's going to be a total of 9 entries in the array, right?  I don't think this is going to work because I don't know how many entries the table I'm pulling the list from has.  Can I use a count variable for the 9 to have more entries?  (Sorry haven't ever used array's and I'm trying to grasp how this works)  I read about collections and it sounds like that is the option I will need to use. From MSDN:

"Arrays are most useful for creating and working with a fixed number of strongly typed objects. Collections provide a more flexible way to work with groups of objects. Unlike arrays, the group of objects that you work with can grow and shrink dynamically as the needs of the application change."

Dontmilkthis,
I tried both of your suggestions and unless I'm doing something wrong both failed.  When I used the first suggestion at the same failure point I get:

"Object reference not set to an instance of an object."

I declared at the class level like this:
Dim arUser_Id As Collections.Generic.List(Of String)

Open in new window

When I tried to include:
arUser_Id = New Collections.Generic.List(Of String)

Open in new window

It didn't like the fact that I didn't it saying "Declaration expected" at arUser_ID.

I also tried:
dim arUser_Id() = String

Open in new window

The IDE is saying ". Expected" after the "= String" portion of the Dim.  I will read the MSDN array article again and try to see what I'm doing wrong.  When I last read it, it made me feel as though you have to declare the number of entries in the array before using it and I since I don't know how many entries and the number will change over time I felt like the method mentioned in the article didn't fit.  Perhaps the variable idea I mentioned under Ironhoof's suggestion would work but just not sure.  Thank you all for your time and assistance!  Please let me know any ideas you may have or anything I've perhaps done wrong.
0
 

Author Comment

by:EDW_Gideon
ID: 39667063
Dontmilkthis,

I think I figured out your example of collection...  Here's the code I'm using, I'm getting the following error now:

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

Imports System.Collections.Generic

Partial Class AlterUserStat
    Inherits System.Web.UI.Page
    'Dim arUser_Id, arCommentString, arOwnerDatabase As Array
    Dim arEmpId As Collections.Generic.List(Of String)
   Function MasterListLD()
        Dim SelStmt, rsUserName, rsCommentString, rsOwnerName, rsCreateTimeStamp, rsED_User_Status, rsED_UserSuspendDate, currentuser As String
        Dim arEmpId, arCreateTS, arUserStatus, arUserSuspendDate
        Dim pf As DbProviderFactory = DbProviderFactories.GetFactory("Teradata.Client.Provider")
        Dim con As DbConnection = pf.CreateConnection()
        'Dim sDataSource As String
        Dim stringBuilder As New Teradata.Client.Provider.TdConnectionStringBuilder()
        'Dim sConnection As [String]
        Dim SqlQuery As StringBuilder = New StringBuilder
        arUser_Id = New Collections.Generic.List(Of String)


        Dim arElements, arPtr As Integer

        stringBuilder.DataSource = Session.Contents("DataSource")
        stringBuilder.UserId = Session.Contents("AdminLogin")
        stringBuilder.Password = Session.Contents("AdminPasswd")
        stringBuilder.PersistSecurityInfo = True
        stringBuilder.SessionMode = "ANSI"

        SqlQuery.Append("Sel * from TABLE")
        SelStmt = SqlQuery.ToString()

        con.ConnectionString = stringBuilder.ConnectionString

        Dim command As DbCommand = con.CreateCommand
        command.CommandText = SelStmt
        command.CommandType = CommandType.Text

        arElements = 0
        arPtr = 0

        Try
            command.CommandTimeout = 600
            con.Open()
            Dim reader As DbDataReader = command.ExecuteReader()


            Do While reader.Read()
                If reader.HasRows Then
                    rsUserName = reader("username").ToString
                    rsCommentString = reader("commentstring").ToString
                    rsOwnerName = reader("ownername").ToString
                    rsCreateTimeStamp = reader("createtimestamp").ToString
                    rsED_User_Status = reader("user_status").ToString
                    rsED_UserSuspendDate = reader("user_suspend_date").ToString

                    If InStr(rsCommentString, "~") <> 0 And _
                           InStr(rsCommentString, "#") <> 0 And _
                           InStr(rsCommentString, "$") <> 0 And _
                           InStr(rsCommentString, "*") <> 0 And _
                           InStr(rsCommentString, "^") <> 0 And _
                           InStr(rsCommentString, "+") <> 0 Then

                        arUser_Id(arPtr) = UCase(rsUserName)  ' ******** FAILS HERE ******** '
                        arCommentString(arPtr) = rsCommentString
                        arOwnerDatabase(arPtr) = rsOwnerName

                        arPtr = arPtr + 1
                    End If
                    reader.NextResult()
                Else
                    reader.NextResult()
                End If
            Loop
            arElements = arPtr - 1
        Catch ex As Exception
            'MsgBox(ex.Message)
            Exit Function
        Finally
            con.Close()
        End Try 
End Class

Open in new window

0
 
LVL 5

Accepted Solution

by:
Dontmilkthis earned 500 total points
ID: 39667501
Since you're using arUser_Id as a list of strings, you no longer assign values to it like you would an array.

instead, you can just add items to the list by
arUser_Id.Add(UCase(rsUserName))

Open in new window

you'll need to do the same for the arCommentString and arOwnerDatabase assigning statements.

Where you're looking for the number of items in the array, you can now just use arUser_Id.Count and it'll tell you how many.

I also believe you've got the reader.hasrows and reader.read around the wrong way.

since your query is a single command (as opposed to a batch) the following should be closer to what you want.

	If reader.HasRows Then
		Do While reader.Read()
			
			rsUserName = reader("username").ToString
			rsCommentString = reader("commentstring").ToString
			rsOwnerName = reader("ownername").ToString
			rsCreateTimeStamp = reader("createtimestamp").ToString
			rsED_User_Status = reader("user_status").ToString
			rsED_UserSuspendDate = reader("user_suspend_date").ToString

			If InStr(rsCommentString, "~") <> 0 And _
				   InStr(rsCommentString, "#") <> 0 And _
				   InStr(rsCommentString, "$") <> 0 And _
				   InStr(rsCommentString, "*") <> 0 And _
				   InStr(rsCommentString, "^") <> 0 And _
				   InStr(rsCommentString, "+") <> 0 Then

				arUser_Id.Add(UCase(rsUserName))
				arCommentString.Add(rsCommentString)
				arOwnerDatabase.Add(rsOwnerName)					
			End If
		Loop
	Else
		'no results!
	End If
		arElements = arUser_Id.Count

Open in new window


also, since these arrays all contain data that has a requirement on the others being in sync, you're better off creating your own data type, and creating a single list of this data type.

the simplest way (and i'm sure someone will tell me/you how to do it "right") would be to make your own class like this
Public Class userInfo
    Public username As String
    Public comment As String
    Public ownerName As String

End Class

Open in new window

change your list variable to be a list of your new class
        Dim lstUsers As List(Of userInfo)
        lstUsers = New List(Of userInfo)()

Open in new window


then in your loop, you can now set up a single variable of your new type and then add it to the list. so your loop becomes something like this
dim newUser as userInfo
		Do While reader.Read()
			newUser = new userInfo()
			newUser.username = UCase(reader("username").ToString)
			newUser.comment = reader("commentstring").ToString
			newUser.ownerName = reader("ownername").ToString
			'rsCreateTimeStamp = reader("createtimestamp").ToString
			'rsED_User_Status = reader("user_status").ToString
			'rsED_UserSuspendDate = reader("user_suspend_date").ToString

			If InStr(newUser.comment, "~") <> 0 And _
				   InStr(newUser.comment, "#") <> 0 And _
				   InStr(newUser.comment, "$") <> 0 And _
				   InStr(newUser.comment, "*") <> 0 And _
				   InStr(newUser.comment, "^") <> 0 And _
				   InStr(newUser.comment, "+") <> 0 Then

				lstUsers.Add(newUser))				
			End If
		Loop

Open in new window

0
 

Author Comment

by:EDW_Gideon
ID: 39690557
How do I recall a specific entry out of the list?  - DISREGARD

I figured it out.  (lstUsers)
0
 

Author Closing Comment

by:EDW_Gideon
ID: 39692559
You're a rock star!  Thanks for your help and patience!
0
 

Author Comment

by:EDW_Gideon
ID: 40140212
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

23 Experts available now in Live!

Get 1:1 Help Now