Solved

Retrieving data from SqlServer database into Asp.Net Gridview

Posted on 2010-09-18
9
619 Views
Last Modified: 2012-06-27
Hi,
I am trying to retrieve data from one table in an SqlServer database to display it in a gridview called "Grid".

The trouble is that the data consist of answers to 22 questions. Each question stores the answer in one of 5 columns (A,B,C,D,E).
To read the answer for each question, I created several Sql commands each command reads the results of one question.. So, the first command would read the answer for the first question, the second command would read the data from second question, etc.

However, I don't know how to bind the Gridveiw to all the commands in a way that would display all their data in a table-like fashion .

I attempted using the following code to retrieve the data and bind the gridview to each command, one by one, but the gridview ends up displaying only the data for the last command. Is it possible to build one command that would read all the data and bind the gridview to it, or is there a way to have the gridview display the data retrieved by all the commands? Thanks.

  Dim con As SqlConnection
        Dim comm1 As SqlCommand
        Dim reader As SqlDataReader
        '---------------
        ' Create the connection to the database
        con = New SqlConnection("Server=Localhost\SQLEXPRESS; Database=Survey;Integrated Security=True")
         comm1 = New SqlCommand("Select a,b,c,d,e from Response where SurveyID =@surveyID and QuestionID =@QuestionID ", con)
        comm1.Parameters.AddWithValue("@surveyID", System.Data.SqlDbType.Int)
        comm1.Parameters("@surveyID").Value = usedID
        comm1.Parameters.AddWithValue("@QuestionID", System.Data.SqlDbType.Int)
        comm1.Parameters("@QuestionID").Value = 1
        ' Open Connection
        con.Open()
        ' execute comm
        reader = comm1.ExecuteReader
        Me.grid.DataSource = reader
        Me.grid.DataBind()
        reader.Close()
        con.Close()
        '------------
        Dim comm2 As SqlCommand
        comm2 = New SqlCommand("Select a,b,c,d,e from Response where SurveyID =@surveyID and QuestionID =@QuestionID ", con)
        comm2.Parameters.AddWithValue("@surveyID", System.Data.SqlDbType.Int)
        comm2.Parameters("@surveyID").Value = usedID
        comm2.Parameters.AddWithValue("@QuestionID", System.Data.SqlDbType.Int)
        comm2.Parameters("@QuestionID").Value = 2
        ' Open Connection
        con.Open()
        ' execute comm
        reader = comm2.ExecuteReader
        Me.grid.DataSource = reader
        Me.grid.DataBind()
        reader.Close()
        con.Close()
0
Comment
Question by:adamtrask
9 Comments
 
LVL 3

Expert Comment

by:xiong8086
ID: 33707993
maybe you can post some sample data in your database, and sample output for your gridview.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33708018
The Grid can only have a single datasource, therefore you would need to combine the results of each of your queries into a single DataTable and bind to that. However, your method of retrieving data is extremely inefficient. I can't quite tell from the code what you are trying to achieve, but you would be better trying to come up with a query that can retrieve all of the data you need in a single query.
0
 
LVL 12

Accepted Solution

by:
jagssidurala earned 500 total points
ID: 33708284
We can assign single data source to the grid.

        Dim con As SqlConnection
        Dim comm1 As SqlCommand
        Dim reader As SqlDataReader
        '---------------
        ' Create the connection to the database
        con = New SqlConnection("Server=Localhost\SQLEXPRESS; Database=Survey;Integrated Security=True")
         comm1 = New SqlCommand(
      "Select Questionname,a,b,c,d,e
      from    Response TR
      Inner
      Join     tblQuestion TQ TR.QuestionId = TQ.questionId
      where SurveyID =" + intSurveyId, con)
       SqlDataAdapter da = new  SqlDataAdapter();
       DataTable dt = new DataTable()
       da.Fill(dt);        

        Me.grid.DataSource = dt;
        Me.grid.DataBind()
       
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:adamtrask
ID: 33708289
Please find attached two image files - one for the GridView and the other for the table containing the data.

I am attaching the images as sample for the data as requested by xiong8086.

I also want to correct the way I wrote the sqlcommand - I neglected to put in part of the command:

 comm1 = New SqlCommand("Select SurveyID, QuestionID, a,b,c,d,e from Response where SurveyID =@surveyID and QuestionID =@QuestionID ", con)
        comm1.Parameters.AddWithValue("@surveyID", System.Data.SqlDbType.Int)
        comm1.Parameters("@surveyID").Value = usedID
        comm1.Parameters.AddWithValue("@QuestionID", System.Data.SqlDbType.Int)
        comm1.Parameters("@QuestionID").Value = 1
        ' Open Connection
        con.Open()
        ' execute comm
        reader = comm1.ExecuteReader
        Me.grid.DataSource = reader
        Me.grid.DataBind()
        reader.Close()
        con.Close()
Gridview.jpg
0
 

Author Comment

by:adamtrask
ID: 33708290
and here is the image for the table
table.jpg
0
 

Author Comment

by:adamtrask
ID: 33708315
I am sorry guys, that was a stupid way of retrieving data..... I substitued it with:

  comm1 = New SqlCommand("Select * from Response where SurveyID =@surveyID ", con)

Which is what I really needed. I am very much a novice in this.

Thanks a lot any way
0
 

Author Comment

by:adamtrask
ID: 33708496
I actually meant to award the points to the two experts, I must have clicked some thing by mistake
0
 

Author Closing Comment

by:adamtrask
ID: 33708502
Thanks
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33709514
You can put in a request to community support to have points reallocated, or have it reopened if you want to reallocate points yourself.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question