Insert multiple answers into one column of table.

[dcsoke]

There are 11 questions on a survey I have created, and I need to record each question and answer on a seperate record of a MS SQL Server table. This table contains 7 columns, 5 of which will have the same data for each question/answer of each survey taken. I cannot figure out a way to record each question/answer on 11 seperate rows. Can anyone please help?

This is my code so far...

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not (IsPostBack) Then
lblDescription.Text = ("In order to better serve our working relationships and proactively correct any problems between Kimball Hill Homes and you, our subcontractors and vendors, we are asking that you provide feedback on our Construction staff on a monthly basis.")
lblDescription2.Text = ("Please complete an evaluation form for each Construction Manager (CM) that you interact with at the above listed community. Once you have completed the evaluation, please email it back to the sender or fax to 866-328-9057. Please do not hesitate to include additional comments, as needed.")
lblDescription3.Text = ("Please use the following rating system to answer questions about the Construction Manager above.")
lblDescription4.Text = ("(1-Poor; 2-Needs Improvement; 3-Fair; 4-Good; 5-Excellent)")
lblQuestion1.Text = ("Presents himself in a professional manner.")
lblQuestion2.Text = ("Gives sufficient notification for scheduling purposes.")
lblQuestion3.Text = ("The scope of work to be performed in the contract is clearly understood between you and the CM.")
lblQuestion4.Text = ("Communicates professionally and in a manner that is easily understood.")
lblQuestion5.Text = ("Solves issues quickly, professionally and diplomatically.")
lblQuestion6.Text = ("Makes use of the checklist provided.")
lblQuestion7.Text = ("Performs follow-up on outstanding issues and questions.")
lblQuestion8.Text = ("Signs Purchase Orders in a timely manner when work is completed or materials received.")
lblQuestion9.Text = ("Writes Extra Work Orders in a timely manner.")
lblQuestion10.Text = ("Assessment of Back Charges are fair and equitable.")
lblQuestion11.Text = ("You are properly notified of service issues in a timely manner.")
lblQuestion12.Text = ("ADDITIONAL COMMENTS:")
SqlDA_Community.Fill(Community)
ddlCommunity.DataBind()
End If
End Sub

Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
If ddlCommunity.SelectedItem.Selected = True Then
ddlCommunity.SelectedItem.Selected = False
ElseIf ddlVendor.SelectedItem.Selected = True Then
ddlVendor.SelectedItem.Selected = False
ElseIf ddlCM.SelectedItem.Selected = True Then
ddlCM.SelectedItem.Selected = False
ElseIf ddlMonth.SelectedItem.Selected = True Then
ddlMonth.SelectedItem.Selected = False
End If

txtTrade.Text = ""
txtVendorRep.Text = ""

If rbl1.SelectedItem.Selected = True Then
rbl1.SelectedItem.Selected = False
End If

txtComment.Text = ""

End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If rbl1.SelectedItem.Value = 0 Or rbl2.SelectedItem.Value = 0 Or rbl3.SelectedItem.Value = 0 Or rbl4.SelectedItem.Value = 0 Or rbl5.SelectedItem.Value = 0 Or rbl6.SelectedItem.Value = 0 Or rbl7.SelectedItem.Value = 0 Or rbl8.SelectedItem.Value = 0 Or rbl9.SelectedItem.Value = 0 Or rbl10.SelectedItem.Value = 0 Or rbl11.SelectedItem.Value = 0 Then
lblmessage.Text = ("Please enter a value for each question.")
Exit Sub
ElseIf ddlMonth.SelectedItem.Value = "" Or ddlVendor.SelectedItem.Value = "" Or ddlCM.SelectedItem.Value = "" Or ddlCommunity.SelectedItem.Value = "" Then
lblmessage.Text = ("Please select a value for each drop down list at the top of the survey.")
Exit Sub
End If

If rbl1.SelectedItem.Value <> 0 Then

End If

With CmdAdd
.Parameters("@uid").Value = ddlCommunity.SelectedItem.Value

.Parameters("@question").Value = lblQuestion1.Text
.Parameters("@answer").Value = rbl1.SelectedItem.Value

.Parameters("@Vendor").Value = ddlVendor.SelectedItem.Value
.Parameters("@construction_mgr").Value = ddlCM.SelectedItem.Value
.Parameters("@Month").Value = ddlMonth.SelectedItem.Value
End With


End Sub

Private Sub ddlCommunity_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlCommunity.SelectedIndexChanged
With CmdVendor
.Parameters("@project_no").Value = ddlCommunity.SelectedItem.Value
End With

SqlConnection2.Open()
CmdVendor.ExecuteNonQuery()
SqlConnection2.Close()

SqlDA_Vendor.Fill(Vendor)
ddlVendor.DataBind()

With CmdCM
.Parameters("@project_no").Value = ddlCommunity.SelectedItem.Value
End With

SqlConnection2.Open()
CmdCM.ExecuteNonQuery()
SqlConnection2.Close()

SqlDA_CM.Fill(Cm)
ddlCM.DataBind()
End Sub
End Class

 

[DotNetGnat]

try posting it in ASP.NET forum...

-DNG

 

[Sheco]

If all of your questions have a single digit answer 1 thru 5 then it can also be said that each question has a single character answer....you could use any other single character to represent failure to answer a question... since you already use 1-5 you might feel that "0" makes the most sense or your might, for whatever reason, feel like "W" represents failure.

So there is no need for 11 separate rows, just 1 row for all questions and you store the answers to all 11 questions in a single field that is data type char(11).

An example set of answers might be: "33231W55WWW"