Solved

Retrieving data from SqlServer database into Asp.Net Gridview

Posted on 2010-09-18
9
618 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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