VB.net loop coding question

[Homerboy]

I have the following loop:


For COUNT As Integer = 1 To 5
If (COUNT = 6) Then
Exit For
End If

SQL = "INSERT INTO CALL_AUDIT_SCORES (AUDIT_NO, SECTION, TOPIC_NO, TOPIC_TEXT)" & _
"VALUES ('" & (FILENO.Text + "-" + COLL_NO.Text + "-" + DateTime.Now.ToString("yyyyMMdd")) & "','OPENING','" & COUNT & "','" & OPENING_1_TOPIC.Text & "')"
'Dim Adapter As SqlDataAdapter = New SqlDataAdapter(SQL, cn)
Adapter = New SqlDataAdapter(SQL, cn)
rs = New DataSet
Adapter.Fill(rs, "master")
cn.Close()
Next

But what I want to do is make the “1” in OPENING_1_TOPIC equal to “COUNT”
So as it loops through, I get OPENING_1_TOPIC, OPENING_2_TOPIC, OPENING_3_TOPIC, OPENING_4_TOPIC, and OPENING_5_TOPIC

Ideas?

[PhatoseAlpha]

The obvious solution would be to change the variable OPENING_1_TOPIC, OPENING_2_TOPIC, etc into an array, OPENING_TOPICS(1), ect.

Why are you doing it with separate variables in the first place?

[WannaFly]

There really isnt an easy way to use a variable as part of a variable name. You could using reflection, but I'd guess it's just not worth it.

Your best bet would be to add the values or controls into an array and then just reference their values using COUNT as the index of the array.

Also, You don't need the if statement, COUNT will never go above 5 in this case.

[KLin]

http://www.dotnetperls.com/string-array-vbnet

use a string array, populate string array with opening topic field values

reference string array in sql building code using COUNT to ensure you get the correct value.

[Homerboy]

Hmmm Ok Let me give a little more info here.

I have a VB Form.
In that form there are 5 "Topics" that the user fills in, then hits "submit" and those 5 entires are then INSERTed in SQL. The values of those 5 topics are OPENING_1_TOPIC.Text, OPENING_2_TOPIC.Text etc

[Homerboy]

AH-HA!!!


Dim array() As String = {OPENING_1_TOPIC.Text, OPENING_2_TOPIC.Text}
For Each value As String In array
SQL = "INSERT INTO CALL_AUDIT_SCORES (AUDIT_NO, SECTION, TOPIC_TEXT)" & _
"VALUES ('" & (FILENO.Text + "-" + COLL_NO.Text + "-" + DateTime.Now.ToString("yyyyMMdd")) & "','OPENING','" & value & "')"
'Dim Adapter As SqlDataAdapter = New SqlDataAdapter(SQL, cn)
Adapter = New SqlDataAdapter(SQL, cn)
rs = New DataSet
Adapter.Fill(rs, "master")
cn.Close()
Next

[Homerboy]

Ok... now as I'm learning here... this is the next step.

For each "OPENING_1_TOPIC.Text" there is a corresponding "OPENING_1_ANSWER" how do I make it so that as it loops through the Array, that it is inserting the correct, corresponding ANSWER? into the table?

[Tweak155]

Holy cow this is confusing me. I'm fairly "fluent" in VB and what you're describing to us makes no sense to me.

Rather than JUST show your code, perhaps a brief explanation of your goal would help us help you.

Arrays SOUNDS like the easiest way to do this...

Dim ANSWER_ARRAY() As String = {answer1, answer2, answer3}
Dim QUESTION_ARRAY() As String = {question1, question2, question3}

Now QUESTION_ARRAY(1) will correspond to ANSWER_ARRAY(1)....

It really depends on your end goal here.

EDIT:

And to loop through both:

For COUNT = 1 to MAX_QUESTIONS
someString = "SQL HERE " & QUESTION_ARRAY(COUNT)
someString = "SQL HERE " & ANSWER_ARRAY(COUNT)
Next

[Homerboy]

Originally Posted by Tweak155

Holy cow this is confusing me. I'm fairly "fluent" in VB and what you're describing to us makes no sense to me.

Rather than JUST show your code, perhaps a brief explanation of your goal would help us help you.

Arrays SOUNDS like the easiest way to do this...

Dim ANSWER_ARRAY() As String = {answer1, answer2, answer3}
Dim QUESTION_ARRAY() As String = {question1, question2, question3}


Now QUESTION_ARRAY(1) will correspond to ANSWER_ARRAY(1)....

It really depends on your end goal here.

LOL yeah. I apologize for the confusion... I admit, I'm just vomiting up thoughts here without much explanation/direction.

My end goal is to have a form. Users will have fields to fill in. There will be 5 rows, and 4 columns in each (so 5 "topics", with 4 fields to fill in for each one) resulting in 20 total values.

I need each one of those rows, and all 4 of its values, to be inserted into a corresponding row within a SQL table.

I think I can do this with a two-dimensional array?


Dim Array(5, 4) As String

Array(0, 0) = "Row 1, Column 1"
Array(0, 1) = "Row 1, Column 2"
...
Array(1, 0) = "Row 2, Column 1"
etc

[Tweak155]

If it is 5 x 4 then you'd want Dim Array(5, 4).

Then look at it as Array(ROW, COLUMN)

[Homerboy]

Originally Posted by Tweak155

If it is 5 x 4 then you'd want Dim Array(5, 4).

Then look at it as Array(ROW, COLUMN)

Yeah I edited that (5,1) was just a typo.

[Homerboy]

But what don't still get is how those string values of "Array(0, 0)" and "Array(0, 1)" etc are put into the SQL INSERT statement.


SQL = "INSERT INTO CALL_AUDIT_SCORES (AUDIT_NO, SECTION, TOPIC_TEXT, ANSWER)" & _
"VALUES ('" & (FILENO.Text + "-" + COLL_NO.Text + "-" + DateTime.Now.ToString("yyyyMMdd")) & "','OPENING','" & value & ..............."')"

The "value" is just of that first Array(0, 0) correct?

[Homerboy]

I could just cheat and use a multi-row INSERT statement in the SQL...

[beginner99]

Our old friend bobby tables would like this very much...

If OP does nto get the Joke:

google for SQL Injection. Threads like this explain soooo much...

[Homerboy]

Originally Posted by beginner99

Our old friend bobby tables would like this very much...

If OP does nto get the Joke:

google for SQL Injection. Threads like this explain soooo much...

I'm familiar with SQL injection.
I'm not overly concerned with that in this case as this is an internal VB.net application used by about 2 people.

[KLin]

http://stackoverflow.com/questions/5...stract-factory

You should implement that for executing commands instead of using that adapter.fill stuff.

[nickbits]

#1 Use arrays
#2 Use parameters