Solved

Retrieving data from SqlServer database into Asp.Net Gridview

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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now