Solved

Object variable or with block variable not set

Posted on 2013-11-20
13
497 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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