DataTable Loop and String Building

28 Apr 2006 8:50 PM

ILCSP

Hi, I have a sql table containing the answers for some tests. The
information in this table is presented vertically and I need to create
strings with them. I know how to read the data in VB.Net and use a
StreamWriter to build the strings. However, the problem lies with the
reading of each row.

Most of the test takers don't give answers to ALL of the items in a
test, but those unanswered items need to be accounted for by using a
comma and a empty space.

Each row has a TestID, a TestType, a TestItemNumber, an Answer and a
ItemMax. I'm providing a small sample at the end of this post.

The fields that change in every row for each test are the
TestItemNumber and the answers given by the test takers.

In order to create a string, I need the TestID, the TestType, and then
I have to look at the ItemMax number for this test. Let's say is 13.
Therefore, I have to check Items from 1 to 13. If the first
TestItemNumber answered for this test is not actually 1, but let's say
3, I still need to place commas with empty spaces replacing
TestItemNumber 1 and 2 and then have the answer for TestItemNumber 3
and so on until I am done with the 13 items and start a new string.

Here's my pseudo code.

Dim dtItemC As New DataTable
dtItemConversion.Fill(dtItemC)
Dim dtrow As DataRow

' creating some variables and storing the row values
Dim i as integer
Dim ExID, ExTp, ExItNum, Resp, ItMax, CurStr As String

WHILE dtItemC NOT EOF
    'I created 2 text boxes: txtTestID and txtType and I place their
values from the first row there
    txtTestID = dtrow("TestID").
    txtType = dtrow("TestType")

    ' set variables to row data values
    ExID = dtrow("TestID")
    ExTp = dtrow("TestType")
    ExItNum = dtrow("TestItemNumber")
    Resp = dtrow("Answer")
    ItMax = dtrow("ItemMax")

    'this is the beginning of a string (TestID, TestType)
    CurStr = ExID & ", " & Extp

    ' here's where it gets confusing
    ' this is where I start a new string
    DO WHILE (ExID = txtTestID) AND (ExTp = txtType) AND (NOT dtItemC.EOF)
        FOR i = from 1 to ItMax
        If i = ExItNum THEN
            CurStr = CurStr & "," & Resp
            'Go to the next row and get the next ItemNumber and Answer
            ExItNum = dtrow("TestItemNumber")
            Resp = dtrow("Answer")
        'On the other hand..
        ELSE IF i = ExItNum + 1 THEN
            'Go to the next row and get the next ItemNumber and Answer
        ExItNum = dtrow("TestItemNumber")
        Resp = dtrow("Answer")
        ELSE
            ' unanswered item.. Add comma and space
            CurStr = CurStr = & ", "
        END

    'Get the TestID, TestType, and ItemMax
    ExId = dtrow("TestID")
    ExTp = dtrow("TestType")
    ItMax = dtrow("ItemMax")

    ' Change the Text Boxes values
    txtTestID = dtrow("TestID")
    txtType = dtrow("TestType")

    'Write the string created
    sb = New StringBuilder
      ' append current string
    sb.Append(CurStr)

    ' clear the curStr
    CurrStr = ""

    'Set the new CurStr values (beginning of a new Exam string)
    CurStr = ExID & ", " & Extp
    END WHILE
END

Issues:
How to I word the code for When the DataTable is not EOF?
And what do I use for "Go to the Next Row"?

Here's a small sample of the Data I have:

TESTID    TestType    TestItemNumber    Answer    ItemMax
632    DD    1    A    10
632    DD    2    C    10
632    DD    4    C    10
632    DD    6    C    10
632    DD    7    A    10
632    DD    8    C    10
632    DD    9    B    10
121    AA    2    B    5
121    AA    3    E    5
121    AA    4    D    5
121    AA    5    D    5
987    BB    1    C    10
987    BB    2    A    10
987    BB    3    C    10
987    BB    6    D    10
987    BB    7    B    10
987    BB    8    D    10
987    BB    9    A    10
987    BB    10    C    10

Thanks for all your help. I really appreciate it.

28 Apr 2006 10:24 PM

tomb

I would use a counter and a pointer. The pointer tells you the item
MAX, the counter gets started at 1 each time you reach a new TESTID,
then tells you which item of the current group you are looking for.
Query the table order by TestID, TestItemNumber. As you loop through
the records, reset the pointer for each new TestID, and reset the
counter to 1. If the current TestItemNumber equals the current counter,
use the values in the record and move to the next one. If it doesn't
equal the current counter, you know you need a blank value, then
increment the counter by one. When counter equals pointer, you can
assume the next record will be a new TestID.

Tom

IL***@NETZERO.NET wrote:

