asked on

DataGrid/Data Display Question

Hi,

I have a form where users can go to fill out a survey. There are 12 questions total. I want to display this data somehow in the following format...

Total Submissions: 5

Date
Excellent Good Average Fair Poor | Average
Question 1 3 1 1 0 0 4.4
Question 2 0 4 0 0 0 3.4
Totals 3 5 1 0 1 4.0

Can I do this with a datagrid? Currently in my database I have a list of submissions (Date, and Responses to Questions 1 - 12). Would it be easier to sum the totals and get the averages in my database? I am using SQL. Or can I do this in my dataset? Basically what is the easiest way to go about doing this. Thanks.

Ryan

RonaldBiemans

Hi NeoTek,

Yes, this is possible in a datagrid, (although it might be easier print the outcome in a textbox), just a question, what is in the
responses to questions 1-12, a number from 0 to 5 ?

NeoTek

ASKER

Hi,

The responses to questions 1-12 are a number from 1-5. How can I go about doing this?

NeoTek

RonaldBiemans

Give me a second, and i'll give you a possible solution

YZlat

Create a datatable, fill it from a database and then bind it to the datagrid or datalist

'Create the DataTable
Dim dtTable As New DataTable()
dtTable.Columns.Add(New DataColumn(("question"), GetType(String)))
dtTable.Columns.Add(New DataColumn(("excellent"), GetType(Integer)))
dtTable.Columns.Add(New DataColumn(("good"), GetType(Integer)))
dtTable.Columns.Add(New DataColumn(("average"), GetType(Integer)))
dtTable.Columns.Add(New DataColumn(("fair"), GetType(Integer)))
dtTable.Columns.Add(New DataColumn(("poor"), GetType(Integer)))
dtTable.Columns.Add(New DataColumn(("totalavg"), GetType(Integer)))

''retrieve your data from the database with a datareader
Dim Reader as OleDbDataReader ''(or SQLDataReader)

.........................

While Reader.Read
      'Create Rows
Dim dr As DataRow
dr = dtTable.NewRow()

      dr("question")=Reader("question")
dr("excellent") = Reader("excellent")
dr("good") = Reader("good")
dr("average") = Reader("average")
dr("fair") = Reader("fair")
      dr("poor") = Reader("poor")
dr("totalavg") = Reader("totalavg")

end while

'Now, create a DataView and order it by question number
Dim myDataView As DataView = dtTable.DefaultView
myDataView.Sort = "question"

'Now, bind the DataView to the DataGrid
DataGrid1.DataSource = myDataView

If you provide yourr db structure I can help you to fill out your datatable

NeoTek

ASKER

My datatabes is called CSS_Input in a Database Intranet. I put that into a dataset, DsCSS. My fields are as follows...

ID - int
CurrentDate - datetime
R1 - int
R2 - int
R3 - int
R4 - int
R5 - int
R6 - int
R7 - int
R8 - int
R9 - int
R10 - int
R11 - int
R12 - int

That's all. Thanks.

NeoTek

ASKER CERTIFIED SOLUTION

RonaldBiemans

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RonaldBiemans

A bit shorter ;-),

Private Sub Form17_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
OleDbDataAdapter1.Fill(dt)

Dim dt2 As New DataTable
dt2.Columns.Add("Question", GetType(System.String))
dt2.Columns.Add("Excellent", GetType(System.Int32))
dt2.Columns.Add("Good", GetType(System.Int32))
dt2.Columns.Add("Average", GetType(System.Int32))
dt2.Columns.Add("Fair", GetType(System.Int32))
dt2.Columns.Add("Poor", GetType(System.Int32))
dt2.Columns.Add("AVG", GetType(System.Single), "((Excellent*5) + (Good*4) + (Average*3) + (Fair*2) + (Poor*1))/(Excellent+Good+Average+Fair+Poor)")

Dim dr As DataRow
For x As Byte = 1 To 12
dr = dt2.NewRow
dr.Item(0) = "Question " & x
For y As Byte = 1 To 5
dr.Item(y) = dt.Compute("count(R" & x & ")", "R" & x & "= " & y)
Next
dt2.Rows.Add(dr)
Next

dr = dt2.NewRow
dr.Item(0) = "Total"
dr.Item(1) = dt2.Compute("sum(Excellent)", "")
dr.Item(2) = dt2.Compute("sum(Good)", "")
dr.Item(3) = dt2.Compute("sum(Average)", "")
dr.Item(4) = dt2.Compute("sum(Fair)", "")
dr.Item(5) = dt2.Compute("sum(Poor)", "")
dt2.Rows.Add(dr)
DataGrid1.DataSource = dt2
End Sub

RonaldBiemans

Sorry, forget my last comment. (WRONG ADJUSTMENT)