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. 2 9161
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: 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.
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
news:11**********************@j73g2000cwa.googlegr oups.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dudi |
last post by:
I am encountering the following strange problem which causes the application
to use more memory then it should.
I have 5 database tables. lets pretend I want to load each one of them into
a...
|
by: randy |
last post by:
Hello all,
I have a DataTable which I am building column by column and adding rows
after each new column. The DataTable columns match the columns in my
database table. I'm building the...
|
by: Stefan Turalski \(stic\) |
last post by:
Hi,
I'm wondering if there is a way to send a method parametrs by ref when
DataTabel is a type of this value ?
I done some sort of select over DataTable columns, just by removing them
froma...
|
by: DraguVaso |
last post by:
Hi,
I need a FAST way to put the content of a file in a datatable (one record
for each line in the file).
I have a routine for it, but it takes me too much time (2-5 seconds for each
file) to...
|
by: Gene Hubert |
last post by:
I'm using DataTable.ImportRow to move data from one datatable to
another...
Dim dt, dtTarget As DataTable
Dim dr As DataRow
dt = DirectCast(Me.DataSource, DataTable)
dtTarget = dt.Clone...
| |
by: Jayne |
last post by:
I'm building a Web Service with the CLR 2.0 that returns a DataTable but I
get the followng error:
System.InvalidOperationException: There was an error generating the XML
document. --->...
|
by: D. Shane Fowlkes |
last post by:
I have a function that is called in page_load and the purpose of this
function is to look up basic data in a MSSQL table and return it in the form
of a datatable. The page_load will read the data...
|
by: dauphian |
last post by:
Hello,
I am new to .net and am trying to build a report application that
queries 4 different tables based on a id, and I need to return them in
the same table for easy viewing.
Basically, I...
|
by: Tim Kelley |
last post by:
If I have a datatable with 1 record only, can I reference the data in the
row without a for loop? I can get to the data by using the following code,
but it would be nice if I didn't have to use...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |