DataTable Loop and String Building

[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.

 

[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

 

[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