Show quoteHide quote

>Hi, I have a sql table containing the answers for some tests. The
>information in this table is presented vertically and I need to create
>strings with them. I know how to read the data in VB.Net and use a
>StreamWriter to build the strings. However, the problem lies with the
>reading of each row.
>
>Most of the test takers don't give answers to ALL of the items in a
>test, but those unanswered items need to be accounted for by using a
>comma and a empty space.
>
>Each row has a TestID, a TestType, a TestItemNumber, an Answer and a
>ItemMax. I'm providing a small sample at the end of this post.
>
>The fields that change in every row for each test are the
>TestItemNumber and the answers given by the test takers.
>
>In order to create a string, I need the TestID, the TestType, and then
>I have to look at the ItemMax number for this test. Let's say is 13.
>Therefore, I have to check Items from 1 to 13. If the first
>TestItemNumber answered for this test is not actually 1, but let's say
>3, I still need to place commas with empty spaces replacing
>TestItemNumber 1 and 2 and then have the answer for TestItemNumber 3
>and so on until I am done with the 13 items and start a new string.
>
>Here's my pseudo code.
>
>Dim dtItemC As New DataTable
>dtItemConversion.Fill(dtItemC)
>Dim dtrow As DataRow
>
>' creating some variables and storing the row values
>Dim i as integer
>Dim ExID, ExTp, ExItNum, Resp, ItMax, CurStr As String
>
>WHILE dtItemC NOT EOF
>    'I created 2 text boxes: txtTestID and txtType and I place their
>values from the first row there
>    txtTestID = dtrow("TestID").
>    txtType = dtrow("TestType")
>
>    ' set variables to row data values
>    ExID = dtrow("TestID")
>    ExTp = dtrow("TestType")
>    ExItNum = dtrow("TestItemNumber")
>    Resp = dtrow("Answer")
>    ItMax = dtrow("ItemMax")
>
>    'this is the beginning of a string (TestID, TestType)
>    CurStr = ExID & ", " & Extp
>
>    ' here's where it gets confusing
>    ' this is where I start a new string
>    DO WHILE (ExID = txtTestID) AND (ExTp = txtType) AND (NOT dtItemC.EOF)
>        FOR i = from 1 to ItMax
>        If i = ExItNum THEN
>            CurStr = CurStr & "," & Resp
>            'Go to the next row and get the next ItemNumber and Answer
>            ExItNum = dtrow("TestItemNumber")
>            Resp = dtrow("Answer")
>        'On the other hand..
>        ELSE IF i = ExItNum + 1 THEN
>            'Go to the next row and get the next ItemNumber and Answer
>        ExItNum = dtrow("TestItemNumber")
>         Resp = dtrow("Answer")
>        ELSE
>            ' unanswered item.. Add comma and space
>            CurStr = CurStr = & ", "
>        END
>
>    'Get the TestID, TestType, and ItemMax
>    ExId = dtrow("TestID")
>    ExTp = dtrow("TestType")
>    ItMax = dtrow("ItemMax")
>
>    ' Change the Text Boxes values
>    txtTestID = dtrow("TestID")
>    txtType = dtrow("TestType")
>
>    'Write the string created
>     sb = New StringBuilder
>      ' append current string
>    sb.Append(CurStr)
>
>    ' clear the curStr
>    CurrStr = ""
>
>    'Set the new CurStr values (beginning of a new Exam string)
>    CurStr = ExID & ", " & Extp
>    END WHILE
>END
>
>Issues:
>How to I word the code for When the DataTable is not EOF?
>And what do I use for "Go to the Next Row"?
>
>
>Here's a small sample of the Data I have:
>
>TESTID    TestType    TestItemNumber    Answer    ItemMax
>632    DD    1    A    10
>632    DD    2    C    10
>632    DD    4    C    10
>632    DD    6    C    10
>632    DD    7    A    10
>632    DD    8    C    10
>632    DD    9    B    10
>121    AA    2    B    5
>121    AA    3    E    5
>121    AA    4    D    5
>121    AA    5    D    5
>987    BB    1    C    10
>987    BB    2    A    10
>987    BB    3    C    10
>987    BB    6    D    10
>987    BB    7    B    10
>987    BB    8    D    10
>987    BB    9    A    10
>987    BB    10    C    10
>
>
>Thanks for all your help. I really appreciate it.
>
>
>

29 Apr 2006 8:00 AM

Cor Ligthert [MVP]

Hi,

A datatable is just an collection therefore

for i as integer = 0 to dt.rows.count -1 'gives you all rows and then
               if dt.rows(i)(0).ToString <> "" then 'is the first field
                'sb concat
               if dt.rows(i).("mycolumname").ToString <> "" ' is the field
