Logic on how to process dataset that returns different rows counts for each grouping

Question

0.00/5 (No votes)

See more:

Okay this to me is a weird one and I'm having trouble wrapping my brain around the way I should process the dataset. A little background - I'm retrieving data from a DNN database that is individuals submitting a form with some questions. Based on how they answer some of the questions there maybe extra fields to process. I'm then shoving this data into a legacy database so I have to process the answers and then concatenate them to fit the legacy database.

Sample of possible returned fields (these are returned as rows not columns):

Name
email
phone
event type (depending on answer on whether the next field is returned or not)
specify other (may or may not be included in dataset)
day
time
budget
how did you hear about us(depending on answer could be up to four more fields)
(0 - 4 of the following may be returned)
specify web search
specify employee
specify referral
specify other

For the event type only one type of answer causes the specify other to be answered so I can do an if statement on it. And if true then I add one to the row count so I don't lose which field I'm processing, but for the last one I'm stumped because I don't want to put in a lot of if statements and I can't do a case statement because there are too many combinations of which fields have been answered. I didn't create the form this data is coming from so I have no control over changing it.

If someone knows of an example handling something like this I would appreciate it ~ otherwise giving me some pointers on how to process the set of specify answers would be okay.

Thanks,
Carolyn

Posted 20-Jun-13 7:25am

CARisk3

Updated 20-Jun-13 7:37am

v3

Add a Solution

Comments

Kschuler 20-Jun-13 14:31pm

After reading your question a couple of times...I'm still not sure what you are doing. Can you post the code you have so far? Or explain it with a simplified example of actual data you are using?

CARisk3 20-Jun-13 14:39pm

Okay for a form there are 8 questions that need answered. For question 4 and 8 they are actually check a box to answer with being allowed to check more than one box. For answer 4 only one answer will cause the specify other box to come up so I coded it this way so I could capture the sequential answers without having to write different processes:

dim rowValue as integer = 1

Dim type As String = "Event Type: " & ds.Tables(0).Rows(3).Item("response")

If type = "Other" Or ds.Tables(0).Rows(3).Item("DynamicQuestionID") = GUIID Then
Dim eventOther As String = "Specify: " & ds.Tables(0).Rows(4).Item("response")
rowValue = 1
End If

Dim eLoc As String = "Location: " & ds.Tables(0).Rows(4 + rowValue).Item("response")
Dim eDate As DateTime = "Date: " & ds.Tables(0).Rows(6 + rowValue).Item("response")
Dim eTime As String = "Time of Day: " & ds.Tables(0).Rows(7 + rowValue).Item("response")
Dim eBudget As String = "Expected Budget: " & ds.Tables(0).Rows(9 + rowValue).Item("response")
Dim hearAbout As String = "Heard About Us:" & ds.Tables(0).Rows(10 + rowValue).Item("response")

CARisk3 20-Jun-13 14:48pm

Then the last question hearAbout they can check more than one answer and four of them can cause a specify input box to come up. This means I could have an extra four rows at the end of my dataset that I don't have for each grouping.

Since I didn't get any answers right away, I started trying to code it like this:

If ds.Tables(0).Rows.Count() > (11 + rowValue) Then
If ds.Tables(0).Rows.Count() > (11 + rowValue) Then
For i = (11 + rowValue) To ds.Tables(0).Rows.Count()
If ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
specifyOther += " Name: " & ds.Tables(0).Rows(i).Item("response")
ElseIf ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
specifyOther += " Search: " & ds.Tables(0).Rows(i).Item("response")
ElseIf ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
specifyOther = "Referrer: " & ds.Tables(0).Rows(i).Item("response")
ElseIf ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
specifyOther = " Other: " & ds.Tables(0).Rows(i).Item("response")
Else
specifyOther = " Specify: " & ds.Tables(0).Rows(i).Item("response")
End If

Next
End If

Would there be a more efficient way to do this? The issue is I have to capture all the rows but don't know how many there will be.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CARisk3 · Accepted Answer · 2013-06-20T10:51:00

I was able to solve this by using a combination of an if statement and looping through any rows that were left after the main rows had been processed.

Because of the extra input field above rowValue is just a way to increment everything by one if the field exist and if it doesn't then it uses the normal row number.

VB

If ds.Tables(0).Rows.Count() > (11 + rowValue) Then
        For i = (11 + rowValue) To ds.Tables(0).Rows.Count()
            If ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
                specifyOther += " Name: " & ds.Tables(0).Rows(i).Item("response")
            ElseIf ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
                specifyOther += " Search: " & ds.Tables(0).Rows(i).Item("response")
            ElseIf ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
                specifyOther = "Referrer: " & ds.Tables(0).Rows(i).Item("response")
            ElseIf ds.Tables(0).Rows(i).Item("DynamicQuestionID") = GUID Then
                specifyOther = " Other: " & ds.Tables(0).Rows(i).Item("response")
            Else
                specifyOther = " Specify: " & ds.Tables(0).Rows(i).Item("response")
            End If
           
            i+=1
        Next
    End If