VB.net loop coding question

Discussion in 'Programming' started by Homerboy, Nov 21, 2012.

  1. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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?
     
  2. PhatoseAlpha

    PhatoseAlpha Platinum Member

    Joined:
    Apr 10, 2005
    Messages:
    2,120
    Likes Received:
    0
    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?
     
  3. WannaFly

    WannaFly Platinum Member

    Joined:
    Jan 14, 2003
    Messages:
    2,811
    Likes Received:
    0
    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.
     
  4. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,330
    Likes Received:
    3
  5. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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
     
  6. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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
     
  7. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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?
     
  8. Tweak155

    Tweak155 Lifer

    Joined:
    Sep 23, 2003
    Messages:
    10,296
    Likes Received:
    18
    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
     
    #8 Tweak155, Nov 21, 2012
    Last edited: Nov 21, 2012
  9. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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
     
    #9 Homerboy, Nov 21, 2012
    Last edited: Nov 21, 2012
  10. Tweak155

    Tweak155 Lifer

    Joined:
    Sep 23, 2003
    Messages:
    10,296
    Likes Received:
    18
    If it is 5 x 4 then you'd want Dim Array(5, 4).

    Then look at it as Array(ROW, COLUMN)
     
  11. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    Yeah I edited that (5,1) was just a typo.
     
  12. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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?
     
  13. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    I could just cheat and use a multi-row INSERT statement in the SQL...
     
  14. beginner99

    beginner99 Platinum Member

    Joined:
    Jun 2, 2009
    Messages:
    2,832
    Likes Received:
    4
    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...
     
  15. Homerboy

    Homerboy Lifer

    Joined:
    Mar 1, 2000
    Messages:
    23,476
    Likes Received:
    72
    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.
     
  16. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,330
    Likes Received:
    3
  17. nickbits

    nickbits Diamond Member

    Joined:
    Mar 10, 2008
    Messages:
    4,125
    Likes Received:
    0
    #1 Use arrays
    #2 Use parameters
     
Loading...