with the name ..
               etc.

               sb.append(vbcrlf) 'if you want all kind of rows.
next

That should be all, so what is the problem.

Cor

<IL***@NETZERO.NET> schreef in bericht
Show quoteHide quote

news:1146257435.704812.115770@j73g2000cwa.googlegroups.com...
>
>
> Hi, I have a sql table containing the answers for some tests. The
> information in this table is presented vertically and I need to create
> strings with them. I know how to read the data in VB.Net and use a
> StreamWriter to build the strings. However, the problem lies with the
> reading of each row.
>
> Most of the test takers don't give answers to ALL of the items in a
> test, but those unanswered items need to be accounted for by using a
> comma and a empty space.
>
> Each row has a TestID, a TestType, a TestItemNumber, an Answer and a
> ItemMax. I'm providing a small sample at the end of this post.
>
> The fields that change in every row for each test are the
> TestItemNumber and the answers given by the test takers.
>
> In order to create a string, I need the TestID, the TestType, and then
> I have to look at the ItemMax number for this test. Let's say is 13.
> Therefore, I have to check Items from 1 to 13. If the first
> TestItemNumber answered for this test is not actually 1, but let's say
> 3, I still need to place commas with empty spaces replacing
> TestItemNumber 1 and 2 and then have the answer for TestItemNumber 3
> and so on until I am done with the 13 items and start a new string.
>
> Here's my pseudo code.
>
> Dim dtItemC As New DataTable
> dtItemConversion.Fill(dtItemC)
> Dim dtrow As DataRow
>
> ' creating some variables and storing the row values
> Dim i as integer
> Dim ExID, ExTp, ExItNum, Resp, ItMax, CurStr As String
>
> WHILE dtItemC NOT EOF
> 'I created 2 text boxes: txtTestID and txtType and I place their
> values from the first row there
> txtTestID = dtrow("TestID").
> txtType = dtrow("TestType")
>
> ' set variables to row data values
> ExID = dtrow("TestID")
> ExTp = dtrow("TestType")
> ExItNum = dtrow("TestItemNumber")
> Resp = dtrow("Answer")
> ItMax = dtrow("ItemMax")
>
> 'this is the beginning of a string (TestID, TestType)
> CurStr = ExID & ", " & Extp
>
> ' here's where it gets confusing
> ' this is where I start a new string
> DO WHILE (ExID = txtTestID) AND (ExTp = txtType) AND (NOT dtItemC.EOF)
> FOR i = from 1 to ItMax
> If i = ExItNum THEN
> CurStr = CurStr & "," & Resp
> 'Go to the next row and get the next ItemNumber and Answer
> ExItNum = dtrow("TestItemNumber")
> Resp = dtrow("Answer")
> 'On the other hand..
> ELSE IF i = ExItNum + 1 THEN
> 'Go to the next row and get the next ItemNumber and Answer
> ExItNum = dtrow("TestItemNumber")
> Resp = dtrow("Answer")
> ELSE
> ' unanswered item.. Add comma and space
> CurStr = CurStr = & ", "
> END
>
> 'Get the TestID, TestType, and ItemMax
> ExId = dtrow("TestID")
> ExTp = dtrow("TestType")
> ItMax = dtrow("ItemMax")
>
> ' Change the Text Boxes values
> txtTestID = dtrow("TestID")
> txtType = dtrow("TestType")
>
> 'Write the string created
> sb = New StringBuilder
> ' append current string
> sb.Append(CurStr)
>
> ' clear the curStr
> CurrStr = ""
>
> 'Set the new CurStr values (beginning of a new Exam string)
> CurStr = ExID & ", " & Extp
> END WHILE
> END
>
> Issues:
> How to I word the code for When the DataTable is not EOF?
> And what do I use for "Go to the Next Row"?
>
>
> Here's a small sample of the Data I have:
>
> TESTID TestType TestItemNumber Answer ItemMax
> 632 DD 1 A 10
> 632 DD 2 C 10
> 632 DD 4 C 10
> 632 DD 6 C 10
> 632 DD 7 A 10
> 632 DD 8 C 10
> 632 DD 9 B 10
> 121 AA 2 B 5
> 121 AA 3 E 5
> 121 AA 4 D 5
> 121 AA 5 D 5
> 987 BB 1 C 10
> 987 BB 2 A 10
> 987 BB 3 C 10
> 987 BB 6 D 10
> 987 BB 7 B 10
> 987 BB 8 D 10
> 987 BB 9 A 10
> 987 BB 10 C 10
>
>
> Thanks for all your help. I really appreciate it.
>