Last post Dec 14, 2009 11:41 AM by atconway
Dec 10, 2009 11:33 AM|purplepint|LINK
Is it at all possible to declare variables dynamically?
Something like Dim Answers & i As ArrayList
What I'm trying to achieve is putting one or more answers to a question into an array. Or should I really be looking at a mult-dimensional array to store the question number and answer? How would I add to and access a multi-dimentional array?
Anyway, here's the section of code:
Dim SqlGetAnswers As String = "SELECT ALK_Values.ALKValue FROM ALK_Selected INNER JOIN ALK_Values ON ALK_Selected.ALKID = ALK_Values.ALKID WHERE (ALK_Selected.AssessmentID = @AssessmentID) AND (ALK_Selected.TypeID = @TypeID)"
Dim GetAnswersCmd As New SqlCommand(SqlGetAnswers, con)
For i = 0 To NoQuestions - 1
Dim Answers As ArrayList
GetAnswersCmd.Parameters.AddWithValue("@TypeID", i + 1)
Dim ObjDataReaderGetAnswers As SqlDataReader = GetAnswersCmd.ExecuteReader(Data.CommandBehavior.CloseConnection)
Dec 10, 2009 12:47 PM|AceCorban|LINK
I'm not sure I fully understand your question. Your code looks correct in that it declares an ArrayList and adds items as it finds them in your database query. I don't think you'd need a multidimensional array since you can use the index of the ArrayList
as your question number (well, you'd have to add one to the index to get the question number). What type of data is going into the array?
Dec 11, 2009 04:37 AM|purplepint|LINK
The main problem is that some questions can have more than one answer, making it difficult to relate answers back to a question. For now I'm having to query the answers for each question but it's not an ideal solution, as you can imagine, there are 30 odd
Dec 11, 2009 12:57 PM|AceCorban|LINK
Ahh, I see. Yeah, that makes sense. What I'd consider doing is implementing an "Answer" class. Populate that class with whatever fields you need (most notably the text and the question number). Then you can do something like this:
Answers.Add(new Answer(ObjDataReaderGetAnswers("QuestionNum"), ObjDataReaderGetAnswers("ALKValue"))
Make sure you do an OrderBy the question number
Dec 14, 2009 11:41 AM|atconway|LINK
Creating variable names at runtime can be done using reflection, but it is a bit cryptic, and based on your description there is a better way to solve your problem. If you want to read a thread similar to your question, take a look to the following
link for informational purposes:
How do i set a string as a variable name at runtime?
A better way to solve your task is probably to create a class as described as before, and then using a collection of objects to be sent off to your procedure in your original post that can be iterated through and persisted to the database. Your sample class
might look like the following:
Public Class ClassTest
Private mQuestionID As Integer = 0
Private mQuestion As String = String.Empty
Private mAnswers As New ArrayList()
Public Property QuestionID() As Integer
Set(ByVal value As Integer)
mQuestionID = value
Public Property Question() As String
Set(ByVal value As String)
mQuestion = value
Public Property Answers() As ArrayList
Set(ByVal value As ArrayList)
mAnswers = value
Now notice the property on the class named: 'Answers'; it is of type 'ArrayList' which is one of many ways to hold 1:many values. Therefore each time you create a new instance of this class to hold your question and answer information, you can iterate through
each 'ClassTest' object, and then through each 'answer' within that object. This will solve in a much cleaner manner your original question that you were going down the path of using a multi-dimensional array. The main problem with that (other than location
is a battleship style 'Row 3 Column 6' nightmare) is you will not know the bounds of the array so you would have to dynamically redimension it all the time and it would become a mess.
I recommend combining the above sample class and extending it for your needs, and then reading over the following on using object collections. This should get you what you need to solve your issue, and have a scalable solution.
.NET Object Collections Using Generics 101:
Hope this helps!